以下题目均来自leetcode官网 https://leetcode.cn/
本文仅用于记录学习进度
1077. 项目员工 III
select t.project_id, t.employee_id
from (select p.project_id,
e.employee_id,
e.experience_years,
rank() over (partition by project_id order by experience_years desc ) rk
from project p
left join employee e on p.employee_id = e.employee_id) t
where rk = '1'
1549. 每件商品的最新订单
select product_name, p.product_id, order_id, order_date
from (select order_id,
order_date,
product_id,
`rank`
from (select order_id,
order_date,
product_id,
dense_rank() over (partition by product_id order by order_date desc) as 'rank'
from orders) t
where t.`rank` = 1) t2
left join products p on t2.product_id = p.product_id
order by product_name, product_id, order_id
1285. 找到连续区间的开始和结束数字
思路:如果找到连续区间?给每个数字排名,如果数值和排名相减的值相同说明是连续值。
select t.log_id start_id , (log_id + count(*) - 1) as end_id
from (select log_id, log_id - rank() over (order by log_id) as `num`
from logs) t
group by num
having count(*) >= 1
1596. 每位顾客最经常订购的商品
select t.customer_id, t.product_id, p.product_name
from (select *, rank() over (partition by customer_id order by count(product_id) desc) as rk
from orders
group by customer_id, product_id) t,
products p
where t.product_id = p.product_id
and rk = 1
178. 分数排名
select score, dense_rank() over (order by score desc )
from scores;
1951. 查询具有最多共同关注者的所有两两结对组
思路:两两结对组即是 有完全相同的关注列表;找相同的关注列表:自连接,连接条件为关注id相同并且表一的user_id小于表二user_id,根据两张自连接表的user_id分组计数就能够找到最大
select t.user1_id, t.user2_id
from (select r1.user_id user1_id,
r2.user_id user2_id,
dense_rank() over (order by count(*) desc ) rk
from relations r1
join relations r2 on r1.follower_id = r2.follower_id and r1.user_id < r2.user_id
group by r1.user_id, r2.user_id) t
where t.rk = 1;
1709. 访问日期之间最大的空档期
关键点:找到当前日期的下一个日期 思路:将同一用户下的日期进行排序,然后自关联,关联条件为:user_id相同并且第二张表的日期排名比第一张表的日期排名大一
找到相邻两个日期的间隔天数,注意:还要与2021-01-01计算得到最后一个间隔期间
找到相同user_id下最大的间隔天数即可
with tmp as (select *, rank() over (partition by user_id order by visit_date) as rk
from uservisits)
select t.user_id, max(days) biggest_window
from (select t1.user_id,
abs(datediff(t1.visit_date, (case
when t2.visit_date is null then '2021-01-01'
else t2.visit_date
end))) days
from tmp t1
left join tmp t2 on t1.user_id = t2.user_id and t1.rk + 1 = t2.rk) t
group by t.user_id