右下角图片

排名

用户解题统计

过去一年提交了

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

收藏

收藏日期 题目名称 解决状态
2025-01-03 招建银行信用卡中心客户挽留-电商平台分类  已解决
2025-01-03 深圳气温异常年份  已解决
2024-12-17 查询所有终点是餐饮类地点的行程记录  已解决
2024-11-29 条件过滤-查找2009年出生的女学生  已解决
2024-11-18 从商品角度统计收藏到购买的转化率  已解决
2024-11-18 餐厅的用户复购率  已解决
2024-11-18 餐饮类别丰富度标签  已解决

评论笔记

评论日期 题目名称 评论内容 站长评论
2024-11-27 被收藏次数最多的商品 
应该是题主搞错了吧,两张表的字段名不一样
啥也没说
2024-11-27 小宇宙电台的同期群分析 
我刚用GPT秒了30多道题。。为啥这题搞不定
同学你用GPT作弊有啥用呢。。
2024-11-18 子查询(1)玩的最嗨那天在做甚?要用Where子查询 
思路新奇,被你整笑了哥
啥也没说
2024-11-17 招建银行信用卡中心客户挽留-电商平台分类 
这题没啥技术含量,全靠百度搜索,😜
谁说没技术含量的。
1、商户那么多,难道你要枚举吗?
2、你是对全量交易描述分类还是去重后分类(影响运行时间)
2024-11-17 赌王争霸赛-盖哥要玩87o 
J4o 还是J4s?站长你没说清楚啊
J4o 你再试试 看能不能到100分 温馨提示 TT 1010

提交记录

提交日期 题目名称 提交代码
2025-02-08 表连接(4)渣男把钱花在哪儿,维表可以来帮忙 
SELECT
b.mch_typ,
COUNT(1) AS trx_cnt,
SUM(a.trx_amt) AS trx_amt
FROM
(
SELECT
mch_nm,
trx_amt
FROM
cmb_usr_trx_rcd
WHERE
usr_id = 5201314520
AND YEAR(trx_time) = 2024
) AS a
LEFT JOIN cmb_mch_typ AS b
ON a.mch_nm = b.mch_nm
GROUP BY b.mch_typ
ORDER BY trx_amt DESC
;
2025-02-08 上月活跃用户数 
SELECT COUNT(DISTINCT usr_id) AS active_users
FROM user_login_log
WHERE login_time >= DATE_FORMAT(DATE_ADD(CURRENT_DATE(), INTERVAL -1 MONTH), '%y-%m-01')
AND login_time < DATE_FORMAT(CURRENT_DATE(), '%y-%m-01')
2025-02-08 最受欢迎歌手 
SELECT sii.singer_name,
 COUNT(*) AS total_plays,
 CASE WHEN sii.singer_nameIN ('周杰伦','Michael Jackson') THEN '特别推荐'
ELSE '' END AS recommendation 
FROM singer_info AS sii
LEFT JOIN song_info AS soi
ON sii.singer_id = soi.origin_singer_id
LEFT JOIN listen_rcd AS lr
ON lr.song_id = soi.song_id
GROUP BY sii.singer_id,sii.singer_name
ORDER BY 2 DESC
2025-02-08 不分类别的最火直播间 
SELECT a.live_id, t2.live_nm ,a.enter_cnt
FROM (
SELECT live_id, COUNT(*) AS enter_cnt
FROM ks_live_t1
WHERE enter_time RLIKE '2021-09-12 23'
GROUP BY live_id
) AS a LEFT JOIN ks_live_t2 AS t2
ON a.live_id = t2.live_id
ORDER BY 3 DESC
LIMIT 5
2025-02-08 计算完播率(按次数) 
SELECT log.video_id,inf.title,
 ROUND(SUM(CASE WHEN log.end_time <= DATE_ADD(log.start_time, INTERVAL inf.duration SECOND) THEN 1 END)/COUNT(*) *100,2) AS completion_rate
FROM ks_video_wat_log AS log 
LEFT JOIN ks_video_inf AS inf
ON log.video_id = inf.video_id
WHERE start_time >= DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH)
GROUP BY log.video_id
ORDER BY 3 DESC
LIMIT 5
2025-02-08 比较每个月客户的拉新质量(1) 
WITH rfm AS (
SELECT cust_uid AS user_id,
 TIMESTAMPDIFF(DAY, MAX(trx_dt), CURRENT_DATE) AS recency,
 COUNT(DISTINCT trx_dt) AS frequency,
 AVG(trx_amt) AS monetary,
 DATE_FORMAT(MIN(trx_dt),'%Y-%m') AS first_trx_month
FROM mt_trx_rcd_f
GROUP BY user_id
)
SELECT user_id, 
 NTILE(3) OVER(ORDER BY recency DESC) AS recency_score,
 NTILE(3) OVER(ORDER BY frequency) AS frequency_score,
 NTILE(3) OVER(ORDER BY monetary) AS monetary_score,
 first_trx_month
FROM rfm
ORDER BY user_id
2025-02-08 数学成绩分段统计(1) 
SELECT CASE WHEN score >= 110 THEN '[110,120]'
WHEN score >= 90 AND score < 110 THEN '[90,110)'
WHEN score >= 60 AND score < 90 THEN '[60,90)'
WHEN score < 60 THEN '[0.60)'
END AS score_range,
 COUNT(*) AS num_students
FROM scores
WHERE exam_date = '2024-06-30'
AND subject = '数学'
GROUP BY score_range
2025-02-08 语文数学英语至少1门超过100分的同学 
SELECT *
FROM subject_score
WHERE student_id NOT IN 
(
SELECT student_id
FROM subject_score
WHERE chinese <= 100
AND math <= 100
AND english <= 100 )
AND chinese IS NOT NULL
ORDER BY chinese
2025-02-08 晚时段专车比例 
SELECT HOUR(start_tm) AS hour,
 SUM(CASE WHEN car_cls = 'A' THEN 1 ELSE 0 END) AS A_orders,
 COUNT(*) AS total_orders,
 ROUND(SUM(CASE WHEN car_cls = 'A' THEN 1 ELSE 0 END) / COUNT(*)*100,2) AS percentage_A_orders
FROM didi_sht_rcd
WHERE HOUR(start_tm) BETWEEN 18 AND 23
GROUP BY HOUR(start_tm)
ORDER BY 1
2025-02-08 歌曲流行度分析 
SELECT 
    s.song_name,
    COUNT(DISTINCT lr.user_id) AS listeners,
    CASE 
        WHEN COUNT(DISTINCT lr.user_id) > 50 THEN '热门歌曲'
        ELSE '普通歌曲'
    END AS song_popularity
FROM 
    song_info s
JOIN 
    listen_rcd lr ON s.song_id = lr.song_id
GROUP BY 
    s.song_id,s.song_name;
2025-02-08 人均消费金额定档标签 
SELECT *, CASE WHEN avg_spending >= 300 THEN '高档'
 WHEN avg_spending >= 100 AND avg_spending < 300 THEN '中档'
 ELSE '低档' END AS label
FROM(
SELECT mch_nm,SUM(trx_amt) / COUNT(DISTINCT cust_uid) AS avg_spending
FROM mt_trx_rcd1
GROUP BY mch_nm ) a
ORDER BY 2
2025-02-08 计算每天的有效订单完成率 
SELECT order_dt AS order_date,
 COUNT(*) AS total_order_count,
 COUNT(CASE WHEN status = 'completed' AND t2.banned = 0 AND t3.banned = 0 THEN 1 END) AS valid_order_count,
 CONCAT(ROUND(CAST(COUNT(CASE WHEN status = 'completed' AND t2.banned = 0 AND t3.banned = 0 THEN 1 END) / COUNT(*) *100 AS float),2),'%') AS completion_rate
FROM hll_t1 AS t1 
LEFT JOIN hll_t2 t2 ON t1.usr_id = t2.usr_id
LEFT JOIN hll_t2 t3 ON t1.driver_id = t3.usr_id
GROUP BY order_dt
ORDER BY 4
2025-02-08 计算车方和司机被禁止的比率 
WITH user_stats AS (
    SELECT 
        role,
        SUM(CASE WHEN banned = 1 THEN 1 ELSE 0 END) AS banned_count,
        COUNT(*) AS total_count
    FROM hll_t2
    GROUP BY role
)
SELECT 
    us.role,
    us.total_count,
    us.banned_count,
    CONCAT(cast(us.banned_count/us.total_count*100 as decimal(4,2)), '%') AS banned_rate
FROM user_stats us
ORDER BY us.role;
2025-02-08 绘制小时进入人数曲线 
SELECT LPAD(hour_entered,2,'0') AS hour_entered,
 enter_count
FROM (
SELECT HOUR(enter_time) AS hour_entered,
 COUNT(*) AS enter_count
FROM ks_live_t1
GROUP BY HOUR(enter_time)) AS a
ORDER BY 1
2025-02-08 年花万元逛窑子,真他妈是个败家子(2) 
SELECT DATE_FORMAT(trx_time,'%Y') AS Y,
 MONTH(trx_time) AS m, 
 SUM(trx_amt) AS sum_trx_amt
FROM cmb_usr_trx_rcd
WHERE usr_id = 5201314520
AND trx_time RLIKE '^202[2-4]'
AND mch_nm = '红玫瑰按摩保健休闲'
GROUP BY Y,m
ORDER BY Y,m
2025-02-08 NULL的知识点 
SELECT COUNT(*) AS rows_num,
 COUNT(DISTINCT student_id) AS students_num,
 COUNT(score) AS score_num,
 COUNT(CASE WHEN score = 0 THEN 1 ELSE NULL END) AS score_zero_num,
 COUNT(CASE WHEN score IS NULL THEN 1 ELSE NULL END) AS score_null_num
FROM scores
2025-02-08 分组与聚合函数(5)五花八门的项目,其实都有固定套路(2) 
SELECT trx_amt,
 COUNT(*) AS total_trx_cnt,
 COUNT(DISTINCT usr_id) AS unique_usr_cnt,
 COUNT(*) / COUNT(DISTINCT usr_id) AS avg_trx_per_user
FROM cmb_usr_trx_rcd
WHERE mch_nm = '红玫瑰按摩保健休闲'
AND LEFT(trx_time,7) BETWEEN '2023-01' AND '2024-06'
GROUP BY trx_amt
ORDER BY avg_trx_per_user DESC
LIMIT 5
2025-02-08 计算每个用户的RFM值(1) 
SELECT cust_uid AS user_id,
 TIMESTAMPDIFF(DAY, MAX(trx_dt), CURDATE()) AS recency,
 COUNT(*) AS frequency,
 SUM(trx_amt) AS trx_dt
FROM mt_trx_rcd_f
GROUP BY cust_uid
ORDER BY user_id
2025-02-08 条件过滤-找出所有教授数学且具有高级职称的教师 
SELECT name, subject, class_code, qualification
FROM teachers
WHERE subject = '数学'
AND qualification = 'Senior'
ORDER BY name
2025-02-08 各行业第一家上市公司 
SELECT ts_code, symbol, name,area, industry, list_date
FROM(
SELECT *,
 ROW_NUMBER() OVER(PARTITION BY industry
 ORDER BY list_date) AS rnk
FROM stock_info
) a
WHERE rnk = 1
ORDER BY list_date