sql进阶day01

以下题目均来自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