排名

用户解题统计

过去一年提交了

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

错题集 数据思维刷题中答错的题目

模块 知识点 题目 你的答案 正确答案 操作
业务决策 项目管理-突发问题 产品经理Michael如何应对延期？ A C 重做

收藏

收藏日期 题目名称 解决状态
没有收藏的题目。

评论笔记

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

提交记录

提交日期 题目名称 提交代码
2026-05-20 用户连续骑行天数TOP10  
with a as(
select 
user_id,date(start_time) as time1,
row_number()over(partition by user_id order by date(start_time) ) as no
from hello_bike_riding_rcd 
),
b as (
select user_id,time1,no,
time1 - interval no day as gap
from a 
),
c as (
select user_id,gap,
count(gap) as steak
from b
group by user_id,gap
)
select * from c
order by steak desc 
limit 10
2026-05-08 每个视频类型的T+3留存率  
with a as (
select v_typ, date(v_tm) as d1, t20.usr_id
from bilibili_t3 t3
join bilibili_t20 t20 using(v_id)
),
a1 as (
select v_typ, usr_id
from a
where d1 = '2021-02-05'
),
a3 as (
select distinct usr_id
from a
where d1 between '2021-02-06' and '2021-02-08'
)
select
a1.v_typ,
count(distinct a1.usr_id) as total_views,
count(distinct a3.usr_id) as retained_users,
round(count(distinct a3.usr_id) / count(distinct a1.usr_id) * 100, 2) as retention_rate
from a1
left join a3 on a1.usr_id = a3.usr_id
group by a1.v_typ
order by retention_rate desc
2026-05-08 每个视频类型的T+3留存率  
with a as(
select t20.usr_id, date(t20.v_tm) as v_date, t3.v_typ
from bilibili_t20 t20
join bilibili_t3 t3 using(v_id)
where date(t20.v_tm) between '2021-02-05' and '2021-02-08'
),
b as (
select
a1.v_typ,
count(distinct a1.usr_id) as total_views,
count(distinct a2.usr_id) as retained_users
from a a1
left join a a2
on a1.usr_id = a2.usr_id
and a2.v_date between '2021-02-06' and '2021-02-08'
where a1.v_date = '2021-02-05'
group by a1.v_typ
)
select
v_typ,
total_views,
retained_users,
round(retained_users / total_views * 100, 2) as retention_rate
from b
order by retention_rate desc
2026-05-08 每个视频类型的T+3留存率  
with a as(
select t20.usr_id, date(t20.v_tm) as v_date, t3.v_typ
from bilibili_t20 t20
join bilibili_t3 t3 using(v_id)
where date(t20.v_tm) between '2021-02-05' and '2021-02-08'
),
b as (
select
a1.v_typ,
count(distinct a1.usr_id) as total_views,
count(distinct a2.usr_id) as retained_users
from a a1
left join a a2
on a1.usr_id = a2.usr_id
and a2.v_date between '2021-02-06' and '2021-02-08'
where a1.v_date = '2021-02-05'
group by a1.v_typ
)
select
v_typ,
total_views,
retained_users,
concat(round(retained_users / total_views * 100, 2), '%') as retention_rate
from b
order by retention_rate desc
2026-05-08 抖音面试真题（1）T+1日留存率  
with a as(
select distinct 
        usr_id,
        date(login_time) as login_date 
    from 
        user_login_log 
    where 
        datediff(current_date, date(login_time)) <= 30
order by login_date
),
b as (
select
a1.login_date,
count(distinct a1.usr_id) as people,
count(distinct a2.usr_id) as t1people
from a a1
left join a a2
on a1.usr_id = a2.usr_id
and a2.login_date = a1.login_date + 1
group by a1.login_date
)
select 
 login_date,
concat(round(t1people / people * 100, 2),'%') as T1_retention_rate
from b
2026-05-08 抖音面试真题（1）T+1日留存率  
with a as(
select distinct 
        usr_id,
        date(login_time) as login_date 
    from 
        user_login_log 
    where 
        datediff(current_date, date(login_time)) <= 30
order by login_date
),
b as (
select login_date,
count(*) as people
from a 
group by login_date
),
c as (
select b1.login_date,b1.people,b2.login_date as T1date,b2.people as t1people
from b b1
join b b2
on b1.login_date = b2.login_date - 1
)
select login_date,
concat(round(t1people / people * 100, 2),'%') as T1_retention_rate
from c
2026-05-08 抖音面试真题（1）T+1日留存率  
with a as(
select distinct 
        usr_id,
        date(login_time) as login_date 
    from 
        user_login_log 
    where 
        datediff(current_date, date(login_time)) <= 30
order by login_date
),
b as (
select login_date,
count(*) as people
from a 
group by login_date
),
c as (
select b1.login_date,b1.people,b2.login_date as T1date,b2.people as t1people
from b b1
join b b2
on b1.login_date = b2.login_date - 1
)
select login_date,
t1people/people *100 as T1_retention_rate
from c
2026-05-07 窗口函数（6）隔三差五去召妓，统计间隔用偏移  
with a as(
select * from cmb_usr_trx_rcd 
where usr_id = 5201314520 and mch_nm = '红玫瑰按摩保健休闲'
 ),
b as ( 
 select usr_id,trx_time,trx_amt,mch_nm,
 lag(trx_time)over(order by trx_time) as prev_trx_time
 from a )
 select 
 usr_id,trx_time,trx_amt,mch_nm,prev_trx_time ,
 datediff(trx_time,prev_trx_time) as days_since_last_fvck
 from b
2026-05-07 招商银行（二）高消费用户识别  
with a as(
select usr_id ,
sum(trx_amt) as tot
from cmb_usr_trx_rcd
group by usr_id
),
b as (
select usr_id,tot,
ntile(5)over(order by tot desc) as n
from a
)
select usr_id,tot as total_amt
from b 
where n = 1
2026-05-07 滴滴出行（三）薪资统计  
with a as(
select salary,
row_number()over(order by salary) as rnk,
count(*)over() as total
from employees),
b as (
select round(avg(salary),0) as median
from a 
where rnk in (floor(total/2),ceil(total/2))
),
c as(
SELECT salary as mode,
count(*) 
FROM employees
GROUP BY salary
ORDER BY COUNT(*) DESC
limit 1
)
SELECT b.median, c.mode
FROM b, c;
2026-05-07 滴滴出行（三）薪资统计  
with a as(
select salary,
row_number()over(order by salary) as rnk,
count(*)over() as total
from employees),
b as (
select avg(salary) as median
from a 
where rnk in (floor(total/2),ceil(total/2))
),
c as(
SELECT salary as mode,
count(*) 
FROM employees
GROUP BY salary
ORDER BY COUNT(*) DESC
limit 1
)
SELECT b.median, c.mode
FROM b, c;
2026-05-07 美团（二）月度累加销售额  
with a as(
select 
date_format(trx_dt,'%Y-%m') as month,
sum(trx_amt) as monthly_sales
from mt_trx_rcd
group by date_format(trx_dt,'%Y-%m')
order by month
),
b as (
select *,
sum(monthly_sales)over(order by month) as cumulative_sales
from a 
)
 select * from b
2026-05-07 得物面试真题（4）首单Mac二单iPhone的客户  
with a as(
select user_id,product_type,purchase_time,
row_number()over(partition by user_id order by purchase_time) as rnk
from apple_pchs_rcd
),
b as (
select user_id ,
max(case when rnk = 1 then product_type end) as buy1,
max(case when rnk = 2 then product_typeend) as buy2
from a 
group by user_id
)
select 
user_id,
case when buy1='Mac' and buy2='iPhone' then 1 else 0 end as tag
from b
2026-05-06 得物面试真题（4）首单Mac二单iPhone的客户  
with a as (
select
user_id,product_type,
row_number() over(partition by user_id order by purchase_time) as rnk
from apple_pchs_rcd
),
b as (
select 
user_id,
max(case when rnk = 1 then product_type end) as type1,
max(case when rnk = 2 then product_type end) as type2
from a 
group by user_id
)
select 
 user_id,
case when type1 = 'Mac' and type2 = 'iPhone' then 1 
 else 0 
end as tag
from b;
2026-05-06 得物面试真题（4）首单Mac二单iPhone的客户  
with a as (
select
user_id,product_type,
row_number() over(partition by user_id order by purchase_time) as rnk
from apple_pchs_rcd
),
b as (
select 
user_id,
case when rnk = 1 then product_type end as type1,
case when rnk = 2 then product_type end as type2
from a 
)
select 
user_id,
case when type1 = 'Mac' and type2 = 'iPhone' then 1 
 else 0 
end as tag
from b;
2026-05-06 得物面试真题（4）首单Mac二单iPhone的客户  
with a as (
select
user_id,product_type,
row_number()over(partition by user_id order by purchase_time) as rnk
from apple_pchs_rcd),
b as (
select user_id,
case when rnk = 1 then product_type end as 1_type,
case when rnk = 2 then product_type end as 2_type
 from a 
)
select 
user_id,
case when 1_type = 'Mac' and2_type = 'iPhone' then 1
else 0
end as tag
from b
2026-05-06 得物面试真题（4）首单Mac二单iPhone的客户  
with a as (
select
user_id,product_type,
row_number()over(partition by user_id order by purchase_time) as rnk
from apple_pchs_rcd),
b as (
select user_id,
case when rnk = 1 then product_type end as 1_type,
case when rnk = 2 then product_type end as 2_type
 from a 
)
select 
user_id,
case when 
1_type = 'Mac' and 2_type = 'iPhone' then 1 else 0 
end as tag
from b
2026-05-06 快手面试真题（3）同时在线人数峰值时点  
WITH a AS (
SELECT usr_id, live_id, enter_time AS ts, 1 AS uv
FROM ks_live_t1 
UNION ALL 
SELECT usr_id, live_id, leave_time AS ts, -1 AS uv
FROM ks_live_t1 
),
b AS (
SELECT 
live_id,
ts,
SUM(uv) OVER (PARTITION BY live_id ORDER BY ts) AS online_users
FROM a 
),
c AS (
SELECT 
live_id,
ts,
online_users,
MAX(online_users) OVER (PARTITION BY live_id) AS max_users
FROM b
)
SELECT c.live_id,live_nm,max_users as max_online_users,
min(ts) asfirst_peak_time,
max(ts) aslast_peak_time
from c
join ks_live_t2 
using(live_id)
where online_users = max_users
group by c.live_id,live_nm,max_online_users
order by max_online_users desc
2026-05-06 快手面试真题（3）同时在线人数峰值时点  
with a as (
select usr_id,live_id,enter_time as ts, 1 as uv
from ks_live_t1 
union all 
select usr_id,live_id,leave_time as ts, -1 as uv
from ks_live_t1 
),
b as (
select live_id,ts,
sum(uv)over(partition by live_id order by ts ) as users
from a 
), 
c as (
select live_id,live_nm,max(users) as max_online_users
from b join ks_live_t2 
using(live_id)
group by live_id ,live_nm
order by max_online_users desc 
)
select c.live_id,live_nm,max(max_online_users) as max_online_users ,
min(ts) as first_peak_time,max(ts) as last_peak_time
from c 
join b 
on c.max_online_users = b.users
group by c.live_id,live_nm
order by max_online_users desc
2026-05-06 快手面试真题（3）同时在线人数峰值时点  
with a as (
select usr_id,live_id,enter_time as ts, 1 as uv
from ks_live_t1 
union all 
select usr_id,live_id,leave_time as ts, -1 as uv
from ks_live_t1 
),
b as (
select live_id,ts,
sum(uv)over(partition by live_id order by ts ) as users
from a 
), 
c as (
select live_id,live_nm,max(users) as max_online_users
from b join ks_live_t2 
using(live_id)
group by live_id ,live_nm
order by max_online_users desc 
)
select c.live_id,live_nm,max_online_users,ts
from c 
join b 
on c.max_online_users = b.users
order by max_online_users desc