校招算法笔面试 | SQL笔试面试编程题-牛客每个人最近的登录日期(四)

题目

题目链接

这道题目要求我们统计每个日期的新用户登录数量。我们要做的事情如下：

1. 确定总体问题

我们需要统计每个日期的新用户登录数量，新用户定义为在某个日期首次登录的用户。即从登录记录表中提取数据，使用窗口函数ROW_NUMBER来标记每个用户的首次登录记录，然后按日期分组统计新用户的数量，并按日期升序排序输出。

2. 分析关键问题

• 标记首次登录记录：使用ROW_NUMBER窗口函数为每个用户的登录记录分配排名，按日期升序排列，标记出首次登录记录。
• 统计新用户数量：通过CASE语句和SUM函数统计每个日期的新用户数量。
• 分组和排序输出：按日期分组统计，并按日期升序排列输出结果。

3. 解决每个关键问题的代码及讲解

步骤1：标记首次登录记录

我们使用ROW_NUMBER窗口函数为每个用户的登录记录分配排名，按日期升序排列：

select
    date,
    row_number() over(partition by user_id order by date) as rk
from login

• ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date) AS rk：为每个用户的登录记录分配排名，按用户分组，按日期升序排列。某一日的新用户即该用户当天是所有登陆日期中最早的一天，rk = 1表示首次登录记录。

步骤2：统计新用户数量

我们使用CASE语句和SUM函数统计每个日期的新用户数量：

select
    date,
    sum(case when rk = 1 then 1 else 0 end) as new
from(
    ...
) s
group by date

• SUM(CASE WHEN rk = 1 THEN 1 ELSE 0 END) AS new：统计每个日期的新用户数量。

步骤3：分组和排序输出

我们按日期分组统计，并按日期升序排列输出结果：

group by date

• GROUP BY date：按日期分组统计。

完整代码

select
    date,
    sum(case when rk = 1 then 1 else 0 end) as new
from(
    select
        date,
        row_number() over(partition by user_id order by date) as rk
    from login
) s
group by date;