排名

用户解题统计

过去一年提交了

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

收藏

收藏日期 题目名称 解决状态
2025-08-23 计算每个城市的有效订单完成率  未解决
2025-08-23 从商品角度统计收藏到购买的转化率  已解决
2025-08-23 找出所有类别组合的最热门路线  已解决
2025-08-23 找出所有以酒店为起点的类别组合的最热门路线  已解决
2025-08-23 找出酒店-餐饮的最热门路线  已解决
2025-08-23 查询所有以住宅区为起点且以写字楼为终点的行程  已解决
2025-08-23 查询所有起点和终点都属于餐饮类别的行程  已解决
2025-08-23 得物面试真题（4）首单Mac二单iPhone的客户  已解决
2025-08-23 基于共同兴趣爱好的餐厅推荐（3）-好基友（1）  未解决
2025-08-23 连续登录3天及以上  已解决

评论笔记

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

提交记录

提交日期 题目名称 提交代码
2025-08-23 从商品角度统计收藏到购买的转化率  
SELECT 
f.mch_id,
COUNT(DISTINCT f.cust_uid) AS fav_user_count,
COUNT(DISTINCT CASE 
WHEN p.cust_uid IS NOT NULL AND p.pchs_tm > f.fav_tm 
THEN f.cust_uid 
ELSE NULL 
END) AS conv_user_count,
ROUND(
CASE 
WHEN COUNT(DISTINCT f.cust_uid) = 0 THEN 0
ELSE (COUNT(DISTINCT CASE 
WHEN p.cust_uid IS NOT NULL AND p.pchs_tm > f.fav_tm 
THEN f.cust_uid 
ELSE NULL 
END) / COUNT(DISTINCT f.cust_uid)) * 100 
END, 
2
) AS conversion_rate
FROM xhs_fav_rcd f
LEFT JOIN xhs_pchs_rcd p 
ON f.cust_uid = p.cust_uid
AND f.mch_id = p.mch_id 
GROUP BY f.mch_id
ORDER BY conversion_rate DESC;
2025-08-23 从商品角度统计收藏到购买的转化率  
WITH favorite_users AS (
    SELECT mch_id, cust_uid
    FROM xhs_fav_rcd
    GROUP BY mch_id, cust_uid
),
favorite_counts AS (
    SELECT mch_id, COUNT(DISTINCT cust_uid) AS fav_user_count
    FROM favorite_users
    GROUP BY mch_id
),
conversion_counts AS (
    SELECT f.mch_id, COUNT(DISTINCT f.cust_uid) AS conv_user_count
    FROM favorite_users f
    JOIN xhs_fav_rcd fv ON f.mch_id = fv.mch_id AND f.cust_uid = fv.cust_uid
    JOIN xhs_pchs_rcd p ON f.mch_id = p.mch_id AND f.cust_uid = p.cust_uid
    WHERE p.pchs_tm > fv.fav_tm
    GROUP BY f.mch_id
)
SELECT 
    fc.mch_id, 
    fc.fav_user_count, 
    COALESCE(cc.conv_user_count, 0) AS conv_user_count,
    COALESCE(ROUND((cast(cc.conv_user_count as float) / fc.fav_user_count) * 100, 2), 0) AS conversion_rate
FROM favorite_counts fc
LEFT JOIN conversion_counts cc ON fc.mch_id = cc.mch_id
ORDER BY conversion_rate DESC;
2025-08-23 找出所有类别组合的最热门路线  
WITH all_routes AS (
    SELECT r.start_loc, r.end_loc, l_start.loc_ctg AS start_ctg, l_end.loc_ctg AS end_ctg, COUNT(*) AS trip_count
    FROM didi_sht_rcd r
    JOIN loc_nm_ctg l_start ON r.start_loc = l_start.loc_nm
    JOIN loc_nm_ctg l_end ON r.end_loc = l_end.loc_nm
    GROUP BY r.start_loc, r.end_loc, l_start.loc_ctg, l_end.loc_ctg
),
ranked_routes AS (
    SELECT start_loc, end_loc, start_ctg, end_ctg, trip_count,
           dense_rank() OVER (PARTITION BY start_ctg, end_ctg ORDER BY trip_count DESC) AS route_rank
    FROM all_routes
)
SELECT start_loc, end_loc, start_ctg, end_ctg, trip_count
FROM ranked_routes
WHERE route_rank = 1
ORDER BY trip_count DESC;
2025-08-23 找出所有以酒店为起点或终点的类别组合的最热门路线  
WITH hotel_routes AS (
    SELECT r.start_loc, r.end_loc, l_start.loc_ctg AS start_ctg, l_end.loc_ctg AS end_ctg, COUNT(*) AS trip_count
    FROM didi_sht_rcd r
    JOIN loc_nm_ctg l_start ON r.start_loc = l_start.loc_nm
    JOIN loc_nm_ctg l_end ON r.end_loc = l_end.loc_nm
    WHERE l_start.loc_ctg = '酒店' OR l_end.loc_ctg = '酒店'
    GROUP BY r.start_loc, r.end_loc, l_start.loc_ctg, l_end.loc_ctg
),
ranked_routes AS (
    SELECT start_loc, end_loc, start_ctg, end_ctg, trip_count,
           ROW_NUMBER() OVER (PARTITION BY start_ctg, end_ctg ORDER BY trip_count DESC) AS route_rank
    FROM hotel_routes
)
SELECT start_loc, end_loc, start_ctg, end_ctg, trip_count
FROM ranked_routes
WHERE route_rank = 1
ORDER BY trip_count DESC;
2025-08-23 找出所有以酒店为起点的类别组合的最热门路线  
WITH hotel_routes AS (
    SELECT r.start_loc, r.end_loc, l_end.loc_ctg, COUNT(*) AS trip_count
    FROM didi_sht_rcd r
    JOIN loc_nm_ctg l_start ON r.start_loc = l_start.loc_nm
    JOIN loc_nm_ctg l_end ON r.end_loc = l_end.loc_nm
    WHERE l_start.loc_ctg = '酒店'
    GROUP BY r.start_loc, r.end_loc, l_end.loc_ctg
),
ranked_routes AS (
    SELECT start_loc, end_loc, loc_ctg, trip_count,
           ROW_NUMBER() OVER (PARTITION BY loc_ctg ORDER BY trip_count DESC) AS route_rank
    FROM hotel_routes
)
SELECT start_loc, end_loc, loc_ctg, trip_count
FROM ranked_routes
WHERE route_rank = 1
ORDER BY trip_count DESC;
2025-08-23 找出酒店-餐饮的最热门路线  
WITH hotel_to_cafe AS (
    SELECT r.start_loc, r.end_loc, COUNT(*) AS trip_count
    FROM didi_sht_rcd r
    JOIN loc_nm_ctg l_start ON r.start_loc = l_start.loc_nm
    JOIN loc_nm_ctg l_end ON r.end_loc = l_end.loc_nm
    WHERE l_start.loc_ctg = '酒店' AND l_end.loc_ctg = '餐饮'
    GROUP BY r.start_loc, r.end_loc
)
SELECT start_loc, end_loc, trip_count
FROM hotel_to_cafe
ORDER BY trip_count DESC
LIMIT 1;
2025-08-23 查询所有以住宅区为起点且以写字楼为终点的行程  
SELECT r.*
FROM didi_sht_rcd r
JOIN loc_nm_ctg l_start ON r.start_loc = l_start.loc_nm
JOIN loc_nm_ctg l_end ON r.end_loc = l_end.loc_nm
WHERE l_start.loc_ctg = '住宅'
  AND l_end.loc_ctg = '写字楼'
ORDER BY r.start_tm ASC;
2025-08-23 查询所有起点和终点都属于餐饮类别的行程  
SELECT r.*
FROM didi_sht_rcd r
JOIN loc_nm_ctg l_start ON r.start_loc = l_start.loc_nm
JOIN loc_nm_ctg l_end ON r.end_loc = l_end.loc_nm
WHERE l_start.loc_ctg = '餐饮'
  AND l_end.loc_ctg = '餐饮'
ORDER BY r.start_tm ASC;
2025-08-23 查询所有终点是餐饮类地点的行程记录  
select
cust_uid,
start_loc,
end_loc,
start_tm,
car_cls
from
didi_sht_rcd a 
join loc_nm_ctg b on a.end_loc = b.loc_nm
where
b.loc_ctg = '餐饮'
order by
start_tm asc
2025-08-23 只被收藏未被购买的商品  
SELECT 
    gd.gd_id, 
    gd.gd_nm, 
    gd.gd_typ
FROM 
    xhs_fav_rcd fav
JOIN 
    gd_inf gd ON fav.mch_id = gd.gd_id
LEFT JOIN 
    xhs_pchs_rcd pchs ON gd.gd_id = pchs.mch_id
WHERE 
    pchs.mch_id IS NULL
group by
    gd.gd_id, 
    gd.gd_nm, 
    gd.gd_typ
2025-08-23 只被收藏未被购买的商品  
SELECT 
    gd.gd_id, 
    gd.gd_nm, 
    gd.gd_typ
FROM 
    xhs_fav_rcd fav
JOIN 
    gd_inf gd ON fav.mch_id = gd.gd_id
LEFT JOIN 
    xhs_pchs_rcd pchs ON gd.gd_id = pchs.mch_id
WHERE 
    pchs.mch_id IS NULL
2025-08-23 购买人数最多的商品类目  
SELECT 
    gd.gd_typ, 
    COUNT(DISTINCT pchs.cust_uid) AS buyer_count
FROM 
    xhs_pchs_rcd pchs
JOIN 
    gd_inf gd ON pchs.mch_id = gd.gd_id
GROUP BY 
    gd.gd_typ
ORDER BY 
    buyer_count DESC
LIMIT 1;
2025-08-23 被收藏次数最多的商品  
select
	t1.gd_id,
t1.gd_nm,
count(t2.fav_trq) as fav_count
from
	gd_inf t1 
join
	xhs_fav_rcd t2 on t1.gd_id = t2.mch_id
group by
	1,2
order by
	fav_count desc
limit
	1;
2025-08-23 被收藏次数最多的商品  
SELECT 
    gd.gd_id, 
    gd.gd_nm, 
    COUNT(*) AS fav_count
FROM 
    xhs_fav_rcd fav
JOIN 
    gd_inf gd ON fav.mch_id = gd.gd_id
GROUP BY 
    gd.gd_id, gd.gd_nm
ORDER BY 
    fav_count DESC
LIMIT 1;
2025-08-23 不分类别的最火直播间  
select
a.live_id,
b.live_nm,
count(1) as enter_cnt
from ks_live_t1 a
join ks_live_t2 b using(live_id)
where date_format(a.enter_time, '%Y-%m-%d %H') = '2021-09-12 23'
group by 1,2
order by enter_cnt desc
limit 5;
2025-08-23 不分类别的最火直播间  
select
a.live_id,
b.live_nm,
count(a.enter_time) as enter_cnt
from ks_live_t1 a
join ks_live_t2 b using(live_id)
where date_format(a.enter_time, '%Y-%m-%d %H') = '2021-09-12 23'
group by 1,2
order by enter_cnt desc
limit 5;
2025-08-23 不分类别的最火直播间  
select
a.live_id,
b.live_nm,
count(a.enter_time) as enter_cnt
from ks_live_t1 a
join ks_live_t2 b using(live_id)
where date_format(a.enter_time, '%Y-%m-%d %H')
group by 1,2
order by enter_cnt desc
limit 5;
2025-08-23 按天粒度统计订单明细  
select
date(start_time) as dt,
start_loc,
end_loc,
count(start_time) as cnt,
count(distinct user_id) as usr_cnt,
round(count(start_time) / count(distinct user_id) , 4) as cnt_per_usr
from
hello_bike_riding_rcd
where
left(start_time,7) = '2024-10'
group by date(start_time), start_loc,end_loc
2025-08-23 行程细分到小时  
select
hour(start_time) as H,
start_loc,
end_loc,
count(1) as cnt
from
hello_bike_riding_rcd
where
user_id = 'u802844'
and (start_loc = '望京南' and end_loc = '方恒购物中心' or start_loc = '方恒购物中心' and  end_loc='望京南')
group by 1,2,3
order by 1,2
2025-08-23 行程细分到小时  
select
hour(start_time) as H,
start_loc,
end_loc,
count(1) as cnt
from
hello_bike_riding_rcd
where
user_id = 'u802844'
and (start_loc = '望京南' and end_loc = '方恒购物中心')
or (start_loc = '方恒购物中心' and  end_loc='望京南')
group by 1,2,3
order by 1,2