[新人求米, 关注等更]力扣 SQL答案 按题型分类(I) JOIN (不包括 self jo...

力扣 SQL 题目答案 按题型分类:
(I) JOIN (不包括 self join)


后续更新的部分目录:

  

新人求米,关注等更!


Average Selling Price

本质就是把同一产品的不同价格与相应的销量相乘, 除以总销量, 得到该产品的平均价格.

1. select p.product_id, round( sum(p.price * u.units)/sum(u.units),2) as average_price
   
2. from Prices p join UnitsSold u
   
3. on p.product_id = u.product_id
   
4. and u.purchase_date between p.start_date and p.end_date
   
5. group by p.product_id;
   

复制代码

Product Sales Analysis I

就是一个简单的 left join.

1. select product_name, year, price
   
2. from Sales s left join Product p
   
3. on s.product_id = p.product_id;
   
4. 
   
5. # similar but quicker
   
6. Select a.product_name, b.year, b.price
   
7. from product a join sales b
   
8. on a.product_id = b.product_id
   
9. order by b.year

复制代码

* Students and Examinations

1. # 这个相对较快, 吧Students 和Subjects 先 join 起来了, 然后座位一个整体, 再去join 另一个
   
2. select a.student_id,a.student_name,a.subject_name, count(b.subject_name) attended_exams  
   
3.     from (select student_id,student_name,subject_name from Students,Subjects ) a
   
4.     left join examinations b
   
5.     on a.student_id = b.student_id and a.subject_name = b.subject_name
   
6. group by a.student_id,a.student_name,a.subject_name
   
7. order by a.student_id

复制代码

1. # 比较慢的做法, 用了 cross join, 不太常用
   
2. select a.student_id,a.student_name,a.subject_name,coalesce(count(e.subject_name)) as attended_exams from
   
3. (select student_id,student_name,s.subject_name
   
4. from subjects s cross join students st) a left join examinations e
   
5. on a.student_id=e.student_id and a.subject_name=e.subject_name
   
6. group by 1,2,3
   
7. order by 1

复制代码

*Game Play Analysis IV

1. select round(count(a2.player_id)/count(a1.player_id),2) as fraction
   
2. from
   
3. (select player_id,
   
4.         min(event_date) as first_login
   
5.         from activity
   
6.         group by player_id) a1
   
7. left join activity a2
   
8. on a1.player_id = a2.player_id
   
9. and datediff(a2.event_date, a1.first_login)=1
   
10. 
    
11. # easier to understand
    
12. SELECT ROUND(SUM(CASE WHEN a.event_date + 1 = b.event_date THEN 1 ELSE 0 END)/COUNT(DISTINCT a.player_id), 2) AS fraction
    
13. FROM (SELECT player_id, MIN(event_date) AS event_date
    
14.       FROM Activity
    
15.       GROUP BY player_id) AS a JOIN Activity AS b
    
16. ON a.player_id = b.player_id;

复制代码

{% hint style="info" %} subquery 提取出来了每个用户的第一次 login {% endhint %}

*Active Businesses

1. select business_id
   
2. from Events e join
   
3. (select event_type, avg(occurences) as avg from Events group by event_type) a
   
4. on e.event_type = a.event_type and occurences > a.avg
   
5. group by business_id
   
6. having count(distinct e.event_type) > 1
   
7. 
   
8. # 审题问题, 不是每个event_type 至少有一个大于平均的, 而是business_id 至少有一个大于平均的.
   

复制代码

*Reported Posts II

1. select round(avg(t.num),2) average_daily_percent from (
   
2.     select count(distinct R.post_id)/count(distinct A.post_id)*100 num
   
3.     from Actions A left join Removals R on A.post_id=R.post_id
   
4.     where extra='spam'
   
5.     group by action_date
   
6. ) t
   

复制代码

Market Analysis I

1. with cte as(select count(distinct order_id) as count, buyer_id
   
2. from Orders
   
3. where order_date like '2019%'
   
4. group by 2)
   
5. 
   
6. select user_id as buyer_id, join_date, ifnull(cte.count,0) as orders_in_2019
   
7. from Users u left join cte
   
8. on u.user_id = cte.buyer_id
   
9. 
   
10. # 直接写效果不好的时候, 试试 cte!
    
11. 
    
12. # 更简单可用 CASE WHEN
    
13. select u.user_id as buyer_id, join_date,
    
14. sum(case when year(order_date)=2019 then 1 else 0 end) orders_in_2019
    
15. from Users u left join Orders o
    
16. on u.user_id=o.buyer_id
    
17. group by u.user_id
    
18. 
    
19. # OR
    
20. SELECT u.user_id AS buyer_id, u.join_date, COUNT(DISTINCT o.order_id) AS orders_in_2019
    
21. FROM Users u LEFT JOIN (SELECT * FROM Orders WHERE year(order_date) = 2019) o
    
22. ON u.user_id = o.buyer_id
    
23. GROUP BY 1
    

复制代码

{% hint style="warning" %} 直接选择日期中 YEAR 可以用 YEAR(order_date) = 2019 {% endhint %}

NPV Queries

1. 
   
2. select q.id, q.year, ifnull(n.npv,0) as npv
   
3. from Queries q left join NPV n
   
4. on q.id=n.id and q.year=n.year
   
5. order by 1 asc
   

复制代码

欢迎大家讨论与勘误! 谢谢! 勘误给加米!