牛客sql大厂真题

某音短视频

用户增长场景

SQL162 2021年11月每天的人均浏览文章时长

在 MySQL 中，可以使用 TIME() 函数或DATE(), HOUR(), MINUTE(), SECOND() 分别提取时间的各个部分。timestampdiff(数据位，)

distinct可以用在子查询 不必跟select

SELECT 
    DATE(t.out_time) AS dt, 
    round(sum(TIMESTAMPDIFF(SECOND, t.in_time, t.out_time))/count(distinct t.uid),1) AS avg_viiew_len_sec
FROM 
    tb_user_log t
WHERE 
    t.artical_id != 0 and month(t.out_time)=11
GROUP BY 
    DATE(t.out_time)
ORDER BY 
    avg_viiew_len_sec;

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

SELECT artical_id, MAX(cnt) AS max_uv 
FROM (
  SELECT artical_id, 
         SUM(num) OVER (PARTITION BY artical_id ORDER BY dt ASC, num DESC) AS cnt
  FROM (
    SELECT artical_id, in_time AS dt, 1 AS num 
    FROM tb_user_log
    WHERE artical_id != 0
    
    UNION ALL
    
    SELECT artical_id, out_time AS dt, -1 AS num 
    FROM tb_user_log
    WHERE artical_id != 0
  ) AS a
) AS b
GROUP BY artical_id
ORDER BY max_uv DESC

sum over 可以错位计算累积值

SQL164 2021年11月每天新用户的次日留存率

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

SQL166 每天的日活数及新用户占比

WITH t1 AS(
SELECT DISTINCT uid,DATE(in_time) dt,MIN(DATE(in_time))OVER(PARTITION BY uid) new_dt FROM tb_user_log
UNION
SELECT DISTINCT uid,DATE(out_time) dt,MIN(DATE(in_time))OVER(PARTITION BY uid) new_dt FROM tb_user_log
)
SELECT dt,COUNT(1) dau,ROUND(SUM(IF(dt=new_dt,1,0))/COUNT(1),2) uv_new_ratio
FROM t1 GROUP BY dt ORDER BY dt ASC;

SQL167 连续签到领金币

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;