[Data analysis Interview Questions] One interview question, my answer

I attended a data analysis interview with a company in Hangzhou this afternoon. I am familiar with some of the questions, but I didn't give the best answer. Share: Data Structure: Name category cost Li weixing movie 30 Li weixing 100 Li weixing 500 Wang weixing 500 Wang weixing 100 Wang weixing clothes 700 display effect: prenamecodeclasssql name

I attended a data analysis interview with a company in Hangzhou this afternoon. I am familiar with some of the questions, but I didn't give the best answer. Share: Data Structure: name category cost Li weixing movie 30 Li weixing 100 Li weixing 500 Wang weixing 500 Wang weixing 100 Wang weixing clothes 700 display effect: pre name = code class = SQL name

I attended a data analysis interview with a company in Hangzhou this afternoon. I am familiar with some of the questions, but I didn't give the best answer.

Share:

Data structure:

Name category spending Li watching movies 30 Li eating 100 Li traveling 500 Wang eating 500 Wang watching movies 100 Wang buying clothes 700

Display Effect:

Name TOP1 category TOP1 cost TOP2 category TOP2 cost TOP3 category TOP3 cost Li travel 500 dinner 100 movie watch 30 King buy clothes 700 dinner 500 movie 100
This topic is actually quite simple, with horizontal column conversion. The most efficient answer, look at the end ,.... /// If there is a better one, you are welcome to discuss it.
Certificate ---------------------------------------------------------------------------------------------------------------------------------------------------------
Select name,
MAX (DECODE (SEQ, 1, TYPE, NULL) HIGHEST_TYPE,
MAX (DECODE (SEQ, 1, COST, NULL) HIGHEST_COST,
MAX (DECODE (SEQ, 2, TYPE, NULL) SECOND_TYPE,
MAX (DECODE (SEQ, 2, COST, NULL) SECOND_COST,
MAX (DECODE (SEQ, 3, TYPE, NULL) THIRD_TYPE,
MAX (DECODE (SEQ, 3, COST, NULL) THIRD_COST
FROM (select name,
COST,
TYPE,
ROW_NUMBER () OVER (partition by name order by cost desc nulls last) SEQ
From test)
Group by name;