排名

用户解题统计

过去一年提交了

勋章 ①金银铜：在竞赛中获得第一二三名；②好习惯：自然月10天提交；③里程碑：解决1/2/5/10/20/50/100/200题；④每周打卡挑战：完成每周5题，每年1月1日清零。

收藏

收藏日期 题目名称 解决状态
2025-06-18 时间日期（4）阶段综合-按月统计日花费，一天都不要浪费  未解决

评论笔记

评论日期 题目名称 评论内容 站长评论
2025-06-16 多类别观看指数计算  
WITH user_category_counts AS (
    SELECT 
        t20.usr_id,
        COUNT(DISTINCT t3.v_typ) AS category_count
    FROM
        bilibili_t20 t20
    JOIN
        bilibili_t3 t3 ON t20.v_id = t3.v_id
    GROUP BY
        t20.usr_id
    HAVING
        COUNT(DISTINCT t3.v_typ) >= 3
),
video_total_viewers AS (
    SELECT
        t3.v_typ,
        COUNT(DISTINCT t20.usr_id) AS total_viewers
    FROM
        bilibili_t20 t20
    JOIN
        bilibili_t3 t3 ON t20.v_id = t3.v_id
    GROUP BY
        t3.v_typ
),
video_multi_viewers AS (
    SELECT
        t3.v_typ,
        COUNT(DISTINCT ucc.usr_id) AS multi_category_viewers
    FROM
        user_category_counts ucc
    JOIN
        bilibili_t20 t20 ON ucc.usr_id = t20.usr_id
    JOIN
        bilibili_t3 t3 ON t20.v_id = t3.v_id
    GROUP BY
        t3.v_typ
)
SELECT 
    vtv.v_typ,
    vmv.multi_category_viewers,
    vtv.total_viewers,
    ROUND((vmv.multi_category_viewers * 100.0 / vtv.total_viewers), 2) AS mcv_index
FROM
    video_total_viewers vtv
JOIN
    video_multi_viewers vmv ON vtv.v_typ = vmv.v_typ
ORDER BY
   mcv_index DESC;
 
啥也没说

提交记录

提交日期 题目名称 提交代码
2025-06-18 时间日期（4）阶段综合-按月统计日花费，一天都不要浪费  
select 
	substr(trx_time,1,7) as trx_mon ,
	last_day(max(trx_time)) as last_day, 
day(last_day(max(trx_time)) ) as day_of_mon,
sum(trx_amt) as trx_amt, 
count(1) as trx_cnt,sum(trx_amt)/day(last_day(max(trx_time)) ) as avg_day_amt,
count(1)/day(last_day(max(trx_time)) ) as avg_day_cnt
from 
	cmb_usr_trx_rcd a
left join 
	cmb_mch_typ mon a.mch_nm = m.mch_nm
where 
a.usr_id=5201314520 
and ((YEAR(trx_time) = 2023) OR (YEAR(trx_time) = 2024 AND MONTH(trx_time) BETWEEN 1 AND 6))
and (m.mch_typ='休闲娱乐' or m.mch_typ is null)
and trx_amt > 288
and (TIME(trx_time) BETWEEN '23:00:00' AND '23:59:59'OR TIME(trx_time) BETWEEN '00:00:00' AND '03:00:00')
group by 
substr(a.trx_time,1,7)
order by 
 1
2025-06-18 时间日期（4）阶段综合-按月统计日花费，一天都不要浪费  
select 
	substr(trx_time,1,7) as trx_mon ,
	last_day(max(trx_time)) as last_day, 
day(last_day(max(trx_time)) ) as day_of_mon,
sum(trx_amt) as trx_amt, 
count(1) as trx_cnt,sum(trx_amt)/day(last_day(max(trx_time)) ) as avg_day_amt,
count(1)/day(last_day(max(trx_time)) ) as avg_day_cnt
from 
	cmb_usr_trx_rcd a
left join 
	cmb_mch_typ mon a.mch_nm = m.mch_nm
where 
a.usr_id=5201314520 
and (year(a.trx_time) = 2023
 or (trx_time between '2024-1-1' and '2024-7-1'))
and (m.mch_typ='休闲娱乐' or m.mch_typ is null)
and trx_amt > 288
and (TIME(trx_time) BETWEEN '23:00:00' AND '23:59:59'OR TIME(trx_time) BETWEEN '00:00:00' AND '03:00:00')
group by 
substr(a.trx_time,1,7)
order by 
 1
2025-06-18 时间日期（3）按月统计日花费，一天都不要浪费  
select
substr(trx_time,1,7) as trx_mon,
last_day(max(trx_time))as last_day,
day(last_day(max(trx_time)))as day_of_mon,
sum(trx_amt) as trx_amt	,
count(1) as trx_cnt,
sum(trx_amt)/ day(last_day(max(trx_time)))as avg_day_amt,
 count(1)/ day(last_day(max(trx_time)))as avg_day_cnt
from 
cmb_usr_trx_rcd rcd
left join
cmb_mch_typ typ on rcd.mch_nm = typ.mch_nm
where
usr_id = '5201314520' and year(trx_time) in ('2023','2024')
and mch_typ = '休闲娱乐'
group by
substr(trx_time,1,7)
order by
 1
2025-06-18 时间日期（2）按月统计日花费，一天都不要浪费  
select
substr(trx_time,1,7) as trx_mon,
last_day(max(trx_time))as last_day,
day(last_day(max(trx_time)))as day_of_mon
from 
cmb_usr_trx_rcd rcd
left join
cmb_mch_typ typ on rcd.mch_nm = typ.mch_nm
where
usr_id = '5201314520' and year(trx_time) in ('2023','2024')
and mch_typ = '休闲娱乐'
group by
substr(trx_time,1,7)
order by
 1
2025-06-18 时间日期（2）按月统计日花费，一天都不要浪费  
select
substr(trx_time,1,7) as trx_mon,
last_day(max(trx_time))as last_day,
day(last_day(max(trx_time)))as day_of_mon
from 
cmb_usr_trx_rcd rcd
left join
cmb_mch_typ typ on rcd.mch_nm = typ.mch_nm
where
usr_id = '5201314520' and year(trx_time) in ('2023','2024')
group by
substr(trx_time,1,7)
order by
 1
2025-06-18 时间日期（2）按月统计日花费，一天都不要浪费  
select
substr(trx_time,1,7) as trx_mon,
last_day(max(trx_time))as last_day,
day(last_day(max(trx_time)))as day_of_mon
from 
cmb_usr_trx_rcd rcd
left join
cmb_mch_typ typ on rcd.mch_nm = typ.mch_nm
where
usr_id = '5201314520' and year(trx_time) in ('2023','2024')
group by
substr(trx_time,1,7), day(trx_time)
order by
 1
2025-06-18 时间日期（2）按月统计日花费，一天都不要浪费  
select
substr(trx_time,1,7) as trx_mon,
last_day(max(trx_time))as last_day,
day(trx_time) as day_of_mon
from 
cmb_usr_trx_rcd rcd
left join
cmb_mch_typ typ on rcd.mch_nm = typ.mch_nm
where
usr_id = '5201314520' and year(trx_time) in ('2023','2024')
group by
substr(trx_time,1,7), day(trx_time)
order by
 1
2025-06-18 时间日期（2）按月统计日花费，一天都不要浪费  
select
substr(trx_time,1,7) as trx_mon,
last_day(max(trx_time))as last_day,
day(trx_time) as day_of_mon
from 
cmb_usr_trx_rcd rcd
left join
cmb_mch_typ typ on rcd.mch_nm = typ.mch_nm
where
usr_id = '5201314520' and year(trx_time) in ('2023','2024')
group by
substr(trx_time,1,7), day(trx_time)
2025-06-18 时间日期（1）按月统计日花费，一天都不要浪费  
select
substr(trx_time,1,7) as trx_mon,
last_day(max(trx_time))as last_day
from 
cmb_usr_trx_rcd rcd
left join
cmb_mch_typ typ on rcd.mch_nm = typ.mch_nm
where
usr_id = '5201314520' and year(trx_time) in ('2023','2024')
group by
substr(trx_time,1,7);
2025-06-18 时间日期（3）按月统计日花费，一天都不要浪费  
select
substr(rcd.trx_time,1,7) as trx_mon,	
last_day(max(rcd.trx_time))last_day	,
day(last_day(max(rcd.trx_time))) as day_of_mon,
sum(rcd.trx_amt) as trx_amt, 
count(*) as trx_cnt,
sum(trx_amt)/day(last_day(max(trx_time)) ) as avg_day_amt,
count(*)/day(last_day(max(trx_time)) ) as avg_day_cnt
from 
cmb_usr_trx_rcd rcd
left join 
cmb_mch_typ typ on rcd.mch_nm = typ.mch_nm
where 
rcd.usr_id= '5201314520' and (trx_time like '2023%' or trx_time like '2024%') and typ.mch_typ='休闲娱乐'
group by substr(rcd.trx_time,1,7)
order by 1
2025-06-18 时间日期（3）按月统计日花费，一天都不要浪费  
select
substr(rcd.trx_time,1,7) as trx_mon,	
last_day(max(rcd.trx_time))last_day	,
day(last_day(max(rcd.trx_time))) as day_of_mon,
sum(rcd.trx_amt) as trx_amt, 
count(1) as trx_cnt,
sum(trx_amt)/day(last_day(max(trx_time)) ) as avg_day_amt,
count(1)/day(last_day(max(trx_time)) ) as avg_day_cnt
from 
cmb_usr_trx_rcd rcd
left join 
cmb_mch_typ typ on rcd.mch_nm = typ.mch_nm
where 
rcd.usr_id= '5201314520' and (trx_time like '2023%' or trx_time like '2024%') and typ.mch_typ='休闲娱乐'
group by substr(rcd.trx_time,1,7)
order by 1
2025-06-18 时间日期（3）按月统计日花费，一天都不要浪费  
select
substr(rcd.trx_time,1,7) as trx_mon,	
last_day(max(rcd.trx_time))last_day	,
day(last_day(max(rcd.trx_time))) as day_of_mon,
sum(rcd.trx_amt) as trx_amt, 
count(1) as trx_cnt,
sum(trx_amt)/day(last_day(max(trx_time)) ) as avg_day_amt,
count(1)/day(last_day(max(trx_time)) ) as avg_day_cnt
from 
cmb_usr_trx_rcd rcd
left join 
cmb_mch_typ typ on rcd.mch_nm = typ.mch_nm
where 
rcd.usr_id= '5201314520' and trx_time like '2023%' or trx_time like '2024%' and typ.mch_typ='休闲娱乐'
group by substr(rcd.trx_time,1,7)
order by 1
2025-06-18 时间日期（3）按月统计日花费，一天都不要浪费  
SELECT
trx_mon,
last_day,
day_of_mon,
SUM(trx_amt) AS trx_amt,
COUNT(*) AS trx_cnt,
round(AVG(avg_day_amt),2) AS avg_day_amt,
COUNT(*) / day_of_mon AS avg_day_cnt
FROM (
SELECT
SUBSTR(trx_time, 1, 7) AS trx_mon,
LAST_DAY(trx_time) AS last_day,
DAY(LAST_DAY(trx_time)) AS day_of_mon,
trx_amt,
AVG(trx_amt) OVER (PARTITION BY SUBSTR(trx_time, 1, 7)) AS avg_day_amt
FROM
cmb_usr_trx_rcd rcd
LEFT JOIN
cmb_mch_typ typ ON rcd.mch_nm = typ.mch_nm
WHERE
usr_id = '5201314520'
AND (trx_time LIKE '2023%' OR trx_time LIKE '2024%')
AND mch_typ = '休闲娱乐'
) AS subquery
GROUP BY
trx_mon, last_day, day_of_mon
ORDER BY
trx_mon;
2025-06-18 时间日期（2）按月统计日花费，一天都不要浪费  
select
substr(trx_time,1,7) as trx_mon,
last_day(max(trx_time)) as last_day,
 day(last_day(max(trx_time)) ) as day_of_mon
from
cmb_usr_trx_rcd rcd
join
 cmb_mch_typ typ on rcd.mch_nm = typ.mch_nm
where
(trx_time like '2023%' or trx_time like '2024%')
and usr_id= '5201314520' and mch_typ = '休闲娱乐'
group by
substr(trx_time,1,7)
order by
 1
2025-06-18 时间日期（2）按月统计日花费，一天都不要浪费  
select
substr(trx_time,1,7) as trx_mon,
last_day(max(trx_time)) as last_day,
 day(last_day(max(trx_time)) ) as day_of_mon
from
cmb_usr_trx_rcd rcd
join
 cmb_mch_typ typ on rcd.mch_nm = typ.mch_nm
where
trx_time like '2023%' or trx_time like '2024%'
and usr_id= '5201314520' and mch_typ = '休闲娱乐'
group by
substr(trx_time,1,7)
order by
 1
2025-06-18 时间日期（2）按月统计日花费，一天都不要浪费  
select
substr(trx_time,1,7) as trx_mon,
last_day(max(trx_time)) as last_day,
 day(last_day(max(trx_time)) ) as day_of_mon
from
cmb_usr_trx_rcd rcd
join
 cmb_mch_typ typ on rcd.mch_nm = typ.mch_nm
where
trx_time like '2023%' or trx_time like '2024%'
and usr_id= '5201314520'
group by
substr(trx_time,1,7)
order by
 1
2025-06-18 时间日期（2）按月统计日花费，一天都不要浪费  
select
substr(trx_time,1,7) as trx_mon,
last_day(max(trx_time)) as last_day,
 day(last_day(max(trx_time)) ) as day_of_monday
from
cmb_usr_trx_rcd rcd
join
 cmb_mch_typ typ on rcd.mch_nm = typ.mch_nm
where
trx_time like '2023%' or trx_time like '2024%'
and usr_id= '5201314520'
group by
substr(trx_time,1,7)
order by
 1
2025-06-17 条件过滤（1）异地男友有异常，数分闺蜜来帮忙  
select * from cmb_usr_trx_rcd
where usr_id = '5201314520'
and date(trx_time) between '2024-9-1' and '2024-9-30'
order by trx_amt desc
2025-06-17 条件过滤（1）异地男友有异常，数分闺蜜来帮忙  
select * from cmb_usr_trx_rcd
where usr_id = '5201314520'
and trx_time between '2024-9-1' and '2024-9-30'
2025-06-16 多类别观看指数计算  
WITH user_category_counts AS (
SELECT 
t20.usr_id,
COUNT(DISTINCT t3.v_typ) AS category_count
FROM
bilibili_t20 t20
JOIN
bilibili_t3 t3 ON t20.v_id = t3.v_id
GROUP BY
t20.usr_id
HAVING
COUNT(DISTINCT t3.v_typ) >= 3
),
video_total_viewers AS (
SELECT
t3.v_typ,
COUNT(DISTINCT t20.usr_id) AS total_viewers
FROM
bilibili_t20 t20
JOIN
bilibili_t3 t3 ON t20.v_id = t3.v_id
GROUP BY
t3.v_typ
),
video_multi_viewers AS (
SELECT
t3.v_typ,
COUNT(DISTINCT ucc.usr_id) AS multi_category_viewers
FROM
user_category_counts ucc
JOIN
bilibili_t20 t20 ON ucc.usr_id = t20.usr_id
JOIN
bilibili_t3 t3 ON t20.v_id = t3.v_id
GROUP BY
t3.v_typ
)
SELECT 
vtv.v_typ,
vmv.multi_category_viewers,
vtv.total_viewers,
ROUND((vmv.multi_category_viewers * 100.0 / vtv.total_viewers), 2) AS mcv_index
FROM
video_total_viewers vtv
JOIN
video_multi_viewers vmv ON vtv.v_typ = vmv.v_typ
ORDER BY
 mcv_index DESC;