这样直接一个查询的话，最后结果小数部分和参考答案跑出来的有些差异，我前几题都这么写，前一题试了下小数保留4位，两种写法结果是一样的
SELECT 
       CAST(SUM(CASE WHEN pay_amount = 144 THEN pay_amount / (DATEDIFF(end_date,begin_date)+1) ELSE NULL END) AS DECIMAL(10,2)) AS yearly,
       CAST(SUM(CASE WHEN pay_amount = 90 THEN pay_amount / (DATEDIFF(end_date,begin_date)+1) ELSE NULL END) AS DECIMAL(10,2)) AS half_yearly,
       CAST(SUM(CASE WHEN pay_amount = 51 THEN pay_amount / (DATEDIFF(end_date,begin_date)+1) ELSE NULL END) AS DECIMAL(10,2)) AS quarterly,
       CAST(SUM(CASE WHEN pay_amount = 19 THEN pay_amount / (DATEDIFF(end_date,begin_date)+1) ELSE NULL END) AS DECIMAL(10,2)) AS monthly,
       CAST(SUM(pay_amount / (DATEDIFF(end_date,begin_date)+1)) AS DECIMAL(10,2)) AS total_income
FROM bilibili_m2 AS m2
LEFT JOIN bilibili_m1 AS m1
ON m2.m_date BETWEEN m1.begin_date AND m1.end_date
WHERE m2.m_date RLIKE '^2021' 

啥也没说

这个餐厅推荐系列的后续题呢

啥也没说

这里也存在着步数表有而好友表(user1)无的人，

既然是问步数排名，那应该把步数表里的人都算进去，

我本来先把好友表里每个user1 自增一行，这样就丢了步数表里有而user1里无的人

啥也没说

二次函数必须开口向下，同时要考虑对称轴的位置

啥也没说

tb_pg_act_rcd页面埋点记录表里存在着一些客户记录表里没有的客户

我现在有点弄不清什么时候该用LEFT JOIN、什么时候该用INNER JOIN 。。。

啥也没说

OK，懂了。那电商或者外卖平台的“再次购买”，也有可能出现这种order_id相同的情况？

啥也没说

CROSS JOIN 获得从1到100的数字序列，参考答案的逗号连接写法只是未显式使用CROSS JOIN 关键字，本质相同。

啥也没说

上一题既然加了HAVING COUNT(uid) > 1 过滤掉只有发布者看完的视频，那这题是不是也要加类似语句

啥也没说

先算秒为单位的总时长，再/3600换算成小时

啥也没说

重新呼叫就是一个新订单了，不应该重新弄一个order_id吗？

还是打车领域有独特的义务逻辑吗

打开高德地图，打一次车，然后快速取消，然后回到【我的】、【我的订单】，查看订单列表，每一个订单（不管是已取消还是已完成的），都会有一个【再来一单】按钮，请问，这个按钮靠什么来判断【再】

WITH usr_act AS (
SELECT usr_id,live_id,enter_time AS event_time ,1 AS act
FROM ks_live_t1
UNION ALL
SELECT usr_id,live_id,leave_time AS event_time, -1 AS act
FROM ks_live_t1
   /* FROM 子句不要忘写 */
),

live_online_users AS (
SELECT live_id,event_time,
       SUM(act) OVER(PARTITION BY live_id
                     ORDER BY event_time) AS online_users
FROM usr_act
),

live_online_users_rnk AS ( /* 也可以利用窗口函数 MAX() OVER() */
SELECT live_id,event_time,online_users,
       RANK() OVER(PARTITION BY live_id
                   ORDER BY online_users DESC) AS rnk
FROM live_online_users
),

live_online_user_peak AS (
SELECT live_id, event_time,online_users
FROM live_online_users_rnk
WHERE rnk = 1
  ),

/* 本来想用FIRST_VALUE() OVER() 和 LAST_VALUE() OVER()的，不过分区内多行的话，好像不能像GROUP BY 那样轻易变成1行 */
live_online_user_peak_time AS (  
SELECT live_id,online_users AS max_online_users,
       MIN(event_time) AS first_peak_time,
       MAX(event_time) AS last_peak_time
FROM live_online_user_peak
GROUP BY live_id,online_users

)

SELECT t1.live_id,t2.live_nm,t1.max_online_users,
       t1.first_peak_time,
       t1.last_peak_time
FROM live_online_user_peak_time AS t1
LEFT JOIN ks_live_t2 AS t2
ON t1.live_id = t2.live_id
ORDER BY max_online_users DESC

啥也没说

需要画辅助线，难以理解的话，先具体找某一直播间试一试参考答案的代码

啥也没说

应该有205条在23:00:00前进入直播间，23:00:00后离开直播间的记录，同样地应该也会有03:59:59前进入，03:59:59后离开的记录（不过这题数据集里似乎没有）

如果这些记录在23点-4点间的时间要算在线时长的话，可以先筛选出enter_time或者leave_time落在所要求时间段内（23点到4点）的记录，这样肯定有在线时长。

然后CASE WHEN 判断enter_time、leave_time在不在23点-4点之间，不在的话，分别转成2021-09-12 23:00:00或者2021-09-13 03:59:59

啥也没说

t1这个数据集比较特殊，enter_time >= 2021-09-13 00:00:00 的记录只有两条，还都等于2021-09-13 00:00:00

啥也没说

题干要求输出4个字段，但题干下的示例和参考答案都多出一个wnd

啥也没说

现在的正确答案和上一题一样，在求每个城市的有效订单完成率

啥也没说

ROUND(CAST(AS float))实现整数依然整数，小数则保留若干位

啥也没说

参考答案里提取小时弄错了，SUBSTR(start_tm, 4, 2)切出来是'aa:bb:cc'里的分钟

啥也没说

order_id = 13 竟然有两条记录，呼叫时间分别在05-03、05-04，不是很理解这种情况，order_id 作为订单标识符不应该是主键，有唯一性吗？


参考答案里NextDayCalls这个CTE，两表联结是条件之一是order_id相等，也很奇怪，
虽说题目主语是未完成“订单”，但隐性主语应该是用户，需要两表cust_uid相等，以及时间相差一天。当然COUNT()计数还是按订单来算。

像order_id IN (12,13,9) 三个未完成订单，对应用户第二天继续呼叫，应该都是符合题意的。

不知道我有没有理解错。

主键不是订单id，一个订单被取消有可能被重新呼叫呀

格林尼治时间是0点时，东八区的北京时间是8点，UTC要加8，那西三区巴西时间要减3

啥也没说

存在着两种被取消的订单，1.呼叫然后取消的订单，2.呼叫有应答再取消的订单

题干说“每一单有应答没被取消的订单，都有应答时间”，可能会有点歧义，即不取那些应答后再取消的订单，直接用“每一单有应答的订单，都有应答时间”即可

啥也没说

上一题的结果
hour	rate
18	16.43
19	26.71
20	38.1
21	36.79
22	37.39
23	37.74
大致能看出是正相关的，R应该是正的吧

啥也没说

这里B25这位比较特殊，01-04成为会员，01-05又变成非会员

本来我是GROUP BY usr_id 求MAX(m_flg)获得最新的会员状态，但有B25这种情况在，这样就行不通了。

啥也没说

参考答案是否有问题？

比如B456这位，2020-01-09才成为会员。现在的写法会将他21年2月的每一条观看记录都和20-01-08、20-01-07两天的登录记录联结，这样非会员似乎会凭空多出两条观看记录，导致最后日均观看量虚高。

不知道我有没有理解错。

好问题，那你觉得问题出在哪里，如果会员记录表足够长（用户注册后每一天是否会员状态都有被记录的话），应该怎样写链接条件

求一段时间内某行为的日均值，先求出所有人每一天的值，再求平均

之前想得太复杂，反而想不明白了

啥也没说

WITH usr_typ AS (
SELECT t20.usr_id,t20.v_id,t20.v_tm,t3.v_typ
FROM bilibili_t20 AS t20
LEFT JOIN bilibili_t3 AS t3
ON t20.v_id = t3.v_id
WHERE DATE(t20.v_tm) >= '2021-02-05' /* 或者用 t20.v_tm RLIKE '^2021-02-0[5-8]' */
)

SELECT ta.v_typ,
       COUNT(DISTINCT ta.usr_id) AS total_views,
       COUNT(DISTINCT tb.usr_id) AS retained_users,
       CAST(COUNT(DISTINCT tb.usr_id) / COUNT(DISTINCT ta.usr_id)*100 AS decimal(5,2)) AS retention_rate
FROM usr_typ AS ta
LEFT JOIN usr_typ AS tb
ON ta.usr_id = tb.usr_id  AND DATEDIFF(tb.v_tm,ta.v_tm)  BETWEEN 1 AND 3
WHERE ta.v_tm RLIKE '^2021-02-05'
GROUP BY ta.v_typ
ORDER BY retention_rate DESC

啥也没说

这种留存率算法是不是宽泛的，更严格的留存率是再看同类型的任意视频？

留存率有很多种，分不同的场景。有些强调新用户的留存、有些则是周期不同，短剧这种上瘾的，可能看天；电商频率低一些，可能看周；家具可能就不看了，直接看1年内复购。

WITH new_user AS (
SELECT usr_id,MIN(v_date) AS first_login
FROM bilibili_t100
GROUP BY usr_id
)

SELECT nu.first_login AS login_date,
       COUNT(DISTINCT nu.usr_id) AS new_users,
       COUNT(DISTINCT CASE WHEN t.m_flg = 1 THEN nu.usr_id ELSE NULL END) AS new_members,
       CAST(COUNT(DISTINCT CASE WHEN t.m_flg = 1 THEN nu.usr_id ELSE NULL END) / COUNT(DISTINCT nu.usr_id)*100 AS decimal(5,2)) AS conversion_rate
FROM new_user AS nu
LEFT JOIN bilibili_t100 AS t
ON nu.usr_id = t.usr_id AND nu.first_login = t.v_date
GROUP BY login_date
ORDER BY login_date

啥也没说

WITH new_user AS (
SELECT usr_id,MIN(v_date) AS first_login
FROM bilibili_t100
GROUP BY usr_id
)

SELECT nu.first_login AS login_date,
       COUNT(DISTINCT nu.usr_id) AS new_users,
       COUNT(DISTINCT CASE WHEN t.m_flg = 1 THEN nu.usr_id ELSE NULL END) AS new_members,
       CAST(COUNT(DISTINCT CASE WHEN t.m_flg = 1 THEN nu.usr_id ELSE NULL END) / COUNT(DISTINCT nu.usr_id)*100 AS decimal(5,2)) AS conversion_rate
FROM new_user AS nu
LEFT JOIN bilibili_t100 AS t
ON nu.usr_id = t.usr_id AND nu.first_login = t.v_date
GROUP BY login_date
ORDER BY login_date

啥也没说

题干里的任务还有输出示例都是在求MCVI值

啥也没说

参考答案没有保留两位小数

已修正。

SELECT cust_uid
FROM mt_trx_rcd1
GROUP BY cust_uid
HAVING GROUP_CONCAT(DISTINCT mch_nm) RLIKE '(庄家界\(千灯店\).+?黄记烘培宫廷桃酥王)'

可以先观察下GROUP_CONCAT()的结果里两家店的前后顺序，再决定正则的写法

啥也没说

SELECT cust_uid
FROM mt_trx_rcd1
GROUP BY cust_uid
HAVING COUNT(DISTINCT CASE WHEN mch_nm IN ('庄家界(千灯店)','黄记烘培宫廷桃酥王') THEN mch_nm ELSE NULL END) = 2

啥也没说

只用第一个表会有什么疏漏吗

啥也没说

好家伙，这回再写，倒是忘记去重了

问人数，一般都要去重吧

啥也没说

ORDER BY score_range DESC

啥也没说

唔，这题参考答案倒是和前一题反过来，没加PARTITION BY


题干要求取前10行，和标题还有参考答案不一致

啥也没说

忘了第一次的时候是怎么写的。。。反正现在跑出来的和参考答案不一样
SELECT st.student_id, st.name, sc.score,
       ROW_NUMBER() OVER(ORDER BY sc.score DESC) AS rnk
FROM students AS st
LEFT JOIN scores AS sc
ON st.student_id = sc.student_id
WHERE subject = '物理' AND grade_code = 'S1'
LIMIT 10


发现ROW_NUMBER() OVER() 里PARTITION BY grade_code 的有无，好像会影响成绩相同时的排序

研究的很细了！评论区里，好几位同学排序找出来的前10名，都是不一样的。

是不是用LEFT JOIN比较好，把没有新客的月份也呈现出来

0/0怎么算呢

NULLIF(,)比较两个值是否相等，相等返回NULL,不等返回第一个值

IFNULL(,)判断第一个值是否为NULL，否则输出第二个，似乎只接受两个参数，用COALESCE更好

啥也没说

这题和前面只买iPhone那题是一样的

啥也没说

题目题干都是21-24年，但答案是包括2020年的

啥也没说

参考答案也应该给出至少三种解法的

啥也没说

有点不能理解时间已经在start_time和end_time区间里，但is_active=0的情况。

啥也没说

答案有误，必须c<>0

啥也没说

2024年10月1日（含）前活跃过,那应该是MAX(DATE(login_time)) <= '2024-10-01'或者MAX(login_time) < '2024-10-02'

不然像2024-10-01 12:23:24 这种符合要求的登录记录就取不到了

已修正

题干描述成2024年7月前（含）活跃过，更精确一些

啥也没说

老哥这一手COUNT( OR NULL) 用得炉火纯青

啥也没说

参考答案取新用户的方法是否有些问题

24年1月份的新用户是MIN(login_time) RLIKE '^2024-01'

参考答案里第一步monthly_unique_logins这个CTE，先在WHERE子句里筛选出2024年的登录记录，那24年之前的老用户只要在24年登录过，也算在了新用户里了

啥也没说

应该是这题在24年11月发布的关系，现在应该更新下

啥也没说

既然是求2024年每月的月留存率，那答案里应该要包括12月的吧

啥也没说

表结构里第一个字段名应该是usr_id

啥也没说

本来第一遍写的跑出来显示正确，但多想了下发现好像有点问题，

单就T+3而言

我跑出来显示正确的写法，两表自联结条件之一是表b登录日期与表a登录日期之差DATEDIFF(tb.login_date, ta.login_date) BETWEEN 2 AND 3
参考答案写法是DISTINCT CASE WHEN days_diff BETWEEN 2 AND 3 THEN usr_id END，

但细想了下，觉得自联结条件应该是DATEDIFF(tb.login_date, ta.login_date) BETWEEN 1 AND 3
相应地，参考答案写法似乎应该是DISTINCT CASE WHEN days_diff BETWEEN 1 AND 3 THEN usr_id END

我也忘了最开始为什么会写BETWEEN 2 AND 3…………


WITH user_login_date AS (
SELECT DISTINCT usr_id, DATE(login_time) AS login_date
FROM user_login_log
WHERE DATEDIFF(CURDATE(), DATE(login_time)) <= 90
)

SELECT ta.login_date,
 CAST(COUNT(DISTINCT tb.usr_id) / COUNT(DISTINCT ta.usr_id)*100 AS decimal(10,2)) AS t_plus_3_retention_rate,
 CAST(COUNT(DISTINCT tc.usr_id) / COUNT(DISTINCT ta.usr_id)*100 AS decimal(10,2)) AS t_plus_7_retention_rate,
 CAST(COUNT(DISTINCT td.usr_id) / COUNT(DISTINCT ta.usr_id)*100 AS decimal(10,2)) AS t_plus_14_retention_rate
FROM user_login_date AS ta
LEFT JOIN user_login_date AS tb
ON ta.usr_id = tb.usr_id AND DATEDIFF(tb.login_date,ta.login_date) BETWEEN 2 AND 3
LEFT JOIN user_login_date AS tc
ON ta.usr_id = tc.usr_id AND DATEDIFF(tc.login_date,ta.login_date) BETWEEN 2 AND 7
LEFT JOIN user_login_date AS td
ON ta.usr_id = td.usr_id AND DATEDIFF(td.login_date,ta.login_date) BETWEEN 2 AND 14
GROUP BY ta.login_date
ORDER BY ta.login_date DESC

啥也没说

WITH user_login_date AS (
SELECT DISTINCT usr_id, DATE(login_time) AS login_date
FROM user_login_log
WHERE /* DATE(login_time) >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) */
       DATEDIFF(CURDATE(), DATE(login_time)) <= 30 
      /* DATEDIFF() 能比 DATE_SUB()、DATE_ADD() 少写几个字符…… */
)

SELECT 
       ta.login_date AS first_login_date,
       CAST(COUNT(tb.usr_id) / COUNT(*)*100 AS decimal(10,2)) AS t_plus_1_retention_rate,
       CAST(COUNT(tc.usr_id) / COUNT(*)*100 AS decimal(10,2)) AS t_plus_3_retention_rate,
       CAST(COUNT(td.usr_id) / COUNT(*)*100 AS decimal(10,2)) AS t_plus_7_retention_rate,
       CAST(COUNT(te.usr_id) / COUNT(*)*100 AS decimal(10,2)) AS t_plus_14_retention_rate
FROM user_login_date AS ta
LEFT JOIN user_login_date AS tb
ON ta.usr_id = tb.usr_id AND DATEDIFF(tb.login_date, ta.login_date) = 1
LEFT JOIN user_login_date AS tc
ON ta.usr_id = tc.usr_id AND DATEDIFF(tc.login_date, ta.login_date) = 3
LEFT JOIN user_login_date AS td
ON ta.usr_id = td.usr_id AND DATEDIFF(td.login_date, ta.login_date) = 7
LEFT JOIN user_login_date AS te
ON ta.usr_id = te.usr_id AND DATEDIFF(te.login_date, ta.login_date) = 14
GROUP BY ta.login_date
ORDER BY ta.login_date

啥也没说

答案应该有误，CASE WHEN 里低价值用户写成 <5 了，高价值是用ELSE得到的，于是高价值包括了5和9分

啥也没说

用ROW_NUMBER() OVER(ORDER BY trx_dt DESC)或者MAX(trx_dt)找去的最后一家餐厅

ROW_NUMBER()好像可以少写一点，除非MAX(trx_dt) 用三表联结

啥也没说

9分这位并列第二的店有三家，也就是说前三要在这三家里取二，

我这里跑出来是取了秀水餐厅、黄记烘培宫廷桃酥王，和参考答案出来的不一样，只有一个排序条件下相同值究竟怎么排的。。。

WITH rfm_inf AS (
SELECT cust_uid AS user_id,
       DATEDIFF(CURDATE(), MAX(trx_dt)) AS re,
       COUNT(DISTINCT trx_dt) AS fre,
       AVG(trx_amt) AS mo
FROM mt_trx_rcd_f
GROUP BY user_id
ORDER BY user_id
),

rfm_score AS (
SELECT user_id,
       NTILE(3) OVER(ORDER BY re DESC) AS recency_score,
       NTILE(3) OVER(ORDER BY fre) AS frequency_score,
       NTILE(3) OVER(ORDER BY mo DESC) AS monetary_score
FROM rfm_inf
ORDER BY user_id
),

mch_nm_rnk AS (
SELECT rs.*,mf.mch_nm,
       COUNT(*) AS cnt,
       ROW_NUMBER() OVER(PARTITION BY rs.user_id
                         ORDER BY COUNT(*) DESC) AS rnk
FROM rfm_score AS rs
LEFT JOIN mt_trx_rcd_f AS mf
ON rs.user_id = mf.cust_uid
WHERE recency_score + frequency_score + monetary_score = 9
GROUP BY rs.user_id, mf.mch_nm,rs.recency_score,rs.frequency_score,rs.monetary_score
ORDER BY rs.user_id,rnk
)

SELECT user_id, recency_score, frequency_score,monetary_score,
       CONCAT(MAX(CASE WHEN rnk = 1 THEN mch_nm ELSE NULL END),', ',
       MAX(CASE WHEN rnk = 2 THEN mch_nm ELSE NULL END),', ',
       MAX(CASE WHEN rnk = 3 THEN mch_nm ELSE NULL END)) AS Top3_mch_nm
FROM mch_nm_rnk
GROUP BY user_id

啥也没说

不用GROUP_CONCAT的话，

可以用CONCAT(MAX(CASE WHEN rnk = 1 THEN mch_nm ELSE NULL END),', ',
             MAX(CASE WHEN rnk = 2 THEN mch_nm ELSE NULL END),', ',
             MAX(CASE WHEN rnk = 3 THEN mch_nm ELSE NULL END))

或者加两个新列LEAD(,1) OVER()、LEAD(,2) OVER()，再用CONCAT()；

当然GROUP_CONCAT确实方便许多

啥也没说

NTILE(7)是4,4,3,3,3,3,3

NTILE是感觉有点粗暴

多谢多谢

啥也没说

重新试了下，单独求frequency,10017分到1、10021分到2
SELECT cust_uid AS user_id,      
       COUNT(DISTINCT trx_dt) AS fre,
       NTILE(3) OVER(ORDER BY COUNT(DISTINCT trx_dt)) AS frequency_score
FROM mt_trx_rcd_f
GROUP BY user_id
ORDER BY user_id

不过我是三值一起求的，就反过来了
SELECT cust_uid AS user_id,
       /* DATEDIFF(CURDATE(), MAX(trx_dt)) AS re,*/
       NTILE(3) OVER(ORDER BY DATEDIFF(CURDATE(), MAX(trx_dt)) DESC) AS recency_score,
       /* COUNT(DISTINCT trx_dt) AS fre,*/
       NTILE(3) OVER(ORDER BY COUNT(DISTINCT trx_dt)) AS frequency_score,
       /* AVG(trx_amt) AS mo,*/
       NTILE(3) OVER(ORDER BY AVG(trx_amt)) AS monetary_score
FROM mt_trx_rcd_f
GROUP BY user_id
ORDER BY user_id

啥也没说

题干要求按rank_difference降序、cust_uid升序排序，答案是只按照前者

Error: (1690, "BIGINT UNSIGNED value is out of range……也是个大坑啊

啥也没说

在WHERE子句已经确定usr_id=5201314520下，应该没有必要在窗口函数用usr_id 作PARTITION BY吧，用日期即可，这样不必在WHERE子句检验两次交易的日期相等

还有一种特殊情况，在A店用了888，紧接着去了B店用了1288，这种算不算Ohya的

跟店铺没有关系，只要是先888后1288就算

WITH 用太多会不会不太好，性能会不会有明显差异

WITH fvck_typ_cnt AS (
SELECT DATE_FORMAT(trx_time,'%Y-%m-%d') AS date_value,
       COUNT(*) AS FvckCnt,
       SUM(CASE WHEN trx_amt = 288 THEN 1 ELSE 0 END) AS WithHand,
       SUM(CASE WHEN trx_amt = 388 THEN 1 ELSE 0 END) AS WithBalls,
       SUM(CASE WHEN trx_amt = 588 THEN 1 ELSE 0 END) AS BlowJobbie,
       SUM(CASE WHEN trx_amt = 888 THEN 1 ELSE 0 END) AS Doi,
       SUM(CASE WHEN trx_amt = 1288 THEN 1 ELSE 0 END) AS DoubleFly 
FROM cmb_usr_trx_rcd
WHERE usr_id = 5201314520
      AND trx_time RLIKE '^2024-09'
      AND mch_nm RLIKE '(按摩|保健|休闲|会所)'
GROUP BY date_value
ORDER BY date_value
),

date2409 AS (
SELECT date_value
FROM date_table
WHERE date_value RLIKE '2024-09'
),

ohya AS (
SELECT *,
       LEAD(trx_amt,1) OVER(PARTITION BY DATE_FORMAT(trx_time,'%Y-%m-%d')
                             ORDER BY trx_time) AS second
FROM cmb_usr_trx_rcd
WHERE usr_id = 5201314520
AND trx_time RLIKE '2024-09'
AND mch_nm RLIKE '(按摩|保健|休闲|会所)'
),

ohya_cnt AS (
SELECT DATE_FORMAT(trx_time,'%Y-%m-%d') AS date_value,
       COUNT(*) AS Ohya
FROM ohya
WHERE trx_amt = 888 AND second = 1288
GROUP BY date_value
)

SELECT da.date_value,
       COALESCE(fv.FvckCnt,0) AS FvckCnt,
       COALESCE(fv.WithHand,0) AS WithHand,
       COALESCE(fv.WithBalls,0) AS WithBalls,
       COALESCE(fv.BlowJobbie,0) AS BlowJobbie,
       COALESCE(fv.Doi,0) AS Doi,
       COALESCE(fv.DoubleFly,0) AS DoubleFly,
       COALESCE(oh.Ohya,0) AS Ohya
FROM date2409 AS da
LEFT JOIN fvck_typ_cnt AS fv
ON da.date_value = fv.date_value
LEFT JOIN ohya_cnt AS oh
ON da.date_value = oh.date_value

with本质上是一种视图。定理：所有with都可以改写成带括号的子查询，所有子查询都可以改成with，理论上效率并没有什么差别。

还有题干里写成红浪漫了

done

题干是2022-2023期间，答案是2023-2024期间

Done

示例要求升序，实际是按降序

改了

前面说DATEDIFF()和TIMESTAMPDIFF() 结果可能会不一样
从这里看DATEDIFF()在实践中更符合实际一些？
1566000008这位第N次和第N+4次最小间隔是79小时，如果用TIMESTAMPDIFF()，他就不算了

好问题。通常意义上领导如果交给你一个任务，三天后完成，领导说的就是第三天的下班前；而不是72小时内。😁。不过观察很仔细，这里datediff够用了。

LAG(,n,m)，n 为偏移量（下移n行），m为前n行的补位值，默认为NULL

啥也没说

哥德堡老哥好像比较喜欢用COUNT(逻辑表达式 OR NULL)，终于大致搞懂啥意思了，是挺方便的
TRUE OR NULL → TRUE
FALSE OR NULL → NULL
NULL OR NULL → NULL

啥也没说

先求出每季度各项目的次数（辅助列），再用SUM() OVER()，再把第一步注释掉，就是trx_quarter的代码在窗口函数里要重复写两遍，看起来有点繁琐

SELECT CONCAT(YEAR(trx_time),'-Q',QUARTER(trx_time)) AS trx_quarter,
       /* SUM(CASE WHEN trx_amt = 288 THEN 1 ELSE 0 END) AS withhand1,*/
       SUM(SUM(CASE WHEN trx_amt = 288 THEN 1 ELSE 0 END)) OVER(ORDER BY CONCAT(YEAR(trx_time),'-Q',QUARTER(trx_time))) AS withhand,
       /* SUM(CASE WHEN trx_amt = 888 THEN 1 ELSE 0 END) AS doi1,*/
       SUM(SUM(CASE WHEN trx_amt = 888 THEN 1 ELSE 0 END)) OVER(ORDER BY CONCAT(YEAR(trx_time),'-Q',QUARTER(trx_time))) AS doi
FROM cmb_usr_trx_rcd
WHERE usr_id = 5201314520
AND mch_nm = '红玫瑰按摩保健休闲'
AND trx_time RLIKE '^202[34]'
GROUP BY trx_quarter
ORDER BY trx_quarter

啥也没说

本来也想直接一个左连接查询出来，应该是SUM(SUM()) OVER，不过想当然了变成了SUM() OVER()

啥也没说

一个查询里同时出现GROUP BY 和窗口函数里的PARTITION BY，不必有困扰，虽说二者在分组上有一定相似之处；
GROUP BY子句内写什么和PARTITION BY没有关系，PARTITION BY 只作用于窗口函数内，就当成没有窗口函数来写。

啥也没说

一个查询里同时出现GROUP BY 和窗口函数里的PARTITION BY，不必有困扰，虽说二者在分组上有一定相似之处；
GROUP BY子句内写什么和PARTITION BY没有关系，PARTITION BY 只作用于窗口函数内，就当成没有窗口函数来写。

啥也没说

如果是TIMESTAMP类型或者DATETIME类型，即YYYY-MM-DD HH:MM:SS格式，TIMESTAMPDIFF()和DATEDIFF()计算天数差可能会存在差异
DATEDIFF() 会先截取前面的DATE在直接计算差值
TIMESTAMPDIFF() 是先求时间差值，再以24小时为一个单位向下取整，相差23时59分59秒也是0天

SELECT DATEDIFF('2025-02-07 14:13:20', '2025-02-06 14:13:21') ,
       TIMESTAMPDIFF(DAY,'2025-02-06 14:13:21','2025-02-07 14:13:20')

SELECT DATEDIFF('2025-02-07 00:00:00', '2025-02-06 23:59:59') ,
       TIMESTAMPDIFF(DAY,'2025-02-06 23:59:59','2025-02-07 00:00:00')

啥也没说

我自己肯定想不出这种写法，那老哥的思路，我大致能理解一点，但这个HAVING子句里的count(usr_id = '5201314520' or null)又不太能理解。。。所以按自己思路写了下

用熟了就发现 count case when sum case when 真香

要求是休闲娱乐类目

👍

YEAR()
DATE_FORMAT(,'%Y')
RLIKE '2024'
LEFT(,4)
SUBSTRING(,1,4),SUBSTRING( FROM 1 FOR 4)
SUBSTR(,1,4)

用于过滤时，哪种都行；
用于输出，尤其是工程化、有下游会使用你的数据时，比如excel、或者pandas去读取，最好还是用时间日期提取，这样可以跟下游格式保持一致。

参照前面哥德堡老哥的思路写一个（我自己觉得）相对好理解的
SELECT
  mch_nm
FROM
  cmb_usr_trx_rcd
WHERE
  trx_time RLIKE '2024'
GROUP BY
  mch_nm
HAVING
  COUNT(
    DISTINCT CASE WHEN usr_id IN (5201314520, 5211314521) THEN usr_id ELSE NULL END
  ) = 2
ORDER BY
  mch_nm DESC

果然人跟人的脑回路是不一样的。
我倒觉得inner join更好理解。你这个写法没到一定水平的小白，脑子抓破了也难以理解，适合高阶人士使用哈哈

DATE_FORMAT(,'%Y-%m')
LEFT(,7)
SUBSTRING( FROM 1 FOR 7),SUBSTRING(,1,7)
SUBSTR(,1,7)

同上次回复。过滤时用哪种都行。

不用truncate()的话，直接RLIKE '[89]8\.'；截取整数部分还可以用FLOOR()，非负数范围内。

你这算是小幅度使用正则表达式了，👍
正则表达式完全可以单独开一个专题，不过咱们这里是SQL，有限使用～

CASE WHEN的某一分支满足后，就不会再继续判断后续分支

是的，很容易被忽略的知识点。

注意now()不在 BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND CURDATE()里

啥也没说

用TIMESTAMPDIFF(YEAR,)会得出差半年的答案，应该以哪个为准？

年龄计算，至少有4种口径。
1、按你说的直接取年再相减；
2、日期相减除365取整数；
3、日期相减除365.25取整数（考虑闰年）；
4、年份相减，然后看今天是否已经过完了生日，如果没有就减一。

具体以业务为准～

注意TIMESTAMPDIFF后要再加1；这题保留两位小数会没区别

加一细节了，👍

输出示例的列名变成fm_category了

我修正后台check逻辑了，不再要求列明严格对应。

参考答案里用NTILE处理每次交易的平均消费金额时，没有降序。

不过不应该平均消费金额高的客户更有价值些，所以赋高分么？

不用降序。

整个系列我留了一些坑，没认真刷题的同学压根体会不到。

窗口函数里的ORDER BY子句可以有多列

10017、10021这俩人frequency相同，但刚好分到了两类，我这里是把10017分到2、10021分到1，和参考答案跑出来的相反，不知道咋回事，这里窗口函数的写法应该是一样的。我是在窗口函数ORDER BY 子句里又加了user_id弄符合的

本题共23个用户。
ntile(5)over(order by frequency_days, cust_uid):5, 5, 5, 4, 4；
ntile(4)over(order by frequency_days, cust_uid):6, 6, 6, 5;
ntile(3)over(order by frequency_days, cust_uid):8, 8, 7。
跟order by 几个字段没有关系。ntile(5)最神奇，我本以为应该是55553，没想到是55543。

也即ntile有点抽风，其分组原理让人琢磨不透，可解释性差。

实际业务中RFM的划分，还是需要业人为界定分界点，再用case when来处理的。

这题，你问到就是赚到哈哈哈

答案有误，和上一题一样按次数来的

Done

这里最后统计搜索次数，不应该DISTINCT search_tm 吧，N个用户在同一时间进行了搜索，还是应该算N次搜索，不应该算1次吧，还是我哪里理解出差错了？

没理解错。搜索次数（左表行数）是分母，点击次数（不空的行数）是分子。
已修改。

也可以直接将收藏表和购买表联结，条件是cust_uid相等；通过CASE WHEN 获得先收藏再购买的用户，条件是商品id相等以及收藏时间小于购买时间，再计数

棒！👍

保留小数时，要求整数依旧是整数，只将小数保留成两位小数，用CAST( AS float)？

select cast(90.01 as float) -->90.01；
select cast(90.00 as float) -->90。
可以系统的总结cast round处理小数点的问题了。

我这么写跑出来的结果和参看答案出来的应该是一样的（用文本比较软件比对过），不过最后还是提示有误，是哪里出问题了呢

WITH class_score_cnt AS (
SELECT st.class_code,
       COUNT(*) AS total_students,
       SUM(CASE WHEN sc.score >= 110 THEN 1 ELSE 0 END) AS excellent_cnt,
       SUM(CASE WHEN sc.score >= 90 AND sc.score < 110 THEN 1 ELSE 0 END) AS good_cnt,
       SUM(CASE WHEN sc.score >= 60 AND sc.score < 90 THEN 1 ELSE 0 END) AS pass_cnt,
       SUM(CASE WHEN sc.score < 60 THEN 1 ELSE 0 END) AS fail_cnt
FROM scores AS sc
LEFT JOIN students AS st
ON sc.student_id = st.student_id
WHERE sc.exam_date = '2024-06-30' AND sc.subject = '数学'
GROUP BY st.class_code
  )

SELECT class_code, total_students,
       CONCAT(excellent_cnt,', ',CAST(excellent_cnt/total_students*100 AS decimal(4,2)),'%') AS excellent,
       CONCAT(good_cnt,', ',CAST(good_cnt/total_students*100 AS decimal(4,2)),'%') AS good,
       CONCAT(pass_cnt,', ',CAST(pass_cnt/total_students*100 AS decimal(4,2)),'%') AS pass,
       CONCAT(fail_cnt,', ',CAST(fail_cnt/total_students*100 AS decimal(4,2)),'%') AS fail
FROM class_score_cnt
ORDER BY class_code

啥也没说

好像可以直接用COUNT(CASE WHEN ),4个区间4个COUNT(CASE WHEN)

写出来

这文理不分的吗，学得累死，我也写得累

哈哈哈 还没分班吧 高一

直接SELECT ROUND(30.3030,2)，结果是30.30…………

就是之前说的情景。直接写出来的和算出来的不一样。

ROUND会根据不同情境做四舍五入，这是真的吗？我以后都用CAST好了。。。

没毛病，cast最稳妥

将每个用户的订单按购买时间排序后，再使用LEAD构造新的一列，即把第二单的购买种类上移放到第一单后面，不过也要注意存在着至少购买3次的用户，所以WHERE子句里要限制rnk = 1，不然会把二单为Mac和三单为iPhone的用户也算在内

人家面试题还是有点水平的，一题多解

也可以先求每个用户的首单时间MIN，再将此表和购买记录表联结，用户名相同以及首单时间与购买时间相等，然后用CASE WHEN

👍。买过苹果、买过2次苹果、没买过苹果、只买苹果、第一次就买苹果，可以出个系列了

YEARWEEK(time,a),a表示以周几为一周第一天，默认是周日

通过取第一天加深对该函数的认识，2023-01-01、2024-01-01、2025-01-01会有不同的答案，去试试

如果用ROUND(,2)，深圳2013年平均最高气温会是23.12，用CAST(AS decimal(,2))会是23.13

深圳平均最高气温是23.12500000。
1、如果直接select round(23.125,2)是返回23.13；但在答案中返回23.12。round还会根据不同情境做四舍五入，好神奇。
2、cast 也有四舍五入功能；如果只想截取不想舍入，要用TRUNCATE()

所以这里为什么30.3030保留2位会变成30.3呢？还要多走一步CAST

round，如果最后一位是0，如30.3030，会变成30.3，直接用cast as decimal 能严格控制位数，不用管最后一位是否为0.

ORDER BY 子句可以使用SELECT中未出现的列；之前看书看到过，后面忘了,这里又碰到了

没出现过、没计算过的都可以用于排序

按照答案来看是求一元一次函数，非一元二次函数

啥也没说

输出示例是3871，不过我跑出来是3471，显示正确

数据是动态变化的

同样用子查询，相比IN，这个exists的效率如何？再和多表联结比呢

实战中，join > exists > in。因为join能有效利用优化器、索引；exists有break功能，只需要找到一个匹配的记录就可以停止搜索；in的话相当于每一行都要做一次in里面的条件查询。

参看答案把提取小时搞错了吧
start_tm现在这个形式好像只是某种简写性的呈现形式，但用SUBSTR去切片的话，还是按'aa:bb:cc'来操作的，SUBSTR(start_tm,4,2)切出来恰好是分钟部分
题干里也说了，用EXTRACT提取小时部分，挺方便的

👍，已修正

那个播放量为0的歌手是不是也应该要纳入统计

哈哈哈！改成left join，把🐔哥加进去。

为什么参考答案里GROUP BY 去掉s.song_id；答案就提示有误了，这里好像也不存在两位歌手各自有一首同名歌曲的情况，单用song_name做分组好像已经足够

加上排序后，输出结果就一样了。

输出示例好像是求各个年代的专辑数量，与题目不符

啥也没说

题干写的是歌手名字和专辑名字，有误

啥也没说

参考答案里的HAVING 子句是必须加的吗？实践中，播放次数为1的视频不计入统计？

创作者自己一般发布后会再看一遍，肯定就完播了。这种100%的完播率不该被统计

WITH fav_gd AS (
  SELECT
    DISTINCT mch_id
  FROM
    xhs_fav_rcd
),
pchs_gd AS (
  SELECT
    DISTINCT mch_id
  FROM
    xhs_pchs_rcd
)
SELECT
  gd.*,
  CASE
    WHEN fav.mch_id is NOT NULL
    AND pchs.mch_id IS NOT NULL THEN 'Collected and Purchased'
    WHEN fav.mch_id is NOT NULL
    AND pchs.mch_id IS NULL THEN 'Only Collected Not Purchased'
    WHEN fav.mch_id is NULL
    AND pchs.mch_id IS NOT NULL THEN 'Only Purchased Not Collected'
    ELSE 'Neither Collected NOR Purchased'
  END AS category
FROM
  gd_inf AS gd
  LEFT JOIN fav_gd AS fav ON fav.mch_id = gd.gd_id
  LEFT JOIN pchs_gd AS pchs ON pchs.mch_id = gd.gd_id
ORDER BY
  gd.gd_id

啥也没说

自己写的跑出来的还有参考答案跑出来的结果和输出示例应该是一样，但还是提示错误

tag改成中文后能通过了。

注意是收到红包；存在一些未被接收的红包

手撕代码的笔试题很喜欢挖这类坑，滴滴取消订单的面试题，就是通过特殊日期标识“订单取消”

参考答案好像是未排序的；有几只1990-12-19上市的公司

👍

标题和题干是要求5月2日，答案是5月3日的记录

👍

子查询或者多表联结

你说的子查询应该是用IN？
子查询 IN：
对于小数据集或当子查询的结果集较小时，子查询 IN 可能表现良好。
如果子查询返回大量的值，性能可能会受到影响，尤其是在没有适当索引的情况下。
多表连接（JOIN）：
通常来说，JOIN 操作更为高效，因为现代数据库系统对 JOIN 的优化做得非常好，尤其是当你有适当的索引时。
如果需要从多个表中检索数据并且这些表之间存在关系时，JOIN 是首选方法。
MySQL查询优化器倾向于更好地处理JOIN，并且能够利用索引来加速匹配过程。看具体数据库的安排。

我之前的实践是，hive上对海量（百万级以上）数据处理时，首选JOIN。时间差了至少3倍。

以中国结尾的公司有两家

啥也没说

题干要求是中国开头或者包含中字，但答案里是中国开头或中字开头，范围变小了，例如互联网、中成药两个行业最早上市公司就变化了

啥也没说

昨晚忘了说了。。。答案是以中国开头或包含中字；刚好和修改后的反过来了

啥也没说

多谢多谢，解决不少疑惑

啥也没说

如果三表联立，直接用CASE WHEN 统计不及格人数，要注意先去重

啥也没说

一次函数和X轴永远有交点，二次函数要判别式<0，常数函数要常数c<>0，参考答案里只有常数函数一种情况

好好好，这题下下周见！

答案应该有问题，比如示例id=103，这个函数与X轴交点为-2；
有直线上倾和下倾两种情况；或者直接交点是-c/b>0，c*b < 0

严谨啊哥哥，连夜修改

标题是包含中国或中字开头，题干里是以中国（开头？）或包含中字，应该统一

done

为什么这里用ROUND(,5)保留5位小数，但最后还是显示4位，而参考答案里用一下*1.0就能变成5位小数，*1.00变成6位小数

1、为什么数4位。
跟系统变量有关系，SELECT 11/7、SELECT round(11/7,5)、SELECT round(11/7,10)默认输出都是4位数字；
2、乘以1.0后者1.00是乘以了浮点数，实际上是在告诉 MySQL 使用浮点数或 decimal 类型来进行除法运算，而不是整数除法。这可以确保结果保留更多的小数位。
3、为什么加一个0变5位、加2个0变6位，可能是mysql特性吧；
4、3位小数点以内用round没问题；如果太长了，还是decimal+手搓吧

输出示例里依然用了交易所的字母简写，和上一题一样

已修改。

可以感受下这3题的递进关系，本意是让初学者感受case when用于分类前的打标

题干里是统计地产，标题和示例里都是软件服务

已统一为软件服务和银行

这里学生人数不应该去重吗

done

本来用子查询，有新方法

啥也没说

题干学科里有政治，但输出示例包含生物

感谢指正！高中毕业15年了忘了文科是政史地了哈哈哈

发现参考答案和跑出来显示正确的答案不一样

啥也没说

这里订单表和用户角色表联结是必须的么

啥也没说

不是要保留两位小数么；被ROUND，CAST保留小数位数搞得有些糊涂了。。。

都能舍入；cast能严格控制位数。

跑出来的结果和示例一样的，写法和上一题（每个城市）也一致，是哪里理解错了么？参考答案里cty改成order_dt，结果和示例也一样，但还是有问题

啥也没说

不是要保留两位小数么，这个CAST函数具体有什么用

啥也没说

这里的正则表达式好像不支持\d ？

啥也没说

网站的MySQL不支持INTERSECT吗

对，mysql没有的

CAST(number AS DECIMAL(m,n))
m表示转化后的结果能支持的最大位数（整数部分+小数部分），n表示小数部分位数
CAST(3.14159 AS DECIMAL(6,4)) 得到3.1416，会四舍五入

啥也没说

这题，网站这里使用ROUND四舍五入后会把末尾的0给舍去，27.90变27.9
但运行SELECT ROUND(3.1415904,6) 结果又是3.141590,有点懵逼了

啥也没说

我这里可以跑通

啥也没说

题目标题以及答案是第一笔未完成订单，题干里是所有未完成订单

啥也没说

感觉这道题有点奇怪，答案里album_id=10压根不在歌曲信息表里。
本来我是想找出听歌记录表里出现过的song_id，再在歌曲信息表里找补集（NOT IN）。

没毛病啊，歌手信息、歌曲信息、专辑信息本来就不是一一对应的关系。

直接单独用听歌记录表得到的结果是一样的，不过是要统计每个用户，所以保险起见先将用户表和听歌记录表连接在操作？

啥也没说

把答案里的坑改了后运行，发现有一个1-5天分段的人被划分到6-10天里去了，不知道是哪里有问题

啥也没说

题干说超过110，应该是>，但答案好像等于110也行，比如输出示例里235411这个学生的数学成绩

喵的太严谨了，连夜改成大于等于

假如现在是1月份，那就无法用MONTH简单处理了

啥也没说

题干说取前10，答案好像是要取前5

啥也没说

花色就同色或异色两种情况，所以不用管具体的花色组合；注意题干里未出现的KA组合

啥也没说

可以DISTINCT area 看看包括哪些

啥也没说

这里用MAX函数似乎挺突兀的，只要trx_time 不是NULL，不管MAX函数使用与否，last_day好像都能得到一样的结果；不用MAX的话，最后也不用GROUP BY而使用DISTINCT，一样能得到这题的答案；或者MAX换成MIN似乎也可以
当然因为后面求每月日均，必须用到GROUP BY，SELECT子句里不能出现聚合键以外的列名，除非使用了聚合键,不知道有没有理解错

如果你要去掉前面的max（或者你说的min）也可以，group by改成1,2,3 就行。

最大的原因其实是一个用户在某一个月可能有多笔交易，本题只需要取当月任意一笔就可以了。

最开始给出这系列最后的输出表第三个字段是days_of_mon，到下面这个字段就变成day_of_mon了，应该统一

字段问题已不用纠结。
已修改check逻辑，字段名对不上也能通过了

题解里的两种写法似乎是一样的？

多种写法试试如何提取年

匹配整数部分是88、98结尾可用 RLIKE '.+[89]8'
原本把金额在200以上和时间条件写在WHERE子句里，这样就把other消费记录给大大减少了

手动赞

第二个写了注释，还以为第二个才正确，看了好久

啥也没说