[Data analysis Interview Questions] One interview question, my answer, and the answer to the data analysis interview questions

[Data analysis Interview Questions] One interview question, my answer, and the answer to the data analysis interview questions

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:

<Pre name = "code" class = "SQL"> name TOP1 category TOP1 cost TOP2 cost TOP2 cost TOP3 category TOP3 cost Li travel 500 eat 100 watch movie 30 King buy clothes 700 eat 500 watch 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;

A database interview question

1. select s. sid from s, t where t. tid in ('1', '2 ')
2.
Select s. sid, s. sname
From s,
(Select sid, count (tid) from st where score <60 group by sid) st1
Where s. sid = st1.sid
3.
Select st1.sid
From
(Select sid, score from st where tid = '1') st1
, (Select sid, score from st where tid = '2') st2
Where st1.score> st2.score
And st1.sid = st2.sid

An interview question for a database

1 -- Take the data in question 2 as an example. The mailbox and name are repeated at the same time and only one of them is retained.
Delete from table name where id not in (select min (id) from table name group by name, email)

2 --
Select t1. * from table name t1,
(Select name, max (lastlogon) lastlogon from table name group by name) t2
Where t1.name = t2.name and t1.lastlogon = t2.lastlogon