排名

用户解题统计

过去一年提交了

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

收藏

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

评论笔记

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

提交记录

提交日期 题目名称 提交代码
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
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,
min(ts) as first_peak_time , max(ts )as last_peak_time
from b join ks_live_t2 
using(live_id)
group by live_id ,live_nm
order by max_online_users desc 
)
select * from c
2026-05-06 快手面试真题（2）同时在线人数峰值  
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,
sum(uv)over(partition by live_id order by ts) as users
from a 
)
select 
b1.live_id,live_nm,max(users) as max_online_users
from b b1
join ks_live_t2 t2
on b1.live_id = t2.live_id
group by live_id,live_nm
order by max_online_users desc
2026-05-06 中金财富（二十七）连续上涨天数  
with a as (
select ts_code,trade_date,price_change
 fromdaily_stock_prices 
where price_change > 0
),
b as (
select *,
row_number()over(partition by ts_code order by trade_date) as days
from a 
),
c as (
select *,
trade_date - interval days day as steak
from b
),
d as (
select
ts_code,steak,MIN(trade_date) as start_date,
count(*) as consecutive_days
from c 
group by ts_code,steak
)
select 
ts_code,start_date, consecutive_days
from d 
where consecutive_days >= 3
order by consecutive_days desc
2026-05-06 中金财富（二十七）连续上涨天数  
SELECT 
ts_code,
MIN(trade_date) as start_date,
COUNT(*) as consecutive_days
FROM (
SELECT 
ts_code,
trade_date,
pct_change,
DATE_SUB(trade_date, INTERVAL ROW_NUMBER() OVER(PARTITION BY ts_code ORDER BY trade_date) DAY) as group_key
FROM daily_stock_prices
WHERE pct_change > 0
) t
GROUP BY ts_code, group_key
HAVING COUNT(*) >= 3
ORDER BY consecutive_days DESC;
2026-05-06 滴滴出行（二）用户打车频次排名  
select
cust_uid,
count(*) as order_count,
rank()over(order by count(*) desc ) as rnk
from didi_order_rcd
where finish_time > '1970-01-01'
group by cust_uid
2026-05-06 滴滴出行（二）用户打车频次排名  
select
cust_uid,
count(order_id) as order_count,
row_number()over(order by count(order_id) desc ) as rnk
from didi_order_rcd
where finish_time > '1970-01-01'
group by cust_uid
2026-05-06 滴滴出行（二）用户打车频次排名  
select
cust_uid,
count(order_id) as order_count,
row_number()over(order by count(order_id) desc ) as rnk
from didi_order_rcd
where date(finish_time) > '1970-01-01'
group by cust_uid
2026-05-06 滴滴出行（一）各部门最高薪员工  
select department,name,salary
from (
select
department,name,salary,
row_number()over(partition by department order by salary desc ) as rnk
from employees) t 
where rnk = 1
2026-05-05 频道下最受欢迎的视频  
with a as (
select * from bilibili_t100
join bilibili_t20 
using(usr_id)
join bilibili_t3 
using(v_id)
),
b as (
select v_typ,v_id,v_nm,count(*) as view_count
from a 
group by v_typ,v_id,v_nm
)
select * from b
2026-05-05 频道下最受欢迎的视频  
with a as (
select * from bilibili_t100
join bilibili_t20 
using(usr_id)
join bilibili_t3 
using(v_id)
),
b as (
select v_typ,v_id,v_nm
from a 
)
select * from b
2026-05-05 连续打卡天数统计  
with a as (
select * 
from attendance a1
join employees e1 on a1.employee_id = e1.id
),
b as (
select distinct employee_id, date(punch_time) as pt1, name
from a 
),
c as (
select *,
row_number() over(partition by employee_id order by pt1) as days
from b
),
d as (
select *,
pt1 - INTERVAL days DAY as steak 
from c 
),
e as (
select employee_id, name, steak, count(*) as steaks 
from d 
group by employee_id, steak
)
select name, max(steaks) as max_consecutive
from e
group by name
order by max_consecutive desc;
2026-05-05 连续打卡天数统计  
with a as (
select * 
from attendance a1
join employees e1 on a1.employee_id = e1.id
),
b as (
select distinct employee_id, date(punch_time) as pt1, name
from a 
),
c as (
select *,
row_number() over(partition by employee_id order by pt1) as days
from b
),
d as (
select *,
DATE_SUB(pt1, INTERVAL days DAY) as steak 
from c 
),
e as (
select employee_id, name, steak, count(*) as steaks 
from d 
group by employee_id, steak
)
select name, max(steaks) as max_consecutive
from e
group by name
order by max_consecutive desc;
2026-05-05 连续打卡天数统计  
SELECT 
e.name,
MAX(consecutive_days) as max_consecutive
FROM (
SELECT 
employee_id,
grp,
COUNT(*) as consecutive_days
FROM (
SELECT 
employee_id,
date_val,
DATE_SUB(date_val, INTERVAL ROW_NUMBER() OVER(PARTITION BY employee_id ORDER BY date_val) DAY) as grp
FROM (
SELECT DISTINCT employee_id, DATE(punch_time) as date_val FROM attendance
) t1
) t2
GROUP BY employee_id, grp
) t3
JOIN employees e ON t3.employee_id = e.id
GROUP BY e.name
ORDER BY max_consecutive DESC;