一亩三分地

 找回密码 注册账号

扫描二维码登录本站

BBS
Offer多多
Salarytics
交友
Learn
Who's Hiring?
Visa Tracker
疫情动态
指尖新闻
Instant
客户端
微信公众号
扫码关注公众号
留学申请公众号
扫码关注留学申请公众号
Youtube频道
留学博客
关于我们
查看: 1080|回复: 1
收起左侧

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

[复制链接] |只看干货 |打卡战拖
我的人缘0

升级   31.71%


分享帖子到朋友圈
zzh2011 | 显示全部楼层 |阅读模式
本楼: 👍   100% (1)
 
 
0% (0)   👎
全局: 👍   100% (161)
 
 
0% (0)    👎

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


后续更新的部分目录:
   




新人求米,关注等更!


Average Selling Price

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


[SQL] 纯文本查看 复制代码
select p.product_id, round( sum(p.price * u.units)/sum(u.units),2) as average_price
from Prices p join UnitsSold u
on p.product_id = u.product_id
and u.purchase_date between p.start_date and p.end_date
group by p.product_id;

Product Sales Analysis I

就是一个简单的 left join.


[SQL] 纯文本查看 复制代码
select product_name, year, price
from Sales s left join Product p 
on s.product_id = p.product_id;

# similar but quicker 
Select a.product_name, b.year, b.price
from product a join sales b
on a.product_id = b.product_id
order by b.year


* Students and Examinations


[SQL] 纯文本查看 复制代码
# 这个相对较快, 吧Students 和Subjects 先 join 起来了, 然后座位一个整体, 再去join 另一个
select a.student_id,a.student_name,a.subject_name, count(b.subject_name) attended_exams  
    from (select student_id,student_name,subject_name from Students,Subjects ) a 
    left join examinations b 
    on a.student_id = b.student_id and a.subject_name = b.subject_name 
group by a.student_id,a.student_name,a.subject_name
order by a.student_id



[SQL] 纯文本查看 复制代码
# 比较慢的做法, 用了 cross join, 不太常用
select a.student_id,a.student_name,a.subject_name,coalesce(count(e.subject_name)) as attended_exams from 
(select student_id,student_name,s.subject_name
from subjects s cross join students st) a left join examinations e 
on a.student_id=e.student_id and a.subject_name=e.subject_name
group by 1,2,3
order by 1



*Game Play Analysis IV

[SQL] 纯文本查看 复制代码
select round(count(a2.player_id)/count(a1.player_id),2) as fraction 
from 
(select player_id, 
        min(event_date) as first_login 
        from activity 
        group by player_id) a1 
left join activity a2 
on a1.player_id = a2.player_id
and datediff(a2.event_date, a1.first_login)=1

# easier to understand 
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 
FROM (SELECT player_id, MIN(event_date) AS event_date
      FROM Activity 
      GROUP BY player_id) AS a JOIN Activity AS b 
ON a.player_id = b.player_id;


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

*Active Businesses

[SQL] 纯文本查看 复制代码
select business_id
from Events e join
(select event_type, avg(occurences) as avg from Events group by event_type) a
on e.event_type = a.event_type and occurences > a.avg
group by business_id
having count(distinct e.event_type) > 1

# 审题问题, 不是每个event_type 至少有一个大于平均的, 而是business_id 至少有一个大于平均的. 


*Reported Posts II


[SQL] 纯文本查看 复制代码
select round(avg(t.num),2) average_daily_percent from (
    select count(distinct R.post_id)/count(distinct A.post_id)*100 num
    from Actions A left join Removals R on A.post_id=R.post_id
    where extra='spam'
    group by action_date
 ) t


Market Analysis I


[SQL] 纯文本查看 复制代码
with cte as(select count(distinct order_id) as count, buyer_id
from Orders
where order_date like '2019%'
group by 2)

select user_id as buyer_id, join_date, ifnull(cte.count,0) as orders_in_2019
from Users u left join cte 
on u.user_id = cte.buyer_id

# 直接写效果不好的时候, 试试 cte! 

# 更简单可用 CASE WHEN 
select u.user_id as buyer_id, join_date, 
sum(case when year(order_date)=2019 then 1 else 0 end) orders_in_2019 
from Users u left join Orders o 
on u.user_id=o.buyer_id
group by u.user_id

# OR 
SELECT u.user_id AS buyer_id, u.join_date, COUNT(DISTINCT o.order_id) AS orders_in_2019
FROM Users u LEFT JOIN (SELECT * FROM Orders WHERE year(order_date) = 2019) o
ON u.user_id = o.buyer_id
GROUP BY 1
{% hint style="warning" %} 直接选择日期中 YEAR 可以用 YEAR(order_date) = 2019 {% endhint %}

NPV Queries

[SQL] 纯文本查看 复制代码
select q.id, q.year, ifnull(n.npv,0) as npv
from Queries q left join NPV n 
on q.id=n.id and q.year=n.year
order by 1 asc





本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?注册账号

x

上一篇:坐标加拿大🇨🇦 | DS/MLE方向跳槽 | 刷题+ML基础
下一篇:500 Data相关的题
我的人缘0

升级   31.71%

 楼主| zzh2011 2020-8-19 09:01:00 | 显示全部楼层
本楼: 👍   0% (0)
 
 
0% (0)   👎
全局: 👍   100% (161)
 
 
0% (0)    👎
欢迎大家讨论与勘误! 谢谢! 勘误给加米!
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 注册账号

本版积分规则

隐私提醒:
■拉群请前往同学同事飞友|拉群结伴版块,其他版块拉群,帖子会被自动删除
■论坛不能删帖,为防止被骚扰甚至人肉,不要公开留微信等联系方式,请以论坛私信方式发送。
■特定版块可以超级匿名:https://pay.1point3acres.com/tools/thread
■其他版块匿名方法:http://www.1point3acres.com/bbs/thread-405991-1-1.html

手机版|||一亩三分地

Powered by Discuz! X3

© 2001-2013 Comsenz Inc. Design By HUXTeam

Some icons made by Freepik from flaticon.com

快速回复 返回顶部 返回列表