排名

用户解题统计

过去一年提交了

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

收藏

收藏日期 题目名称 解决状态
2025-07-01 钢铁直男的观看记录  已解决
2025-07-01 每天新增用户的会员转化比例  已解决
2025-07-01 哔哩哔哩面试真题（2）计算春节周会员收入  已解决
2025-06-30 抖音面试真题（3）一次性计算T+3、T+7、T+14留存率（2）  未解决

评论笔记

评论日期 题目名称 评论内容 站长评论
没有评论过的题目。

提交记录

提交日期 题目名称 提交代码
2025-07-01 会员与非会员的日均观看视频数量  
with duiying as(select t100.m_flg,t100.usr_id
,v_id,v_tm
from bilibili_t100 t100 
left join 
bilibili_t20 t20 
on t100.usr_id=t20.usr_id
where date(v_tm)between '2021-02-01' and '2021-02-28'
),
ccnt as(
select 
m_flg
,usr_id
,date(v_tm)
,count(v_id) as cnt from duiying
group by 1,2,3
)
select m_flg,round(avg(cnt),2)
from ccnt
group by 1
2025-07-01 多类别观看指数计算  
WITH UserVideoTypes AS (
    SELECT
        t20.usr_id,
        t3.v_typ
    FROM
        bilibili_t20 t20
    JOIN
        bilibili_t3 t3 ON t20.v_id = t3.v_id
),
UserViewCategories AS (
    SELECT
        usr_id,
        COUNT(DISTINCT v_typ) AS view_category_count
    FROM
        UserVideoTypes
    GROUP BY
        usr_id
),
FilteredUsers AS (
    SELECT
        usr_id
    FROM
        UserViewCategories
    WHERE
        view_category_count >= 3
),
VideoCategoryCounts AS (
    SELECT
        v_typ,
        COUNT(DISTINCT usr_id) AS total_viewers
    FROM
        UserVideoTypes
    GROUP BY
        v_typ
),
MultiCategoryViewers AS (
    SELECT
        v_typ,
        COUNT(DISTINCT ut.usr_id) AS multi_category_viewers
    FROM
        UserVideoTypes ut
    JOIN
        FilteredUsers fu ON ut.usr_id = fu.usr_id
    GROUP BY
        v_typ
),
MCVI AS (
    SELECT
        vc.v_typ,
        mc.multi_category_viewers,
        vc.total_viewers,
        (mc.multi_category_viewers * 100.0 / vc.total_viewers) AS mcv_index
    FROM
        VideoCategoryCounts vc
    LEFT JOIN
        MultiCategoryViewers mc ON vc.v_typ = mc.v_typ
)
SELECT
    v_typ,
    multi_category_viewers,
    total_viewers,
    mcv_index
FROM
    MCVI
ORDER BY
    mcv_index DESC;
2025-07-01 多类别观看指数计算  
with duiying as(select t20.usr_id,t3.v_typ
from
bilibili_t20 t20
join bilibili_t3 t3
on t20.v_id=t3.v_id
group by 1,2
)
,duoge as(
select usr_id
from(
select usr_id,count(distinct v_typ) cnt
from
duiying
group by
1)a
where cnt>=3)
,duogetyp as(
select y.v_typ,count(d.usr_id) dcnt
from duiying y
join duoge d
on y.usr_id=d.usr_id
group by 1)
,aaa as
(select
 v_typ,count(usr_id) as cnt 
 from duiying group by 1
 )
selecta.v_typ,dcnt as multi_category_viewers
,cnt total_viewers
,dcnt/cnt*100 mcv_index
from duogetyp p 
left joinaaa a
on a.v_typ=p.v_typ
order by mcv_index desc
2025-07-01 多类别观看指数计算  
with duiying as(select t20.usr_id,t3.v_typ
from
bilibili_t20 t20
join bilibili_t3 t3
on t20.v_id=t3.v_id
group by 1,2
)
,duoge as(
select usr_id
from(
select usr_id,count(distinct v_typ) cnt
from
duiying
group by
1)a
where cnt>=3)
,duogetyp as(
select y.v_typ,count(d.usr_id) dcnt
from duiying y
join duoge d
on y.usr_id=d.usr_id
group by 1)
,aaa as
(select
 v_typ,count(usr_id) as cnt 
 from duiying group by 1
 )
selecta.v_typ,dcnt as multi_category_viewers
,cnt total_viewers
,round(dcnt/cnt*100,2)mcv_index
from duogetyp p 
left joinaaa a
on a.v_typ=p.v_typ
order by mcv_index desc
2025-07-01 多类别观看指数计算  
with duiying as(select t20.usr_id,t3.v_typ
from
bilibili_t20 t20
join bilibili_t3 t3
on t20.v_id=t3.v_id
group by 1,2
)
,duoge as(
select usr_id
from(
select usr_id,count(distinct v_typ) cnt
from
duiying
group by
1)a
where cnt>=3)
,duogetyp as(
select y.v_typ,count(d.usr_id) dcnt
from duiying y
join duoge d
on y.usr_id=d.usr_id
group by 1)
,aaa as
(select
 v_typ,count(usr_id) as cnt 
 from duiying group by 1
 )
selecta.v_typ,dcnt,cnt,round(dcnt/cnt*100,2)rate
from duogetyp p 
left joinaaa a
on a.v_typ=p.v_typ
order by rate desc
2025-07-01 钢铁直男的观看记录  
with duiying as(select t20.usr_id,t3.v_typ
from
bilibili_t20 t20
join bilibili_t3 t3
on t20.v_id=t3.v_id
)
,users as(
select usr_id
from(
select
usr_id
from(
 select usr_id,group_concat(distinct v_typ 
order by v_typ) tag
 from duiying
 group by 1
 )a
where tag in('汽车','IT','IT,汽车')) b
 )
 select 
 t20.usr_id,
    t20.v_id,
    t20.v_tm
 from
 bilibili_t20 t20
 join users u
 on u.usr_id=t20.usr_id
 order by v_tm
2025-07-01 只观看放映厅的用户  
with duiying as(select t20.usr_id,t3.v_typ
from 
bilibili_t20 t20
left join bilibili_t3 t3
on t20.v_id=t3.v_id
group by 1,2
)
select
count(a.usr_id)
from(
 select usr_id,count(v_typ) cnt
 from duiying 
 group by 1
 )a 
 left join
 duiying d 
 on a.usr_id=d.usr_id
 where cnt=1 and d.v_typ='放映厅'
2025-07-01 每天新增用户的会员转化比例  
with minlogin as(
select usr_id,min(v_date) mindate
from bilibili_t100 
group by 1
),
newlogin as(
select mindate,count(usr_id) cnt 
from minlogin
group by 1
order by 1
)
,minflg as(
select
mindate,count(t.usr_id) fcnt
from
minlogin m
left join bilibili_t100 t 
onm.usr_id=t.usr_id
wherem_flg=1 andm.mindate=t.v_date
group by 1
)
select 
g.mindate,g.cnt as new_users,
f.fcnt as new_members,
round(100*f.fcnt/g.cnt ,2)
from newlogin g 
left join
minflg f 
on g.mindate=f.mindate
order by 1
2025-07-01 每天新增会员  
select
asminflg
,count(usr_id) cnt
from
(select usr_id,min(v_date) asminflg
from bilibili_t100 
 where m_flg=1
 group by usr_id)a 
 group by asminflg
 order by asminflg
2025-07-01 每天新增会员  
select usr_id,min(v_date) asminflg
from bilibili_t100 
 where m_flg=1
 group by usr_id
2025-07-01 哔哩哔哩面试真题（6）全年会员收入分类  
with biym as
(select *,round((datediff(end_date,begin_date)+1)/30,0) as ym
 ,datediff(end_date,begin_date)+1 as duration
from bilibili_m1
)
,didate as(
select m_date
 from bilibili_m2
 where y_m between '2021-01' and '2021-12'
)
 ,biunion as(
select m_date
 ,ym
 ,user_id,begin_date,end_date,pay_amount/duration money
 from didate d 
 left joinbiym m1
 on d.m_date between m1.begin_date and m1.end_date
 )
 select
 sum(case when a.ym=12 thentt end) as yearly_card
 ,sum(case when a.ym=6 thentt end) as half_yearly_card
 ,sum(case when a.ym=3 thentt end) as quarterly_card
 ,sum(case when a.ym=1 thentt end) as monthly_card
	,round(sum(tt),2) as total_income
 from(
 select ym,sum(money) tt from
 biunion
 group by ym)a
2025-07-01 哔哩哔哩面试真题（6）全年会员收入分类  
with biym as
(select *,round((datediff(end_date,begin_date)+1)/30,0) as ym
 ,datediff(end_date,begin_date)+1 as duration
from bilibili_m1
)
,didate as(
select m_date
 from bilibili_m2
 where y_m between '2021-01' and '2021-12'
)
 ,biunion as(
select m_date
 ,ym
 ,user_id,begin_date,end_date,pay_amount/duration money
 from didate d 
 left joinbiym m1
 on d.m_date between m1.begin_date and m1.end_date
 )
 select
 sum(case when a.ym=12 thentt end) as yearly_card
 ,sum(case when a.ym=6 thentt end) as half_yearly_card
 ,sum(case when a.ym=3 thentt end) as quarterly_card
 ,sum(case when a.ym=1 thentt end) as monthly_card
	,sum(tt) as total_income
 from(
 select ym,sum(money) tt from
 biunion
 group by ym)a
2025-07-01 哔哩哔哩面试真题（5）每月分摊会员收入  
with bisingle as(
select *,datediff(end_date,begin_date)+1 as diff
from bilibili_m1
)
,didate as(
select m_date,y_m
 from bilibili_m2
 where y_m between '2021-01' and '2021-12'
)
 ,biunion as(
select m_date
 ,y_m
 ,user_id,begin_date,end_date,pay_amount/diff money
 from didate d 
 left join bisingle m1
 on d.m_date between m1.begin_date and m1.end_date
 )
 select y_m,round(sum(money),2)
 from biunion
 group by 1
2025-07-01 哔哩哔哩面试真题（4）每周分摊会员收入  
with bisingle as(
select *,datediff(end_date,begin_date)+1 as diff
from bilibili_m1
)
,didate as(
select m_date
 from bilibili_m2
 where y_m between '2021-01' and '2021-12'
)
 ,biunion as(
select m_date
 ,FLOOR((DAYOFYEAR(m_date) - 1) / 7) + 1 as nweek
 ,user_id,begin_date,end_date,pay_amount/diff money
 from didate d 
 left join bisingle m1
 on d.m_date between m1.begin_date and m1.end_date
 )
 select nweek,round(sum(money),2)
 from biunion
 group by 1
2025-07-01 哔哩哔哩面试真题（3）计算2月会员收入  
with bisingle as (select *
,pay_amount/(datediff(end_date,begin_date)+1) as single_amount
from bilibili_m1)
,bidate as(
select m_date
from bilibili_m2
where y_m='2021-02'
)
 ,hebing as(
select d.m_date,user_id,begin_date,end_date,single_amount
from bidate d 
join bisingle s 
on d.m_date between s.begin_date and s.end_date
 )
 select round(sum(single_amount),2)
 from hebing
2025-07-01 哔哩哔哩面试真题（2）计算春节周会员收入  
with b1 as(
select m1.user_id
,		datediff(m1.end_date,m1.begin_date)+1 duration
,		m1.pay_amount
,datediff('2022-02-06',m1.begin_date)+1 validduration
	from 
 	bilibili_m1 m1
wherem1.end_date>='2022-02-06' andm1.begin_date>'2022-01-31'
)
,b2 as(
	select m2.user_id
,		datediff(m2.end_date,m2.begin_date)+1 duration
,		m2.pay_amount
,datediff('2022-02-06','2022-01-31')+1 validduration
from bilibili_m1 m2
wherem2.end_date>='2022-02-06' andm2.begin_date<='2022-01-31'
)
,b3 as(
select m3.user_id
,		datediff(m3.end_date,m3.begin_date)+1 duration
,		m3.pay_amount
,datediff(end_date,'2022-01-31')+1 validduration
	from 
 	bilibili_m1 m3
wherem3.end_date<'2022-02-06' andm3.begin_date<='2022-01-31'
and m3.end_date>='2022-01-31'
)
select round(sum(pay_amount/duration*validduration),2)
from(
select * from b1
union
select * from b2
union 
select * from b3
)a
2025-07-01 收到520红包用户的平均年龄  
SELECT 
    ROUND(AVG(DATEDIFF('2021-02-13', u.bth_dt) / 365), 2) AS avg_age
FROM 
    tx_red_pkt_rcd r
JOIN 
    tx_usr_bas_info u ON r.rcv_usr_id = u.usr_id
WHERE 
    DATE(r.rcv_datetime) = '2021-02-13'
    AND r.pkt_amt = "520.00";
2025-07-01 计算红包接收率  
SELECT 
    COUNT(*) AS total_sent,
    SUM(CASE WHEN rcv_datetime != '1900-01-01 00:00:00' THEN 1 ELSE 0 END) AS received_count,
    ROUND(SUM(CASE WHEN rcv_datetime != '1900-01-01 00:00:00' THEN 1 ELSE 0 END) / NULLIF(COUNT(*), 0) * 100, 2) AS receive_rate
FROM 
    tx_red_pkt_rcd
WHERE 
    DATE(snd_datetime) = '2021-02-13';
2025-07-01 整体的点击率  
WITH search_click AS (
    SELECT 
        q.usr_id,
        q.key_word,
        c.click_page_id,
        q.search_tm,
        q.session_id
    FROM jx_query_rcd q
    LEFT JOIN jx_click_rcd c ON q.usr_id = c.usr_id AND q.session_id = c.session_id
)
SELECT 
    COUNT(search_tm) AS total_searches,
    COUNT(click_page_id) AS total_clicks,
    ROUND((COUNT(click_page_id) / NULLIF(COUNT(search_tm), 0)) * 100, 2) AS click_rate
FROM search_click;
2025-07-01 整体的点击率  
WITH search_click AS (
    SELECT 
        q.usr_id,
        q.key_word,
        c.click_page_id,
        q.search_tm,
        q.session_id
    FROM jx_query_rcd q
    LEFT JOIN jx_click_rcd c ON q.usr_id = c.usr_id AND q.session_id = c.session_id
)
SELECT 
    COUNT(session_id) AS total_searches,
    COUNT(click_page_id) AS total_clicks,
    ROUND((COUNT(click_page_id) / NULLIF(COUNT(search_tm), 0)) * 100, 2) AS click_rate
FROM search_click;