右下角图片

排名

用户解题统计

过去一年提交了

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

收藏

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

评论笔记

评论日期 题目名称 评论内容 站长评论
2024-12-26 计算每个用户的日均观看时间 
答案的写法,算的不是每个视频的平均观看时间么? 但题目不是算日均观看时间么?
已修正。
题意:一个用户18日看了20分钟、19日30分钟。则日平均观看时间为25分钟
2024-12-26 找出最近一周内发布的竖屏视频 
输出示例和需求不符
已修正
2024-12-25 按天粒度统计订单明细 
题目说的9月份,答案却是10月份。
啥也没说
2024-12-25 按天粒度统计订单明细 
提个建议,这种设计时间的题,能否精确说下是以开始时间为基准,还是结束时间? 如果有跨天的数据,要不要处理。
啥也没说
2024-12-24 赌王争霸赛-盖哥要玩87o 
话说得分的标准是啥?
跟我的正确答案一一比对,每正确一行得分就增加
2024-12-20 播放量最高的标签 
题目是:观看过该标签下任意视频的唯一用户数。

为啥取count时,没有用count(distinct uid)?
啥也没说
2024-12-18 总播放时长最长的视频 
为啥要包一层abs,时间为负数应该是异常数据吧?
另外为啥我无法通过: end_time < start_time 或者 timestampdiff(second, start_time, end_time) < 0 来查看这些异常数据,过滤条件感觉没有效果?
已修正,你再试试
2024-12-18 近1个月最热短视频 
题目不是前10个么?为啥答案缺只是前3个
已改,感谢指出
2024-12-13 GROUP BY-各班级人数 
题目里不是说:按班级代码升序排列。吗。
啥也没说
2024-12-12 购买人数最多的商品类目 
没明白哪里不对?

SELECT
    inf.gd_typ,
    count(pch_trq) buyer_count
FROM xhs_pchs_rcd ph
         JOIN gd_inf inf ON ph.mch_id = gd_id
GROUP BY inf.gd_typ
ORDER BY buyer_count DESC
LIMIT 1;
啥叫购买人数,反正不是count(1)、也不是count(pch_trq)
2024-12-12 HAVING-每次成绩都不低于80分的学生 
输出里的样例表哥包含了求:最大值、最小值、平均值。
而实际答案只需要student_id。
已改
2024-12-12 GROUP BY-各班级人数 
哪里错了,通过不了

SELECT class_code,
       count(distinct student_id) student_count
FROM students
GROUP BY class_code
ORDER BY class_code;
看输出,不是有明显的排序么
2024-12-10 数学成绩分段统计(1) 
题目没写结果需要排序。
啥也没说
2024-12-09 S1年级物理成绩前10名(2) 
1. 这题不是说单科前10么?为啥需要去掉PARTITION BY? 

2. 题目应该是取排名小于10的吧,而不是前10行。
啥也没说
2024-12-06 大结局(😊)渣男9月爽翻天,罪证送他去西天 
另外感觉答案没有考虑一天内多次oha的情况?

感觉可以通过lag取到上一次消费值,然后做group by?

oha AS (SELECT trx_date,
                    count(1) ohya_cnt
             FROM (SELECT trx_date,
                          trx_amt                                                        current_trx,
                          lag(trx_amt, 1) OVER (PARTITION BY trx_date ORDER BY trx_time) pre_trx
                   FROM base_table) a
             WHERE current_trx = 1288
               AND pre_trx = 888
             GROUP BY trx_date)
啥也没说
2024-12-06 大结局(😊)渣男9月爽翻天,罪证送他去西天 
虽然但是,建议这题里面说明下什么算Fvck,什么算WithHand,等等。不然忘了具体的判断细节,也不好找。
啥也没说
2024-12-04 窗口函数(4)越来越喜欢召妓,窗口函数用累计(2) 
答案不能包含2024的。但上一题明显说是2023-2024,这一题又没有特别说出来。 下面这个答案去掉2024就对:

SELECT
    trx_mon,
    sum(trx_amt) OVER (ORDER BY trx_mon) trx_amt
FROM (SELECT d.date_value         trx_mon,
             coalesce(trx_amt, 0) trx_amt
      FROM (SELECT DISTINCT date_format(date_value, '%Y-%m') date_value
            FROM date_table
            WHERE year(date_value) in ('2023', '2024')) d
               LEFT JOIN (SELECT date_format(crd.trx_time, '%Y-%m') trx_mon,
                                 sum(crd.trx_amt)                   trx_amt
                          FROM cmb_usr_trx_rcd crd
                                   JOIN cmb_mch_typ cmt
                                        on crd.mch_nm = cmt.mch_nm
                          WHERE cmt.mch_typ = '休闲娱乐'
                            AND crd.usr_id = '5201314520'
                            AND year(crd.trx_time) in ('2023', '2024')
                          GROUP BY trx_mon) a
                         ON trx_mon = d.date_value) a
ORDER BY trx_mon
啥也没说
2024-12-04 窗口函数(2)所有前一和每类前一,继续搞懂排序窗口函数 
没懂错在哪??

with all_tb as (SELECT null as                                    mch_typ,
                       mch_nm,
                       count(*)                                   trx_cnt,
                       dense_rank() OVER (ORDER BY count(*) desc) rnk
                FROM cmb_usr_trx_rcd
                WHERE usr_id = '5201314520'
                GROUP BY mch_nm),
     all_top2 as (SELECT mch_typ, mch_nm, trx_cnt, rnk
                  FROM all_tb
                  WHERE rnk <= 2),
     mch as (SELECT mch_typ                                                           mch_typ,
                    a.mch_nm,
                    count(*)                                                          trx_cnt,
                    dense_rank() OVER (PARTITION BY b.mch_typ ORDER BY count(*) desc) rnk
             FROM cmb_usr_trx_rcd a
                      JOIN cmb_mch_typ b
                           ON a.mch_nm = b.mch_nm
             WHERE a.usr_id = '5201314520'
             GROUP BY b.mch_typ, a.mch_nm),
     mch_top2 as (SELECT mch_typ, mch_nm, trx_cnt, rnk
                  FROM mch a
                  WHERE rnk <= 2)
SELECT mch_typ, mch_nm, trx_cnt, rnk
FROM all_top2
UNION ALL
SELECT mch_typ, mch_nm, trx_cnt, rnk
FROM mch_top2
ORDER BY mch_typ,
         rnk;
没错。

修改题干了,可以再试试修改后的新题
2024-11-23 时间日期(6)爽完来根事后烟,不羡鸳鸯不羡仙 
渣男第一次去了红玫瑰按摩保健休闲后两小时(内),“又”去哪里潇洒了?
1. 建议加上内这个关键字。不然可以理解为两小时后。
2. 又,按我理解应该是不含`红玫瑰按摩保健休闲`。
啥也没说
2024-11-22 时间日期(5)三腿爱往会所走,全当良心喂了狗 
这句话:以及他是表白后多少天后(前也有可能)去的第一次去了红玫瑰按摩保健休闲。

歧义多:
1. 我到底是求表白后第一次去,还是求他生命周期中第一次去。 而实际没有临界值,所以无论加不加时间过滤,都能通过。
2. 去的第一次去了 -> 第一次去了。
严谨啊哥哥,已改。
2024-11-22 时间日期(4)阶段综合-按月统计日花费,一天都不要浪费 
说个问题,题目是大于288,题解中对应的含义则是大于等于。但你可能没有设置临界数据,所以无论是>,还是>=。都能通过。
啥也没说
2024-11-18 表连接(2)渣男去过我对象没去过,那就用LeftJoin 
是我没读懂题么?感觉SQL是符合题意的吧。提交也没有错误提示,不好排查。

```sql
select
  a.mch_nm asshole_tried,
  a.cnt asshole_tried_cnt,
  b.mch_nm darling_tried
from
  (
    select 
      mch_nm, 
      count(1) cnt
    from cmb_usr_trx_rcd
    where usr_id = '5201314520'
      and year(trx_time) in (2023,2024)
    group by mch_nm
    having cnt >= 20
  ) a
left join (
    select
      distinct mch_nm
    from cmb_usr_trx_rcd
    where usr_id = '5211314521'
      and year(trx_time) in (2023,2024)
  ) b
on a.mch_nm = b.mch_nm
order by a.cnt desc
```
符合题意,字段名有点小毛病,已修复
2024-11-17 表连接(1)你们难道都去过?那就试试用InnerJoin 
1. 题目里没写男朋友的usr_id,难道要我先看答案再去做么。而且输入里的usr_id是5211414521(压根没数据),答案里是5211314521。
2. 你答案里条件过滤包含了时间,但题目里并没写,结合第一点,我甚至不知道这是你挖的坑,还是题目忘写了。建议仍然给一个折叠的标准答案(或者正确答案输出的前几列是啥(有些题目,正确的答案并对不上你题目的输出)),不然比如这道题,我会很懵。
已改,感谢抓虫!
2024-11-16 小结(2)越花越多是死罪,按月统计Substr 
消费时间为23:00至03:00
感觉描述为[23:00, 03:00)好些吧。

不然其实应该是类似这样:
hour(trx_time) between 0 and 2 or hour(trx_time) = 23 or date_format(trx_time, '%T') = '03:00:00'
已采纳,严谨如斯!
2024-11-14 条件过滤(1)异地男友有异常,数分闺蜜来帮忙 
看半天才发现输出这里还有一行小字:

输出所有字段,按照trx_time升序排列。

感觉这行字同时也写在任务里会好些?
有道理 已采纳建议

提交记录

提交日期 题目名称 提交代码
2024-12-26 计算每个用户的日均观看时间 
SELECT 
    uid,
    round(AVG(watch_duration),0) AS daily_avg_watch_time
FROM (
    SELECT 
        uid,
        DATE(start_time) AS watch_date,
        TIMESTAMPDIFF(SECOND, start_time, end_time) AS watch_duration
    FROM ks_video_wat_log
) AS daily_watch_times
GROUP BY uid
ORDER BY daily_avg_watch_time DESC limit 5
2024-12-26 统计每个作者发布视频的平均互动指数 
SELECT author_id,
 round((sum(if_like) + sum(if_retweet) + sum(if_fav) + count(comment_id)) /
 count(DISTINCT inf.video_id), 2) avg_interaction_index
FROM ks_video_wat_log log
 JOIN ks_video_inf inf ON log.video_id = inf.video_id
GROUP BY inf.author_id
ORDER BY avg_interaction_index DESC;
2024-12-26 计算视频的平均观看完成率 
SELECT inf.video_id,
 inf.title,
 avg(watch_time / duration) avg_completion_rate
FROM (SELECT video_id, timestampdiff(second, start_time, end_time) watch_time
FROM ks_video_wat_log) log
 JOIN ks_video_inf inf ON log.video_id = inf.video_id
GROUP BY inf.video_id, title
ORDER BY avg_completion_rate DESC;
2024-12-26 计算视频的平均观看完成率 
SELECT inf.video_id,
 inf.title,
 sum(watch_time) / sum(duration) avg_completion_rate
FROM (SELECT video_id, timestampdiff(second, start_time, end_time) watch_time
FROM ks_video_wat_log) log
 JOIN ks_video_inf inf ON log.video_id = inf.video_id
GROUP BY inf.video_id, title
ORDER BY avg_completion_rate DESC;
2024-12-26 找出最近一周内发布的竖屏视频 
SELECT video_id, author_id, title
FROM ks_video_inf
WHERE screen_type = 'p'
AND datediff(current_date, release_time) <= 7
ORDER BY release_time DESC
2024-12-26 统计每个视频的点赞数 
SELECT inf.video_id,
 inf.title,
 sum(if_like) like_count
FROM ks_video_wat_log log
 JOIN ks_video_inf inf ON log.video_id = inf.video_id
GROUP BY inf.video_id, inf.title
ORDER BY like_count DESC;
2024-12-25 计算用户观看视频的平均时长 
SELECT uid, 
 avg(timestampdiff(second, start_time, end_time)) avg_watch_duration
FROM ks_video_wat_log
GROUP BY uid
ORDER BY avg_watch_duration DESC
2024-12-25 统计每个作者发布的AI配音视频数量 
SELECT author_id, count(1) ai_video_count
FROM ks_video_inf
WHERE if_AI_talking = 1
GROUP BY author_id
2024-12-25 按天粒度统计订单明细 
SELECT date(start_time) dt,
 start_loc,
 end_loc,
 count(1) cnt,
 count(distinct user_id)user_cnt,
 count(1) / count(distinct user_id) cnt_per_usr
FROM hello_bike_riding_rcd
WHERE year(start_time) = 2024 AND month(start_time) = 10
GROUP BY date(start_time), start_loc, end_loc
ORDER BY dt, start_loc, end_loc
2024-12-25 按天粒度统计订单明细 
SELECT date(start_time) dt,
 start_loc,
 end_loc,
 count(1) cnt,
 count(distinct user_id)user_cnt,
 count(distinct user_id) / count(1) cnt_per_usr
FROM hello_bike_riding_rcd
WHERE year(start_time) = 2024 AND month(start_time) = 10
GROUP BY date(start_time), start_loc, end_loc
ORDER BY dt, start_loc, end_loc
2024-12-25 按天粒度统计订单明细 
SELECT date(start_time) dt,
 start_loc,
 end_loc,
 count(1) cnt,
 count(distinct user_id)user_cnt,
 count(distinct user_id) / count(1) cnt_per_usr
FROM hello_bike_riding_rcd
WHERE year(start_time) = 2024 AND month(start_time) = 9
GROUP BY date(start_time), start_loc, end_loc
ORDER BY dt, start_loc, end_loc
2024-12-25 行程细分到小时 
SELECT hour(start_time) H,
 start_loc,
 end_loc,
 count(*) cnt
FROM hello_bike_riding_rcd
WHERE user_id = 'u802844'
AND start_loc in ('望京南', '方恒购物中心')
AND end_loc in ('望京南', '方恒购物中心')
GROUP BY hour(start_time),
 start_loc,
 end_loc
ORDER BY H, start_loc
2024-12-24 赌王争霸赛-盖哥要玩87o 
WITH substr AS (SELECT concat(card1, card2) AS card12,
 concat(substring(card1, 1, 1),
substring(card2, 1, 1),
CASE
WHEN substring(card1, 1, 1) = substring(card2, 1, 1) THEN ''
WHEN substring(card1, 2, 2) = substring(card2, 2, 2) THEN 's'
ELSE 'o' END
 )AS hand
from hand_permutations)
SELECT ss.card12,
 IF(pro.hand IS NULL, 0, 1) AS if_garrett
FROM substr ss
 LEFT JOIN (SELECT hand
FROM hand_probabilities
WHERE ranking > (SELECT ranking FROM hand_probabilities WHERE hand = '87o')) pro
 ON ss.hand = pro.hand;
2024-12-24 赌王争霸赛-盖哥要玩87o 
WITH substr AS (SELECT concat(card1, card2) AS card12,
 concat(substring(card1, 1, 1),
substring(card2, 1, 1),
CASE
WHEN substring(card1, 1, 1) = substring(card2, 1, 1) THEN ''
WHEN substring(card1, 2, 2) = substring(card2, 2, 2) THEN 's'
ELSE 'o' END
 )AS hand
from hand_permutations)
SELECT card12,
 IF(pro.hand IS NULL, 0, 1) AS if_garrett
FROM substr
 LEFT JOIN (SELECT hand
FROM hand_probabilities
WHERE ranking > (SELECT ranking FROM hand_probabilities WHERE hand = '87o')) pro
 ON substr.hand = pro.hand;
2024-12-24 赌王争霸赛-盖哥要玩87o 
WITH substr AS (
SELECT 
CONCAT(card1, card2) AS card12,
SUBSTRING(card1, 1, 1) AS card1,
SUBSTRING(card2, 1, 1) AS card2,
SUBSTRING(card1, 2, 2) AS color1,
SUBSTRING(card2, 2, 2) AS color2
FROM hand_permutations
),
concat_str AS (
SELECT 
card12,
CONCAT(
card1, card2,
CASE
WHEN card1 = card2 THEN ''
WHEN color1 = color2 THEN 's'
ELSE 'o' 
END
) AS hand
FROM substr
),
rank_threshold AS (
SELECT ranking
FROM hand_probabilities
WHERE hand = '87o'
)
SELECT 
cs.card12,
IFNULL(CASE WHEN hp.hand IS NOT NULL THEN 1 ELSE 0 END, 0) AS if_garrett
FROM concat_str cs
LEFT JOIN hand_probabilities hp 
ON cs.hand = hp.hand
AND hp.ranking > (SELECT ranking FROM rank_threshold)
2024-12-24 赌王争霸赛-盖哥要玩87o 
WITH substr AS (SELECT concat(card1, card2) AS card12,
 substring(card1, 1, 1) AS card1,
 substring(card2, 1, 1) AS card2,
 substring(card1, 2, 2) AS color1,
 substring(card2, 2, 2) AS color2
from hand_permutations),
 concat_str AS (SELECT card12,
 concat(card1, card2,
CASE
WHEN card1 = card2 THEN ''
WHEN color1 = color2 THEN 's'
ELSE 'o' END
 ) hand
FROM substr)
SELECT card12,
 IF(pro.hand IS NULL, 0, 1) AS if_garrett
FROM concat_str
 LEFT JOIN (SELECT hand
FROM hand_probabilities
WHERE ranking > (SELECT ranking FROM hand_probabilities WHERE hand = '87o')) pro
 ON concat_str.hand = pro.hand;
2024-12-24 赌王争霸赛-盖哥要玩87o 
WITH substr AS (SELECT concat(card1, card2) AS card12,
substring(card1, 1, 1) AS card1,
substring(card2, 1, 1) AS card2,
substring(card1, 2, 2) AS color1,
substring(card2, 2, 2) AS color2
 from hand_permutations),
 concat_str AS (SELECT card12,
 concat(card1, card2,
CASE
WHEN card1 = card2 THEN ''
WHEN color1 = color2 THEN 's'
ELSE 'o' END
 ) hand
FROM substr)
SELECT card12,
 CASE WHEN pro.hand IS NULL THEN 0 ELSE 1 END AS if_garrett
FROM concat_str
 LEFT JOIN (SELECT hand
FROM hand_probabilities
WHERE ranking > (SELECT ranking FROM hand_probabilities WHERE hand = '87o')) pro
 ON concat_str.hand = pro.hand;
2024-12-24 赌王争霸赛-盖哥要玩87o 
WITH permu AS (SELECT concat(card1, card2)card12,
substring(card1, 1, 1) AS card1,
substring(card2, 1, 1) AS card2,
CASE
WHEN substring(card1, 1, 1) = substring(card2, 1, 1) THEN ''
WHEN substring(card1, 2, 2) = substring(card2, 2, 2) THEN 's'
ELSE 'o' END AS ec
 from hand_permutations),
 permu2 AS (SELECT card12, concat(card1, card2, ec) hand FROM permu)
SELECT card12,
 CASE WHEN pro.hand IS NULL THEN 0 ELSE 1 END AS if_garrett
FROM permu2
 LEFT JOIN (SELECT hand
FROM hand_probabilities
WHERE ranking > (SELECT ranking FROM hand_probabilities WHERE hand = '87o')) pro
 ON permu2.hand = pro.hand;
2024-12-23 按终点统计行程次数 
SELECT end_loc,
 count(1) cnt
FROM hello_bike_riding_rcd
GROUP BY end_loc
ORDER BY cnt DESC;
2024-12-23 找出所有以酒店为起点或终点的类别组合的最热门路线 
SELECT start_loc,
 end_loc,
 start_ctg,
 end_ctg,
 trip_count
FROM (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) rn
FROM (SELECT start_loc,
 end_loc,
 loc.loc_ctgstart_ctg,
 loc2.loc_ctg end_ctg,
 count(1) trip_count
FROM didi_sht_rcd rcd
 JOIN loc_nm_ctg loc
ON rcd.start_loc = loc.loc_nm
 JOIN loc_nm_ctg loc2
ON rcd.end_loc = loc2.loc_nm
WHERE loc.loc_ctg = '酒店'
 OR loc2.loc_ctg = '酒店'
GROUP BY start_loc,
 end_loc,
 loc.loc_ctg,
 loc2.loc_ctg
ORDER BY trip_count DESC) t) t2
WHERE t2.rn = 1
ORDER BY trip_count DESC