排名

用户解题统计

过去一年提交了

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

收藏

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

评论笔记

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

提交记录

提交日期 题目名称 提交代码
2026-03-09 最受新一线城市欢迎的红包金额  
with a as(
select 
r.snd_usr_id,
 r.pkt_amt 
from 
tx_red_pkt_rcd r
JOIN 
 tx_usr_bas_info u ON r.snd_usr_id = u.usr_id
JOIN 
 tx_cty_map cm ON u.cty = cm.cty
WHERE 
 DATE(r.snd_datetime) = '2021-02-13'
AND 
cm.cty_cls = '新一线'
)
select
pkt_amt,
count(*) as send_count
from
a
group by
1
order by
2 desc
limit
1
2026-03-09 整体的点击率  
with a 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
a
2026-03-08 分关键词的搜索UV转化率  
with a as(
select 
q.usr_id,
q.key_word,
q.search_tm,
q.session_id,
c.click_page_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
key_word,
count(distinct usr_id) as total_search_users,
count(distinct case when click_page_id is not null then usr_id end) as users_reached_product_page,
round((count(distinct case when click_page_id is not null then usr_id end) / nullif(count(distinct usr_id),0))*100,2) as uv_conversion_rate
from
a 
group by
key_word
order by
4 desc 
limit 10
2026-03-08 整体搜索UV转化率  
select 
 COUNT(DISTINCT q.usr_id) AS total_search_users,
    COUNT(DISTINCT CASE WHEN click_page_id IS NOT NULL THEN q.usr_id END) AS users_reached_product_page,
    ROUND((COUNT(DISTINCT CASE WHEN click_page_id IS NOT NULL THEN q.usr_id END) / NULLIF(COUNT(DISTINCT q.usr_id), 0)) * 100, 2) AS uv_conversion_rate
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
2026-03-08 不同时段的热门搜索词  
with hourly_searches as(
select 
hour(search_tm) as hour,
key_word,
 COUNT(*) AS search_count
from 
jx_query_rcd 
where
hour(search_tm) between 12 and 23
group by
hour,
key_word
),
ranked_searches AS (
select
hour,
key_word,
search_count,
row_number()over(partition by hour order by search_count desc) as search_rank
from
hourly_searches
)
select
hour,
key_word,
search_count
from
ranked_searches 
where
search_rank <= 3
order by
hour asc,
search_count desc
2026-03-08 不同时段的热门搜索词  
with hourly_searches as(
select 
hour(search_tm) as hour,
key_word,
 COUNT(*) AS search_count
from 
jx_query_rcd 
where
hour(search_tm) between 13 and 23
group by
hour,
key_word
),
ranked_searches AS (
select
hour,
key_word,
search_count,
row_number()over(partition by hour order by search_count desc) as search_rank
from
hourly_searches
)
select
hour,
key_word,
search_count
from
ranked_searches 
where
search_rank <= 3
order by
hour asc,
search_count
2026-03-08 不同时段的热门搜索词  
with hourly_searches as(
select 
hour(search_tm) as hour,
key_word,
 COUNT(*) AS search_count
from 
jx_query_rcd 
where
hour(search_tm) between 13 and 23
group by
hour,
key_word
),
ranked_searches AS (
select
hour,
key_word,
search_count,
row_number()over(partition by hour order by search_count desc) as search_rank
from
hourly_searches
)
select
hour,
key_word,
search_count
from
ranked_searches 
where
search_rank <= 3
order by
hour asc,
search_count desc
2026-03-08 不同时段的热门搜索词  
with hourly_searches as(
select 
hour(search_tm) as hour,
key_word,
 COUNT(*) AS search_count
from 
jx_query_rcd 
where
hour(search_tm) between 13 and 23
group by
hour,
key_word
),
ranked_searches AS (
select
hour,
key_word,
search_count,
row_number()over(partition by hour order by search_count desc) as search_rank
from
hourly_searches
)
select
hour,
key_word,
search_count
from
ranked_searches 
where
search_rank <= 3
order by
hour asc
2026-02-20 各金额区间红包拒收率  
with a as(
select 
 case when pkt_amt <= 50 then '(0, 50元]'
when pkt_amt > 50 AND pkt_amt <= 200 THEN '(50元, 200元]' 
else '[200元, +)' end as amount_range, 
 count(*) as total_sent,
 sum(case when rcv_datetime ='1900-01-01 00:00:00' THEN 1 ELSE 0 END) as rejected_count
from 
tx_red_pkt_rcd 
where
DATE(snd_datetime) = '2021-02-13' 
group by
amount_range
)
select
amount_range,
total_sent,
rejected_count,
round((rejected_count / total_sent)*100,2) as rejection_rate
from
a
2026-02-20 计算红包接收率  
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) / count(*)*100,2) as receive_rate
from 
tx_red_pkt_rcd 
where 
date(snd_datetime) = '2021-02-13';
2026-02-05 红包发送量排名  
SELECT 
    u.cty,
    COUNT(r.snd_usr_id) AS send_count
FROM 
    tx_red_pkt_rcd r
JOIN 
    tx_usr_bas_info u ON r.snd_usr_id = u.usr_id
WHERE 
    date(r.snd_datetime) = '2021-02-13'
GROUP BY 
    u.cty
ORDER BY 
    send_count DESC
LIMIT 5;
2026-02-05 热门购买商品  
select 
r.good_id,
m.gd_nm,
count(distinct usr_id) as purchase_user_count
from 
jx_pchs_rcd r 
join
jx_gd_page_map m
on
r.good_id = m.gd_id
group by
r.good_id,
m.gd_nm
order by
3 desc
2026-02-05 热门搜索关键词  
select 
key_word,
count(*) as search_count
from 
jx_query_rcd
group by
key_word
order by
2 desc
limit
5
2026-02-03 每个商品的用户性别分布  
with a as(
select 
r.cust_uid,
r.prd_id,
i.gdr
from 
tb_pg_act_rcd r 
join
tb_cst_bas_inf i
on
r.cust_uid = i.cust_uid
where
r.if_buy = '1'
)
select
t.prd_id,
m.prd_nm,
sum(case when t.gdr = 'M' then 1 else 0 end) as male_count,
sum(case when t.gdr = 'F' then 1 else 0 end) as female_count,
count(t.cust_uid) as total_count
from
a t
join
tb_prd_map m
on
t.prd_id = m.prd_id
group by
t.prd_id,
m.prd_nm
2026-02-02 面包甜点店的市场份额  
with a as(
select 
mch_nm,
sum(trx_amt) as restaurant_revenue
from 
mt_trx_rcd1
where
mch_typ2 = '面包甜点'
and
date_format(trx_dt, '%Y-%m') = '2021-03'
group by
mch_nm
),
b as(
select
sum(trx_amt) as category_revenue
from
mt_trx_rcd1
where
 mch_typ2 = '面包甜点'
and
date_format(trx_dt, '%Y-%m') = '2021-03'
)
select
mch_nm,
restaurant_revenue,
category_revenue,
round((restaurant_revenue / nullif(category_revenue,0))*100,2) as market_share
from
a,b
2026-02-02 每月新增用户占比  
with a as(
select 
mch_nm,
cust_uid,
min(trx_dt) as first_trx_dt
from 
mt_trx_rcd1 
group by
mch_nm,
cust_uid 
),
b as(
select
mch_nm,
date_format(first_trx_dt,'%Y-%m') as month,
count(distinct cust_uid) as new_users
from
a 
group by
mch_nm,
month
),
c as(
select
mch_nm,
date_format(trx_dt,'%Y-%m') as month,
count(distinct cust_uid) as total_users
from
mt_trx_rcd1
group by
mch_nm,
month
)
select
b.mch_nm,
b.month,
c.total_users,
b.new_users,
round((b.new_users / c.total_users)*100,2) as new_user_ratio
from
b
join
c 
on
b.mch_nm = c.mch_nm
and
b.month = c.month
group by
b.mch_nm,
b.month,
c.total_users,
b.new_users
2026-02-02 每月新增用户占比  
with a as(
select 
mch_nm,
cust_uid,
min(trx_dt) as first_trx_dt
from 
mt_trx_rcd1 
group by
mch_nm,
cust_uid 
),
b as(
select
mch_nm,
date_format(first_trx_dt,'%Y-%m') as month,
count(distinct cust_uid) as new_users
from
a 
group by
mch_nm,
month
),
c as(
select
mch_nm,
date_format(trx_dt,'%Y-%m') as month,
count(distinct cust_uid) as total_users
from
mt_trx_rcd1
group by
mch_nm,
month
)
select
b.mch_nm,
b.month,
c.total_users,
b.new_users,
(b.new_users / c.total_users)*100 as new_user_ratio
from
b
join
c 
on
b.mch_nm = c.mch_nm
and
b.month = c.month
group by
b.mch_nm,
b.month,
c.total_users,
b.new_users
2026-02-02 每月新增用户占比  
select 
mch_nm,
cust_uid,
min(trx_dt) as first_trx_dt
from 
mt_trx_rcd1 
group by
mch_nm,
cust_uid
2026-01-29 每天新增用户  
with a as(
select 
usr_id,
min(v_date) as first_login_date
from 
bilibili_t100 
group by
usr_id
)
select
first_login_date,
count(*) as new_users
from
a 
group by
first_login_date
2026-01-28 滴滴面试真题（2）打车订单呼叫应答时间  
select 
 sum(TIMESTAMPDIFF(SECOND, call_time, grab_time))/count(1) AS avg_response_time_seconds
from 
didi_order_rcd 
where
grab_time != '1970-01-01 00:00:00'