准备数仓实习面试中回顾SQL题

550-game-play-analysis-iv

https://leetcode.com/problems/game-play-analysis-iv/description/

连续两天登录的比率，2025年6月10日 星期二，date_sub

# 编写解决方案，报告在首次登录的第二天再次登录的玩家的 比率，四舍五入到小数点后两位。换句话说，你需要计算从首次登录日期开始至少连续两天登录的玩家的数量，然后除以玩家总数。
# Subqueries and multi-value use of the IN comparison operator
# 子查询和IN 比较运算符的多值使用
select round(count(a1.player_id) / (select count(distinct a3.player_id)from Activity a3), 2) as fraction
from Activity a1
where (a1.player_id, date_sub(a1.event_date, interval 1 day )) in(
    select a2.player_id, min(a2.event_date)
    from Activity a2
    group by a2.player_id
    );
# CTEs and INNER JOIN
with first_logins as (
    select a.player_id, min(a.event_date) as first_login
    from Activity a
    group by a.player_id
), consec_logins as (
    select count(a.player_id) as num_logins
    from first_logins f
    inner join Activity a on f.player_id = a.player_id
    and f.first_login = date_sub(a.event_date, interval 1 day )
)
select round(
    (select num_logins from consec_logins) /
    (select count(player_id) from first_logins), 2
) as fraction

602-friend-requests-ii-who-has-the-most-friends

https://leetcode.com/problems/friend-requests-ii-who-has-the-most-friends/description/

好友申请 II ：谁有最多的好友

拥有最多的好友的人和他拥有的好友数目，2025年6月12日 星期四

-- 应该使用 union all 而不是 union ，因为 union all 即使遇到重复的记录也都会保存下来。
select ids as id, cnt as num
from(
select ids, count(*) as cnt
   from(
        select requester_id as ids from RequestAccepted
        union all
        select accepter_id from RequestAccepted
    ) as tbl1
   group by ids
   ) as tbl2
order by cnt desc
limit 1;
--
with all_ids as (
    select requester_id as id from RequestAccepted
    union all
    select accepter_id from RequestAccepted
)
select id, count(id) as num
from all_ids
group by id
order by count(id) desc limit 1;

-- use rank
with all_ids as (
    select requester_id as id from RequestAccepted
    union all
    select accepter_id from RequestAccepted
)
select id, num
from (
    select id, count(id) as num,
           rank() over (order by count(id) desc ) as rnk
    from all_ids
    group by id
)t0
where rnk = 1