求米，分享买它DS面试中几个SQL常见题目

Customers Who Never Order
Write an SQL query to report all customers who never order anything.
Return the result table in any order.
Input:
Customers table:
+----+-------+
| id | name  |
+----+-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+----+-------+
Orders table:
+----+------------+
| id | customerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+
Output:
+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+
Solution:
select
    name as Customers
from Customers
where id not in (select customerId from Orders)
Solution 2:
with cte as (
select
    a.name,
    sum(case when b.id is null then 0 else 1 end) as total_order
from Customers a
left join Orders b
on a.id=b.customerId
group by a.id, a.name
)
select
    name as Customers
from cte
where total_order=0

Department Top Three Salaries
A company's executives are interested in seeing who earns the most money in each of the company's departments. A high earner in a department is an employee who has a salary in the top three unique salaries for that department.

Input:
Employee table:
+----+-------+--------+--------------+
| id | name  | salary | departmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 85000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
| 5  | Janet | 69000  | 1            |
| 6  | Randy | 85000  | 1            |
| 7  | Will  | 70000  | 1            |
+----+-------+--------+--------------+
Department table:
+----+-------+
| id | name  |
+----+-------+
| 1  | IT    |
| 2  | Sales |
. ----+----+-------+
Output:
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Joe      | 85000  |
| IT         | Randy    | 85000  |
| IT         | Will     | 70000  |
| Sales      | Henry    | 80000  |
| Sales      | Sam      | 60000  |
+------------+----------+--------+
Solution:
with cte as (
select
    b.name as Department,
    a.name as Employee,
    a.salary as Salary,
    dense_rank() over(partition by a.departmentId order by a.salary desc) as r
from Employee a
left join Department b
on a.departmentId = b.id
)
select
    Department,
    Employee,
    Salary
from cte
where r<=3

Overall Acceptance Rate
•        The accepted requests are not necessarily from the table friend_request. In this case, Count the total accepted requests (no matter whether they are in the original requests), and divide it by the number of requests to get the acceptance rate.
•        It is possible that a sender sends multiple requests to the same receiver, and a request could be accepted more than once. In this case, the ‘duplicated’ requests or acceptances are only counted once.
•        If there are no requests at all, you should return 0.00 as the accept_rate.
FriendRequest table:
There is no primary key for this table, it may contain duplicates.
+-----------+------------+--------------+
| sender_id  | send_to_id | request_date |
+-----------+------------+--------------+
|       1         |       2          | 2016/06/01   |
|       1         |       3          | 2016/06/01   |
|       1         |       4          | 2016/06/01   |
|       2         |       3          | 2016/06/02   |
|       3         |       4          | 2016/06/09   |
+-----------+------------+--------------+
RequestAccepted table:
There is no primary key for this table, it may contain duplicates.
+--------------+-------------+-------------+
| requester_id | accepter_id | accept_date |
+--------------+-------------+-------------+
|       1            |       2            | 2016/06/03  |
|       1            |       3            | 2016/06/08  |
|       2            |       3            | 2016/06/08  |
|       3            |       4            | 2016/06/09  |
|       3            |       4            | 2016/06/10  |
+--------------+-------------+-------------+
Output:
+-------------+
| accept_rate |
+-------------+
|       0.8         |
+-------------+
Solution:
with
x as (
select
count(distinct requester_id, accepter_id) as x_cnt
from RequestAccepted
),
y as (
select
count(distinct sender_id, send_to_id) as y_cnt
from FriendRequest
)
select
round(coalesce(x_cnt / (select y_cnt from y), 0.00), 2) as accept_rate
from x

Get Highest Answer Rate Question
The answer rate for a question is the number of times a user answered the question by the number of times a user showed the question.
Write an SQL query to report the question that has the highest answer rate. If multiple questions have the same maximum answer rate, report the question with the smallest question_id.

Input:
SurveyLog table:
There is no primary key for this table. It may contain duplicates.
action is an ENUM of the type: "show", "answer", or "skip".
+----+--------+-------------+-----------+-------+-----------+
| id | action | question_id | answer_id | q_num | timestamp |
+----+--------+-------------+-----------+-------+-----------+
| 5  | show   | 285         | null      | 1     | 123       |
| 5  | answer | 285         | 124124    | 1     | 124       |
| 5  | show   | 369         | null      | 2     | 125       |
| 5  | skip   | 369         | null      | 2     | 126       |
+----+--------+-------------+-----------+-------+-----------+
Output:
+------------+
| survey_log |
+------------+
| 285        |
+------------+
Solution:
with cte as (
select
    question_id,
    sum(case when action='answer' then 1 else 0 end) / sum(case when action='show' then 1 else 0 end) as answer_rate
from SurveyLog
group by question_id
)
select
. ----    question_id as survey_log
from cte
order by answer_rate desc, question_id asc
limit 1

Median Employee Salary
Input:
Employee table:
+----+---------+--------+
| id | company | salary |
+----+---------+--------+
| 1  | A       | 2341   |
| 2  | A       | 341    |
| 3  | A       | 15     |
| 4  | A       | 15314  |
| 5  | A       | 451    |
| 6  | A       | 513    |
| 7  | B       | 15     |
| 8  | B       | 13     |
| 9  | B       | 1154   |
.google  и| 10 | B       | 1345   |
| 11 | B       | 1221   |
| 12 | B       | 234    |
| 13 | C       | 2345   |
| 14 | C       | 2645   |
| 15 | C       | 2645   |
| 16 | C       | 2652   |
| 17 | C       | 65     |
+----+---------+--------+
Output:
+----+---------+--------+
| id | company | salary |
+----+---------+--------+
| 5  | A       | 451    |
| 6  | A       | 513    |
| 12 | B       | 234    |
| 9  | B       | 1154   |
| 14 | C       | 2645   |
+----+---------+--------+
Solution:
with temp as
(
select id, company, salary,
rank() over (partition by company order by salary asc) as lowrank,
rank() over (partition by company order by salary desc) as highrank
from Employee
group by 1,2
)
select id, company, salary
from temp
where lowrank = highrank+1 or highrank=lowrank+1 or lowrank=highrank
group by 2,3
order by 2,3

Who Has the Most Friends
Write an SQL query to find the people who have the most friends and the most friends number.
The test cases are generated so that only one person has the most friends.
Input:
RequestAccepted table:
+--------------+-------------+-------------+
| requester_id | accepter_id | accept_date |
+--------------+-------------+-------------+
| 1            | 2           | 2016/06/03  |
| 1            | 3           | 2016/06/08  |
| 2            | 3           | 2016/06/08  |
| 3            | 4           | 2016/06/09  |
+--------------+-------------+-------------+
Output:
+----+-----+
| id | num |
+----+-----+
| 3  | 3   |
+----+-----+
Solution:
with cte as (
select
    requester_id as id,
    accepter_id as friend_id
from RequestAccepted
union
select
    accepter_id as id,
    requester_id as friend_id
from RequestAccepted
)
select
    id,
    count(distinct friend_id) as num
from cte
group by id
order by num desc
limit 1

Second Degree Follower
Write an SQL query to report the second-degree users and the number of their followers.
A second-degree follower is a user who:
•        follows at least one user, and
•        is followed by at least one user.

Input:
Follow table:
+----------+----------+
| followee | follower |
+----------+----------+
| Alice    | Bob      |
| Bob      | Cena     |
| Bob      | Donald   |
| Donald   | Edward   |
+----------+----------+
Output:
+----------+-----+
| follower | num |
+----------+-----+
| Bob      | 2   |
| Donald   | 1   |
+----------+-----+
Solution:
select
    a.follower,
    count(distinct b.follower) as num
from Follow a
left join Follow b
on a.follower=b.followee
group by a.follower
having num>0
order by a.follower asc

Reported Posts

Write an SQL query that reports the number of posts reported yesterday for each report reason. Assume today is 2019-07-05
Actions table:
+---------+---------+-------------+--------+--------+
| user_id | post_id | action_date | action | extra  |
+---------+---------+-------------+--------+--------+
| 1       | 1       | 2019-07-01  | view   | null   |
| 1       | 1       | 2019-07-01  | like   | null   |
| 1       | 1       | 2019-07-01  | share  | null   |
| 2       | 4       | 2019-07-04  | view   | null   |
| 2       | 4       | 2019-07-04  | report | spam   |
| 3       | 4       | 2019-07-04  | view   | null   |
| 3       | 4       | 2019-07-04  | report | spam   |
| 4       | 3       | 2019-07-02  | view   | null   |
| 4       | 3       | 2019-07-02  | report | spam   |
| 5       | 2       | 2019-07-04  | view   | null   |
| 5       | 2       | 2019-07-04  | report | racism |
| 5       | 5       | 2019-07-04  | view   | null   |
| 5       | 5       | 2019-07-04  | report | racism |
+---------+---------+-------------+--------+--------+
Output:
+---------------+--------------+
| report_reason | report_count |
+---------------+--------------+
| spam          | 1            |
| racism        | 2            |
+---------------+--------------+
Solution:
select
    extra as report_reason,
    count(distinct post_id) as report_count
from Actions
where action='report' and action_date='2019-07-04'
group by extra
having report_count>0

Reported Posts II
Write an SQL query to find the average daily percentage of posts that got removed after being reported as spam, rounded to 2 decimal places.

Input:
Actions table:
+---------+---------+-------------+--------+--------+
| user_id | post_id | action_date | action | extra  |
+---------+---------+-------------+--------+--------+
| 1       | 1       | 2019-07-01  | view   | null   |
| 1       | 1       | 2019-07-01  | like   | null   |
. Χ| 1       | 1       | 2019-07-01  | share  | null   |
| 2       | 2       | 2019-07-04  | view   | null   |
| 2       | 2       | 2019-07-04  | report | spam   |
| 3       | 4       | 2019-07-04  | view   | null   |
| 3       | 4       | 2019-07-04  | report | spam   |
| 4       | 3       | 2019-07-02  | view   | null   |
| 4       | 3       | 2019-07-02  | report | spam   |
| 5       | 2       | 2019-07-03  | view   | null   |
| 5       | 2       | 2019-07-03  | report | racism |
| 5       | 5       | 2019-07-03  | view   | null   |
| 5       | 5       | 2019-07-03  | report | racism |
+---------+---------+-------------+--------+--------+
Removals table:
+---------+-------------+
| post_id | remove_date |
+---------+-------------+
| 2       | 2019-07-20  |
| 3       | 2019-07-18  |
+---------+-------------+
Output:
+-----------------------+
| average_daily_percent |
+-----------------------+
| 75.00                 |
+-----------------------+
with cte as (
select
    a.post_id,
    a.action_date,
    a.extra,
    b.post_id as remove_id
from Actions a
left join Removals b
on a.post_id = b.post_id
where a.action='report' and a.extra='spam'
),
cte2 as (
select
    count(distinct remove_id) / count(distinct post_id) as daily_percent
. 1point3acres.com from cte
group by action_date
)
select
    round(avg(daily_percent)*100, 2) as average_daily_percent
from cte2