题解 | #牛客每个人最近的登录日期(四)#

# 1.首先提取每个用户的最早登陆日期表m
# 2.以login为左表左连接m表，两个条件user_id相等，l.date=m.min_date
# 3.以date为分组统计m.user_id出现的次数
SELECT
    l.date,
    count( m.user_id ) 
FROM
    login l
    LEFT JOIN ( SELECT user_id, min( date ) min_date FROM login GROUP BY user_id ) m ON l.user_id = m.user_id 
    AND l.date = m.min_date 
GROUP BY
    l.date;

# 1. 首先提取每个用户的最早登陆日期表
# 2. 按照date分组计算( user_id, date )同时出现在“最早登陆日期表”中的次数


SELECT
    date,
    sum(( user_id, date ) IN ( SELECT user_id, min( date ) min_date FROM login GROUP BY user_id ) ) new 
FROM
    login 
GROUP BY
    date 
ORDER BY
    date;

# 1.利用first_value窗口函数求第一次登陆的日期
# 2.以date分组用sum( date = f_date ) 为首次登陆的次数

SELECT
    date,
    sum( date = f_date ) 
FROM
    ( SELECT user_id, date, first_value( date ) over ( PARTITION BY user_id ORDER BY date ) f_date FROM login ) f 
GROUP BY
    date;