每日SQL day2

1. 每天人均浏览时长

select
    date(in_time) as dt,
    round(sum(timestampdiff(second,in_time, out_time))/count(distinct uid),1) as avg_viiew_len_sec
from tb_user_log
where date_format(in_time, "%Y-%m") = "2021-11" and artical_id!=0
group by dt
order by avg_viiew_len_sec

2. 每篇文章同一时刻最大在看人数

SELECT 
    artical_id,
    max(instant_view) max_uv
FROM(
    SELECT 
        artical_id,
        sum(diff)over(partition by artical_id order by dt,diff DESC) instant_view
    FROM(
        SELECT 
            artical_id,
            in_time dt,
            1 diff
        FROM tb_user_log 
        WHERE artical_id!=0
        UNION ALL
        SELECT 
            artical_id,
            out_time dt,
            -1 diff
        FROM tb_user_log
        WHERE artical_id!=0
        ) a
     )b 
GROUP BY artical_id
ORDER BY max_uv DESC;

3. 每天新用户的次日留存率

select 
    t1.dt,
    round(count(t2.uid)/count(t1.uid),2) uv_rate
from (
    select 
        uid,
        min(date(in_time)) dt
    from tb_user_log 
    group by uid
) as t1  -- 每天新用户表
left join (
    select 
        uid, 
        date(in_time) dt
    from tb_user_log
    union
       select 
            uid,
            date(out_time)
       from tb_user_log
) as t2 -- 用户活跃表
on t1.uid=t2.uid and t1.dt=date_sub(t2.dt,INTERVAL 1 day)
where date_format(t1.dt,'%Y-%m') = '2021-11'
group by t1.dt
order by t1.dt

4. 活跃间隔对用户分级

SELECT user_grade, ROUND(COUNT(uid) / MAX(user_cnt), 2) as ratio
FROM (
    SELECT uid, user_cnt,
        CASE
            WHEN last_dt_diff >= 30 THEN "流失用户"
            WHEN last_dt_diff >= 7 THEN "沉睡用户"
            WHEN first_dt_diff < 7 THEN "新晋用户"
            ELSE "忠实用户"
        END as user_grade
    FROM (
        SELECT uid, user_cnt,
            TIMESTAMPDIFF(DAY,first_dt,cur_dt) as first_dt_diff,  # 计算第一次登录是几天前
            TIMESTAMPDIFF(DAY,last_dt,cur_dt) as last_dt_diff     # 计算最后一次登录是几天前
        FROM (
            SELECT uid, MIN(DATE(in_time)) as first_dt,
                MAX(DATE(out_time)) as last_dt
            FROM tb_user_log
            GROUP BY uid #每个用户最早登录时间和最晚登出时间
        ) as t_uid_first_last
        LEFT JOIN (
            SELECT MAX(DATE(out_time)) as cur_dt, # 当前日期
                COUNT(DISTINCT uid) as user_cnt   # 总用户数
            FROM tb_user_log
        ) as t_overall_info ON 1
    ) as t_user_info
) as t_user_grade
GROUP BY user_grade
ORDER BY ratio DESC;

5. 每天日活数及新用户占比

select
    t4.dt as dt,
    t4.user_cnt as dau,
    if(t3.new_user_cnt is null,0.00,round(t3.new_user_cnt/t4.user_cnt,2)) as uv_new_ratio
from(
    select
        first_dt,
        count(uid) as new_user_cnt  #每天新用户人数
    from( 
        select
            uid,
            min(date(in_time)) as first_dt
        from tb_user_log
        group by uid
    ) as t1
    group by first_dt#每个用户首次登录时间表
) as t3
right join (
    select
        dt,
        count(distinct uid) as user_cnt
    from(
        select
            uid,
            date(in_time) as dt
        from tb_user_log
        union
        select
            uid,
            date(out_time) as dt
        from tb_user_log
    ) as t2
    group by dt
) as t4
on t3.first_dt = t4.dt
order by dt

 6. 连续签到领金币

 

WITH t1 AS( -- t1表筛选出活动期间内的数据，并且为了防止一天有多次签到活动，distinct 去重
	SELECT
		DISTINCT uid,
		DATE(in_time) dt,
		DENSE_RANK() over(PARTITION BY uid ORDER BY DATE(in_time)) rn -- 编号
	FROM
		tb_user_log
	WHERE
		DATE(in_time) BETWEEN '2021-07-07' AND '2021-10-31' AND artical_id = 0 AND sign_in = 1
),
t2 AS (
	SELECT
	*,
	DATE_SUB(dt,INTERVAL rn day) dt_tmp, 
	case DENSE_RANK() over(PARTITION BY DATE_SUB(dt,INTERVAL rn day),uid ORDER BY dt )%7 -- 再次编号
		WHEN 3 THEN 3
		WHEN 0 THEN 7
		ELSE 1
	END as day_coin -- 用户当天签到时应该获得的金币数
	FROM
	t1
)
	SELECT
		uid,
        DATE_FORMAT(dt,'%Y%m') `month`,
        sum(day_coin) coin  -- 总金币数
	FROM t2
	GROUP BY uid,DATE_FORMAT(dt,'%Y%m')
	ORDER BY DATE_FORMAT(dt,'%Y%m'),uid