排名

用户解题统计

过去一年提交了

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

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

模块 知识点 题目 你的答案 正确答案 操作
暂无错题，继续保持！

收藏

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

评论笔记

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

提交记录

提交日期 题目名称 提交代码
2026-06-22 用户7日留存率  
WITH first_ride AS (
SELECT user_id, MIN(DATE(start_time)) AS first_date 
FROM hello_bike_riding_rcd 
GROUP BY user_id
),
daily_users AS (
SELECT DISTINCT user_id, DATE(start_time) AS ride_date 
FROM hello_bike_riding_rcd
)
SELECT 
f.first_date,
COUNT(DISTINCT f.user_id) AS new_users,
COUNT(DISTINCT d.user_id) AS retained_users,
ROUND(COUNT(DISTINCT d.user_id) / COUNT(DISTINCT f.user_id) * 100, 2) AS retention_rate
FROM first_ride f
LEFT JOIN daily_users d ON f.user_id = d.user_id 
AND d.ride_date = DATE_ADD(f.first_date, INTERVAL 7 DAY)
WHERE f.first_date BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY f.first_date
ORDER BY f.first_date
LIMIT 3
2026-06-22 用户7日留存率  
WITH first_ride AS (
SELECT user_id, MIN(DATE(start_time)) AS first_date 
FROM hello_bike_riding_rcd 
GROUP BY user_id
),
daily_users AS (
SELECT DISTINCT user_id, DATE(start_time) AS ride_date 
FROM hello_bike_riding_rcd
)
SELECT 
f.first_date,
COUNT(DISTINCT f.user_id) AS new_users,
COUNT(DISTINCT d.user_id) AS retained_users,
ROUND(COUNT(DISTINCT d.user_id) / COUNT(DISTINCT f.user_id) * 100, 2) AS retention_rate
FROM first_ride f
LEFT JOIN daily_users d ON f.user_id = d.user_id 
AND d.ride_date = DATE_ADD(f.first_date, INTERVAL 7 DAY)
WHERE f.first_date BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY f.first_date
ORDER BY f.first_date
 limit 3
2026-06-22 用户7日留存率  
WITH first_ride AS (
SELECT user_id, MIN(DATE(start_time)) AS first_date 
FROM hello_bike_riding_rcd 
GROUP BY user_id
),
daily_users AS (
SELECT DISTINCT user_id, DATE(start_time) AS ride_date 
FROM hello_bike_riding_rcd
)
SELECT 
f.first_date,
COUNT(DISTINCT f.user_id) AS new_users,
COUNT(DISTINCT d.user_id) AS retained_users,
ROUND(COUNT(DISTINCT d.user_id) / COUNT(DISTINCT f.user_id) * 100, 2) AS retention_rate
FROM first_ride f
LEFT JOIN daily_users d ON f.user_id = d.user_id 
AND d.ride_date = DATE_ADD(f.first_date, INTERVAL 7 DAY)
WHERE f.first_date BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY f.first_date
ORDER BY f.first_date
2026-06-21 用户30日留存率  
WITH first_watch AS (
SELECT usr_id, MIN(DATE(v_tm)) AS first_date 
FROM bilibili_t20 
GROUP BY usr_id
),
daily_watch AS (
SELECT DISTINCT usr_id, DATE(v_tm) AS watch_date 
FROM bilibili_t20
)
SELECT 
f.first_date,
COUNT(DISTINCT f.usr_id) AS new_users,
COUNT(DISTINCT d.usr_id) AS retained_users,
ROUND(COUNT(DISTINCT d.usr_id) / COUNT(DISTINCT f.usr_id) * 100, 2) AS retention_rate
FROM first_watch f
LEFT JOIN daily_watch d ON f.usr_id = d.usr_id 
AND d.watch_date = DATE_ADD(f.first_date, INTERVAL 30 DAY)
WHERE f.first_date BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY f.first_date
ORDER BY f.first_date
LIMIT 3
2026-06-21 招建银行（十九）差旅+有车双重画像  
SELECT 
distinct
u.usr_id,
h.hotel_cnt,
c.car_cnt
FROM 
cmb_usr_trx_rcd as u
join (
SELECT usr_id, COUNT(*) as hotel_cnt 
FROM cmb_usr_trx_rcd 
WHERE mch_nm LIKE '%酒店%' 
GROUP BY usr_id
) h
on u.usr_id = h.usr_id
JOIN (
SELECT usr_id, COUNT(*) as car_cnt 
FROM cmb_usr_trx_rcd 
WHERE mch_nm LIKE '%加油%' OR mch_nm LIKE '%洗车%' 
GROUP BY usr_id
) c ON u.usr_id = c.usr_id
where h.hotel_cnt>=1 and c.car_cnt>=1
 ORDER BY h.hotel_cnt + c.car_cnt DESC;
2026-06-21 招建银行（十九）差旅+有车双重画像  
WITH tag AS (
SELECT 
*,
CASE WHEN mch_nm LIKE '%酒店%' THEN 1 ELSE 0 END AS hoteltag,
CASE WHEN mch_nm LIKE '%加油%' OR mch_nm LIKE '%洗车%' THEN 1 ELSE 0 END AS cartag
FROM cmb_usr_trx_rcd
)
SELECT 
usr_id,
SUM(hoteltag) AS hotel_cnt,
SUM(cartag) AS car_cnt
FROM tag
GROUP BY usr_id
HAVING SUM(hoteltag) >= 1 
 AND SUM(cartag) >= 1;
2026-06-21 招建银行（十九）差旅+有车双重画像  
SELECT 
h.usr_id,
h.hotel_cnt,
c.car_cnt
FROM (
SELECT usr_id, COUNT(*) as hotel_cnt 
FROM cmb_usr_trx_rcd 
WHERE mch_nm LIKE '%酒店%' 
GROUP BY usr_id
) h
JOIN (
SELECT usr_id, COUNT(*) as car_cnt 
FROM cmb_usr_trx_rcd 
WHERE mch_nm LIKE '%加油%' OR mch_nm LIKE '%洗车%' 
GROUP BY usr_id
) c ON h.usr_id = c.usr_id
ORDER BY h.hotel_cnt + c.car_cnt DESC;
2026-06-21 招建银行（十九）差旅+有车双重画像  
with tag as (select *,case when mch_nm like '%酒店%' then 1 end as hoteltag,
case when mch_nm like '加油'or mch_nm like "洗车" then 1 end as cartag
from cmb_usr_trx_rcd)
select usr_id,sum(hoteltag) as hotel_cnt,
sum(cartag) as car_cnt
from tag
group by usr_id
having sum(hoteltag)>=1 and sum(cartag)>=1
2026-06-21 招建银行（十九）差旅+有车双重画像  
with tag as (select *,case when mch_nm like '%酒店%' then 1 end as hoteltag,
case when mch_nm like '加油'or "洗车" then 1 end as cartag
from cmb_usr_trx_rcd)
select usr_id,sum(hoteltag) as hotel_cnt,
sum(cartag) as car_cnt
from tag
group by usr_id
having sum(hoteltag)>=1 and sum(cartag)>=1
2026-06-21 招建银行（十九）差旅+有车双重画像  
with tag as (select *,case when mch_nm like '%酒店%' then 1 end as hoteltag,
case when mch_nm like '加油'or "洗车" then 1 end as cartag
from cmb_usr_trx_rcd)
select usr_id,sum(hoteltag) as hotel_cnt,
sum(cartag) as car_cnt
from tag
group by usr_id
2026-06-21 滴滴出行（十二）高端车型用户画像  
select c.loc_ctg as start_type,
count(*) as trip_count,
ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM didi_sht_rcd WHERE car_cls = 'S'), 2) as ratio
from didi_sht_rcd as d 
left join loc_nm_ctg as c 
on d.start_loc = c.loc_nm
where car_cls = 'S'
group by c.loc_ctg
 order by ratio desc
2026-06-21 得物面试真题（4）首单Mac二单iPhone的客户  
with base as (
select user_id,product_type,row_number()over(partition by user_id order by purchase_time) as rnk
from apple_pchs_rcd),
next as (select 
user_id,
min(case when rnk = 1 then product_type end ) as firstone,
min(case when rnk =2 then product_type end) as secondone
from base 
group by user_id)
select user_id,if(firstone='Mac' and secondone = 'iPhone',1,0) as tag
from next
2026-06-21 至少两门科目大于等于110分的学生  
SELECT 
student_id,
chinese,
math,
english
FROM subject_score
HAVING(
IF(chinese >= 110, 1, 0) + 
IF(math >= 110, 1, 0) + 
IF(english >= 110, 1, 0)
)>= 2
ORDER BY student_id;
2026-06-21 至少两门科目大于等于110分的学生  
SELECT 
student_id,
(
IF(chinese >= 110, 1, 0) + 
IF(math >= 110, 1, 0) + 
IF(english >= 110, 1, 0)
) AS cc
FROM subject_score
HAVING cc >= 2
ORDER BY student_id;
2026-06-21 至少两门科目大于等于110分的学生  
with base as (select student_id,chinese,case when chinese>=110 then 1 else 0 end as c,
math,case when math>=110 then 1 else 0 end as m,
english,case when english >=110 then 1 else 0 end as e
from subject_score)
select student_id,(c+m+e) as cc
from base
where (c+m+e)>=2
order by student_id
2026-06-21 数学成绩分段统计（3）  
WITH score_ranges AS (
    SELECT 
        s.class_code,
        CASE 
            WHEN sc.score >= 110 THEN 'excellent'
            WHEN sc.score >= 90 THEN 'good'
            WHEN sc.score >= 60 THEN 'pass'
            ELSE 'fail'
        END AS score_range
    FROM 
        students s
    JOIN 
        scores sc ON s.student_id = sc.student_id
    WHERE 
        sc.subject = '数学' 
        AND sc.exam_date = '2024-06-30'
),
class_totals AS (
    SELECT 
        class_code,
        COUNT(*) AS total_students
    FROM 
        score_ranges
    GROUP BY 
        class_code
)
SELECT 
    sr.class_code,
    total_students,
    CONCAT(SUM(CASE WHEN sr.score_range = 'excellent' THEN 1 ELSE 0 END), ', ', 
           ROUND(100.0 * SUM(CASE WHEN sr.score_range = 'excellent' THEN 1 ELSE 0 END) / ct.total_students, 2), '%') AS excellent,
    CONCAT(SUM(CASE WHEN sr.score_range = 'good' THEN 1 ELSE 0 END), ', ', 
           ROUND(100.0 * SUM(CASE WHEN sr.score_range = 'good' THEN 1 ELSE 0 END) / ct.total_students, 2), '%') AS good,
    CONCAT(SUM(CASE WHEN sr.score_range = 'pass' THEN 1 ELSE 0 END), ', ', 
           ROUND(100.0 * SUM(CASE WHEN sr.score_range = 'pass' THEN 1 ELSE 0 END) / ct.total_students, 2), '%') AS pass,
    CONCAT(SUM(CASE WHEN sr.score_range = 'fail' THEN 1 ELSE 0 END), ', ', 
           ROUND(100.0 * SUM(CASE WHEN sr.score_range = 'fail' THEN 1 ELSE 0 END) / ct.total_students, 2), '%') AS fail
FROM 
    score_ranges sr
JOIN 
    class_totals ct ON sr.class_code = ct.class_code
GROUP BY 
    sr.class_code, ct.total_students
ORDER BY 
    sr.class_code;
2026-06-21 数学成绩分段统计（3）  
with base as (select st.class_code,st.name,score,sc.student_id
from scores as sc 
join students as st 
on sc.student_id = st.student_id
where subject = '数学' and exam_date = '2024-06-30'),
ta as (select class_code,count(student_id) as total_student
from base
group by class_code),
next as (
select 
student_id,name,score,class_code
,case when score>110 then 'excellent'
when score>90 then 'good'
when score>60 then 'pass'
else 'fail' end as standard
from base
)
select class_code,count(student_id) as total,
concat(sum(case when standard = 'excellent' then 1 end), ",",
concat
(round(sum(case when standard = 'excellent' then 1 end)*100/count(student_id),2),"%")
)as 'excellent',
sum(case when standard = 'good' then 1 end) as 'good', 
sum(case when standard = 'pass' then 1 end) as 'pass', 
sum(case when standard = 'fail' then 1 end) as 'fail'
from next
group by class_code
2026-06-21 互相发过红包的好友关系对  
select distinct t1.snd_usr_id as usera,t1.rcv_usr_id as userb
from tx_red_pkt_rcd as t1 
join tx_red_pkt_rcd as t2 
on t1.snd_usr_id=t2. rcv_usr_id and t1.rcv_usr_id = t2.snd_usr_id
 WHERE 
    DATE(t1.snd_datetime) = '2021-02-13'
    AND DATE(t2.snd_datetime) = '2021-02-13'
  AND t1.snd_usr_id < t1.rcv_usr_id
2026-06-21 互相发过红包的好友关系对  
select distinct t1.snd_usr_id as usera,t1.rcv_usr_id as userb
from tx_red_pkt_rcd as t1 
join tx_red_pkt_rcd as t2 
on t1.snd_usr_id=t2. rcv_usr_id and t1.rcv_usr_id = t2.snd_usr_id
 WHERE 
    DATE(t1.snd_datetime) = '2021-02-13'
    AND DATE(t2.snd_datetime) = '2021-02-13'
2026-06-21 互相发过红包的好友关系对  
select distinct t1.snd_usr_id as usera,t1.rcv_usr_id as userb
from tx_red_pkt_rcd as t1 
join tx_red_pkt_rcd as t2 
on t1.snd_usr_id=t2. rcv_usr_id and t1.rcv_usr_id = t2.snd_usr_id