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

WITH t1 AS (
    SELECT user_id,
           DATE(log_time) AS log_day,
           log_time,
           RANK()
               over (PARTITION BY DATE(log_time)
                   ORDER BY log_time ASC) AS ranking
    FROM login_tb
),
t2 AS (
    SELECT log_day,
           user_id
    FROM t1
    WHERE ranking = 1
    ORDER BY log_day ASC
),
t3 AS (
    SELECT log_day,
           t2.user_id,
           hobby
    FROM t2
    LEFT JOIN user_action_tb b
    ON t2.user_id = b.user_id
)
SELECT * FROM t3;

# 这道题主要是在日期排序筛选最早的用户再进行关联！