题解 | 每天登陆最早的用户的内容喜好

-- 请查询每天登陆最早的用户的内容喜好？
-- 注：如果当天出现多个用户同时最早登录，那么这些用户均需要输出
-- 输出日期、用户id、用户内容喜好，结果按照日期升序排序

-- 首先用窗口函数DENSE_RANK，要求输出并列的
WITH T1 AS (SELECT user_id, DATE(log_time) AS login_date,
DENSE_RANK() OVER (PARTITION BY DATE(log_time) ORDER BY log_time)
AS DR FROM login_tb),

T2 AS (SELECT * FROM T1 WHERE DR <= 1)

SELECT T2.login_date AS log_day, T2.user_id, U.hobby
FROM T2 
LEFT JOIN user_action_tb U
ON T2.user_id = U.user_id
ORDER BY T2.login_date