SQL面试题：手写7日留存

最新推荐文章于 2024-10-05 00:24:42 发布

话数Science

最新推荐文章于 2024-10-05 00:24:42 发布

阅读量108

点赞数

分类专栏：大数据面试文章标签： sql 大数据 1024程序员节

本文链接：https://blog.csdn.net/weixin_40035038/article/details/134090014

版权

大数据同时被 2 个专栏收录

95 篇文章 6 订阅

订阅专栏

面试

35 篇文章 3 订阅

订阅专栏

文章描述了在阿里云MaxCompute中进行数据库操作，包括创建表并插入用户登录记录，然后通过SQL查询统计不同日期范围内的用户登录活跃度。

摘要由CSDN通过智能技术生成

环境

阿里云，maxcompute

初始化

DROP TABLE IF EXISTS t_user_login;
CREATE TABLE IF NOT EXISTS t_user_login(
    user_id                    STRING COMMENT '用户id',
    login_time                  STRING COMMENT '登录时间'
) COMMENT '用户登录表';

INSERT INTO t_user_login 
SELECT 'user_1', '2023-10-28 12:26:10'
UNION
SELECT 'user_1', '2023-10-27 12:26:10'
UNION
SELECT 'user_2', '2023-10-27 12:26:10'
UNION
SELECT 'user_1', '2023-10-26 12:26:10'
UNION
SELECT 'user_2', '2023-10-26 12:26:10'
UNION
SELECT 'user_3', '2023-10-26 12:26:10'
UNION
SELECT 'user_1', '2023-10-22 12:26:10'
UNION
SELECT 'user_2', '2023-10-22 12:26:10'
UNION
SELECT 'user_3', '2023-10-22 12:26:10'
UNION
SELECT 'user_4', '2023-10-22 12:26:10'
UNION
SELECT 'user_1', '2023-10-15 12:26:10'
UNION
SELECT 'user_2', '2023-10-15 12:26:10'
UNION
SELECT 'user_3', '2023-10-15 12:26:10'
UNION
SELECT 'user_4', '2023-10-15 12:26:10'
UNION
SELECT 'user_5', '2023-10-15 12:26:10'
UNION
SELECT 'user_1', '2023-09-29 12:26:10'
UNION
SELECT 'user_2', '2023-09-29 12:26:10'
UNION
SELECT 'user_3', '2023-09-29 12:26:10'
UNION
SELECT 'user_4', '2023-09-29 12:26:10'
UNION
SELECT 'user_5', '2023-09-29 12:26:10'
UNION
SELECT 'user_6', '2023-09-29 12:26:10'
UNION
SELECT 'user_1', '2023-07-31 12:26:10'
UNION
SELECT 'user_2', '2023-07-31 12:26:10'
UNION
SELECT 'user_3', '2023-07-31 12:26:10'
UNION
SELECT 'user_4', '2023-07-31 12:26:10'
UNION
SELECT 'user_5', '2023-07-31 12:26:10'
UNION
SELECT 'user_6', '2023-07-31 12:26:10'
UNION
SELECT 'user_7', '2023-07-31 12:26:10'
;

计算

SELECT
    log_day,
    count(user_id_day0) AS login_user_cnt,
    count(user_id_day1)/count(user_id_day0) AS day1_rate,
    count(user_id_day3)/count(user_id_day0) AS day3_rate,
    count(user_id_day7)/count(user_id_day0) AS day7_rate,
    count(user_id_day14)/count(user_id_day0) AS day14_rate,
    count(user_id_day30)/count(user_id_day0) AS day30_rate,
    count(user_id_day90)/count(user_id_day0) AS day90_rate
FROM
    (
    SELECT 
        log_day,
        a.user_id_day0,
        b.user_id AS user_id_day1,
        c.user_id AS user_id_day3,
        d.user_id AS user_id_day7,
        e.user_id AS user_id_day14,
        f.user_id AS user_id_day30,
        g.user_id AS user_id_day90
    FROM
        ( SELECT  
                TO_DATE(login_time) AS log_day, 
                user_id AS user_id_day0 
                FROM 
                t_user_login 
                GROUP BY 
                user_id,
                TO_DATE(login_time)
                ) a
        LEFT JOIN t_user_login b ON DATEDIFF( TO_DATE( b.login_time ), a.log_day ) = 1 
        AND a.user_id_day0 = b.user_id
        LEFT JOIN t_user_login c ON DATEDIFF( TO_DATE( c.login_time ), a.log_day ) = 2 
        AND a.user_id_day0 = c.user_id
        LEFT JOIN t_user_login d ON datediff( TO_DATE( d.login_time ), a.log_day ) = 6 
        AND a.user_id_day0 = d.user_id
        LEFT JOIN t_user_login e ON datediff( TO_DATE( e.login_time ), a.log_day ) = 13 
        AND a.user_id_day0 = e.user_id 
        LEFT JOIN t_user_login f ON datediff( TO_DATE( f.login_time ), a.log_day ) = 29 
        AND a.user_id_day0 = f.user_id 
        LEFT JOIN t_user_login g ON datediff( TO_DATE( g.login_time ), a.log_day ) = 89 
        AND a.user_id_day0 = g.user_id 
    ) temp 
GROUP BY
    log_day
ORDER BY 
    log_day
;