同花不应该是right(card1, 2) = right(card2, 2)吗？

select 
    right('2♠',1)
   输出什么？

字符长度不应该是char_length()吗

看题干 注意，“周杰伦”的字符长度应为9。

没有限定在哪段时间内求峰值

没限定就是默认为全部哦

with login_before as (
  select
  	distinct usr_id
  from
  	user_login_log
  where
  	date(login_time) <= '2024-10-01'
),
login_after as (
  select
  	distinct usr_id
  from
  	user_login_log
  where
  	date(login_time) >= '2024-10-02'
)
select
	count(b.usr_id) - count(a.usr_id) as inactive_user_count
from 
	login_before b
left join
	login_after a on a.usr_id = b.usr_id
;
为什么不对呢？

还是你牛，你的是对的。更原始更直观。

有一处不解：在monthly_top_merchants里既使用group by 分组，又在over()里使用了partition by，岂不是按照 trx_mon、mch_nm分组之后再用trx_mon分组吗？为什么最终结果还是对的呢？

这说明你对窗口函数还没有真正理解。如果over()里不使用partition by，输出的是什么呢？

你多试试这样的题。每个月交易笔数前三的商户、每次考试每个年级每个班级物理前三名的用户。

这样写为什么结果和参考答案不一样呢？
select 
	distinct u1.mch_nm 
from 
	cmb_usr_trx_rcd u1 
inner join 
	cmb_usr_trx_rcd u2 on u1.mch_nm = u1.mch_nm
where
	year(u1.trx_time) = 2024
    and 
    year(u2.trx_time) = 2024
    and 
    u1.usr_id = 5201314520
    and 
    u2.usr_id = 5211314521
order by
	u1.mch_nm desc;

两个问题。1、你的连接条件是什么；2、你的where条件能识别出【都】去过吗

我搞错了，两家分别统计的结果相加应该=2，不是>=2
having
	count(distinct case when u1.mch_nm = '庄家界(千灯店)' then u1.cust_uid else null end) + count(distinct case when u1.mch_nm = '黄记烘培宫廷桃酥王' then u1.cust_uid else null end) = 2

啥也没说

我觉得我这种写法比较简洁。
在过滤cust_id_1的时候要满足用户两家店都去过，也就是两家店都能在mch_nm里找到，那么就应该分别对两家进行count，因为如果只是用 mch_nm in ('庄家界(千灯店)', '黄记烘培宫廷桃酥王') 来判断，那么只吃过其中一家（超过两次）的用户就会被计入。
select 
	u.cust_uid,
        u1.cust_uid as cust_uid_1
from 
	mt_trx_rcd1 u 
left join
	mt_trx_rcd1 u1 on u.cust_uid <> u1.cust_uid
where
	u.cust_uid = 'MT10000'
group by
	u.cust_uid,
        cust_uid_1
having
	count(distinct case when u1.mch_nm = '庄家界(千灯店)' then u1.cust_uid else null end) + count(distinct case when u1.mch_nm = '黄记烘培宫廷桃酥王' then u1.cust_uid else null end) >= 2; 

啥也没说

with groupings as (
  select
  	so.album_id,
  	so.song_id,
  	so.song_name,
  	count(distinct r.user_id) as user_count,
  	count(r.user_id) as play_count
  from
  	singer_info si 
  inner join 
  	song_info so on so.origin_singer_id = si.singer_id
  inner join 
  	album_info a on so.album_id = a.album_id
  inner join 
  	listen_rcd r on r.song_id = so.song_id
  where
  	si.singer_name = 'Michael Jackson'
  group by
  	so.album_id,
  	so.song_id,
  	so.song_name
),
top_album as (
  select
  	album_id,
  	sum(user_count) as album_user_count
  from
  	groupings
  group by
  	album_id
  order by
  	album_user_count desc
  limit 1
)
select 
	g.song_name,
    sum(g.play_count) as total_play_count,
    sum(g.user_count) as total_user_count
from 
	top_album t 
inner join 
	groupings g on t.album_id = g.album_id
group by
	g.song_name 
order by
	total_play_count desc
limit 3;

啥也没说

题干要求找到最受欢迎的“专辑”，参考答案没有涉及专辑，计算的是最受欢迎的歌曲

已修改

我在第一个子查询的group by里没有添加video_id，因为我觉得可以直接统计每个类别的观看用户数和观看至视频结束的用户数，但是这样计算并没有得到输出示例里的结果，p行的数据是对的，l行AI_with_hint no_AI_with_hint数据不对。为什么会这样？
with group_counting as (
  select
  	inf.screen_type,
  	inf.if_AI_talking,
  	inf.if_hint,
  	count(distinct case when timestampdiff(second, log.start_time, log.end_time) >= inf.duration then log.uid else null end) as finish_count,
  	count(distinct log.uid) as total_count
  from
  	ks_video_inf inf 
  inner join 
  	ks_video_wat_log log on log.video_id = inf.video_id
  group by
  	inf.screen_type,
  	inf.if_AI_talking,
  	inf.if_hint
)
select 
	screen_type,
    round(100 * max(case when if_AI_talking = 1 and if_hint = 1 then finish_count else 0 end) / nullif(max(case when if_AI_talking = 1 and if_hint = 1 then total_count end), 0), 2) as AI_with_hint,
    round(100 * max(case when if_AI_talking = 1 and if_hint = 0 then finish_count else 0 end) / nullif(max(case when if_AI_talking = 1 and if_hint = 0 then total_count end), 0), 2) as AI_no_hint, 
    round(100 * max(case when if_AI_talking = 0 and if_hint = 1 then finish_count else 0 end) / nullif(max(case when if_AI_talking = 0 and if_hint = 1 then total_count end), 0), 2) as no_AI_with_hint, 
    round(100 * max(case when if_AI_talking = 0 and if_hint = 0 then finish_count else 0 end) / nullif(max(case when if_AI_talking = 0 and if_hint = 0 then total_count end), 0), 2) as no_AI_no_hint
from 
	group_counting
group by
	screen_type;

再理解下这种写法：SELECT 
    i.screen_type,
    ROUND(COALESCE(
        SUM((TIMESTAMPDIFF(SECOND, l.start_time, l.end_time) >= i.duration) * (i.if_AI_talking = 1 AND i.if_hint = 1)) 
        * 100.0 
        / NULLIF(SUM(i.if_AI_talking = 1 AND i.if_hint = 1), 0), 
        0
    ), 2) AS AI_with_hint,
    ROUND(COALESCE(
        SUM((TIMESTAMPDIFF(SECOND, l.start_time, l.end_time) >= i.duration) * (i.if_AI_talking = 1 AND i.if_hint = 0)) 
        * 100.0 
        / NULLIF(SUM(i.if_AI_talking = 1 AND i.if_hint = 0), 0), 
        0
    ), 2) AS AI_no_hint,
    ROUND(COALESCE(
        SUM((TIMESTAMPDIFF(SECOND, l.start_time, l.end_time) >= i.duration) * (i.if_AI_talking = 0 AND i.if_hint = 1)) 
        * 100.0 
        / NULLIF(SUM(i.if_AI_talking = 0 AND i.if_hint = 1), 0), 
        0
    ), 2) AS no_AI_with_hint,
    ROUND(COALESCE(
        SUM((TIMESTAMPDIFF(SECOND, l.start_time, l.end_time) >= i.duration) * (i.if_AI_talking = 0 AND i.if_hint = 0)) 
        * 100.0 
        / NULLIF(SUM(i.if_AI_talking = 0 AND i.if_hint = 0), 0), 
        0
    ), 2) AS no_AI_no_hint
FROM 
    ks_video_inf i
JOIN 
    ks_video_wat_log l ON i.video_id = l.video_id
GROUP BY 
    i.screen_type;。这个是最直接的，没有弯弯绕绕的多次计算。你写的相当于多了一次去重。把分子分母都搞小了。

这题容易在最后一步忘记限定考试日期

啥也没说

这一题好像比较容易忽略教师的科目还是要限制的

啥也没说

为什么参考答案不关联点击记录表？如果没有不关联点击记录表，应该就没有办法限定用户是按照“搜索——点击——购买”的流程消费的吧？万一用户在同一个session当中既搜索了关键词又从搜索结果以外的地方点击页面购买商品呢？

手动点赞！你这才算是深入理解了漏斗。漏斗需要人来定义的，你的是灰常严格的定义👍

参考答案末尾 ORDER BY completion_rate DESC; 
但是输出示例似乎是按照日期排序的

啥也没说

用一次group by 可以同时计算出订单总数和有效订单数，count(*) count(case when……)

啥也没说

使用两表联合，条件是商品id相等和用户ID相等，但是在where语句里过滤收藏时间早于购买时间却产生了奇怪的结果：收藏用户和购买用户数量相等了。代码如下：
with purchase_cnt as (
  select
  	f.mch_id,
  	count(distinct f.cust_uid) as fav_user_cnt,
  	count(distinct p.cust_uid) as pcs_user_cnt
  from
  	xhs_fav_rcd f 
  left join 
  	xhs_pchs_rcd p on p.mch_id = f.mch_id and p.cust_uid = f.cust_uid 
  where
  	p.pchs_tm > f.fav_tm
  group by
  	f.mch_id
)

select 
	mch_id,
    fav_user_cnt,
    pcs_user_cnt,
    round(100 * cast(pcs_user_cnt as float) / fav_user_cnt, 2) as convert_rate
from 
	purchase_cnt
group by
	mch_id,
    fav_user_cnt,
    pcs_user_cnt
order by
	convert_rate desc;
后来把 p.pchs_tm > f.fav_tm 写在了on里面 on p.mch_id = f.mch_id and p.cust_uid = f.cust_uid and p.pchs_tm > f.fav_tm，结果就都对了。为什么会这样？

啥也没说

为什么这里用danse_rank？

啥也没说

group by 的参数有很多时，group by 是怎样分组的呢？我们想让它以start_ctg和end_ctg分组，但是r.start_loc和r.end_loc不会干扰吗？

啥也没说

起点是餐饮、终点是餐饮两个子查询，同时在两个集合中的地点：id相同，且时间相同
with start_catering as (
  select
  	r.cust_uid,
  	r.start_loc,
  	r.end_loc,
  	r.start_tm,
  	r.car_cls
  from
  	didi_sht_rcd r 
  inner join
  	loc_nm_ctg l on l.loc_nm = r.start_loc
  where
  	l.loc_ctg = '餐饮'
),
end_catering as (
  select
  	r.cust_uid,
  	r.start_loc,
  	r.end_loc,
  	r.start_tm,
  	r.car_cls
  from
  	didi_sht_rcd r 
  inner join 
  	loc_nm_ctg l on l.loc_nm = r.end_loc
  where
  	l.loc_ctg = '餐饮'
)
select 
	s.* 
from 
	start_catering s 
inner join 
	end_catering e on s.cust_uid = e.cust_uid and s.start_tm = e.start_tm
order by
	s.start_tm asc
;

啥也没说

这一题如果用DISTINCT怎么做呢？

啥也没说

select 
	t.name,
    count(distinct s.student_id) as total_stu,
    count(distinct (case when sc.score < 60 then s.student_id else null end)) as unpass_stu,
    count(distinct case when sc.score < 60 then s.student_id else null end) / count(distinct s.student_id) as up_rate
from students s 
inner join 
	teachers t on t.class_code like concat('%', s.class_code, '%')
inner join
	scores sc on s.student_id = sc.student_id
where
	t.subject = '化学'
group by
	t.name
;

这样写有问题吗？未及格学生人数和正确结果有1-3的误差，不知为什么。

仔细阅读你的链接条件，化学老师所带班级的学生成绩，你这样是把所有科目成绩都算进去了。要么在case when条件里加一个sc.subject='化学'，要么在where 条件里过滤出化学成绩。

select 
	student_id
from scores 
group by
	student_id
having
	count(exam_date) = count(score)
order by
	student_id;

啥也没说

select 
	name,
    subject,
    class_code
from 
	teachers 
where 
	class_code like 'C___C___C___'
order by
	name asc;

啥也没说

select 
	name,
    subject,
    class_code
from 
	teachers 
where
	class_code like 'C___C___C___C___C___'
order by
	name asc;
 

啥也没说

with score_ranges as (
  select 
  	st.class_code,
  	count(*) as class_total, 
	sum(case when sc.score >= 110 then 1 else 0 end) as excellent,
    sum(case when sc.score between 90 and 109 then 1 else 0 end) as good,
    sum(case when sc.score between 60 and 89 then 1 else 0 end) as pass,
    sum(case when sc.score < 60 then 1 else 0 end) as fail
  from
  	students st
  inner join
  	scores sc on sc.student_id = st.student_id
  where
  	exam_date = '2024-06-30' and sc.subject = '数学'
  group by
  	st.class_code
)
select 
	class_code,
    class_total,
    concat(excellent, ' ,', round(100 * excellent / class_total, 2), '%') as excellent,
    concat(good, ' ,', round(100 * good / class_total, 2), '%') as good,
    concat(pass, ' ,', round(100 * pass / class_total, 2), '%') as pass,
    concat(fail, ' ,', round(100 * fail / class_total, 2), '%') as fail
from score_ranges
group by class_code
order by class_code;
 

啥也没说

with total_levels as (
  select 
  	student_id,
  	(case when score >= 110 then 1 else 0 end) as excellent,
  	(case when score between 90 and 109 then 1 else 0 end) as good,
  	(case when score between 60 and 89 then 1 else 0 end) as pass,
  	(case when score < 60 then 1 else 0 end) as fail
  from scores
  where exam_date = '2024-06-30' and subject = '数学'
)
select 
	s.class_code,
    sum(excellent) as excellent,
    sum(good) as good,
    sum(pass) as pass,
    sum(fail) as fail
from 
	total_levels as t 
inner join 
	students as s on s.student_id = t.student_id
group by
	s.class_code
order by
	s.class_code;

啥也没说

这题只要scores一个表就行了吧？
case when好像会短路，所以要从大数往小了判断

短路这个词用的好啊。关于短路，你再研究研究，要是我就想从小到大来写，又该怎么办呢？（理论上只需要保证所有条件符合MECE原则，管你怎么写，都能算出来）

a=0,b>0时，c是否应该>0才能经过第二象限？
a>0时，是否不需要其他条件曲线都能经过第二象限？
select * 
from numbers_for_fun 
where
	(
      a = 0 and
      (
        (b > 0 and c > 0)
        or 
        (b < 0)
        or 
        (b = 0 and c < 0)
      )
    ) 
    or a > 0
    or (
      a < 0 and 
      (
        (b > 0 and c > 0)
        or 
        (b < 0 and c > b*b/4/a)
      )
    );

只有你在认真刷题。我忘改标题了，是经过第【四】象限的所有函数

select 
    distinct cust_uid, 
    mch_nm 
from mt_trx_rcd1 
where cust_uid = 'MT10000'
order by mch_nm;
也可以

是的。
deepseek询问 distinct 和group by哪个效率更高，分别使用什么场景，让自己再多学一点知识

c = 0 直线也可以经过两个象限啊

只要是一元一次函数，他一定经过至少两个象限是不？

select * 
from numbers_for_fun 
where
	a = 0 
    and 
    b <> 0
    -(c/b) > 0
order by id;
直线与x轴的交点：bx+c=0, 那么x=-(c/b)。应该是这样的吧？

 -(c/b) > 0 跟参考答案的条件是一样的哦

song_info 要用left join，是因为有些歌手或专辑可能没有歌曲在线？我用inner join最终没有结果。所以“播放量为0”包含了“有歌曲在线无播放记录”和“歌手的歌曲不在线因此无从播放”两个子集？

这题考的就是inner join和left join的使用场景。你运行这段代码试试，SELECT 
    s.singer_id,
    s.singer_name,
    a.album_id,
    a.album_name,
    COUNT(l.id) AS play_count
FROM 
    singer_info s
JOIN 
    album_info a ON s.singer_id = a.singer_id
inner JOIN 
    song_info sg ON a.album_id = sg.album_id
inner JOIN 
    listen_rcd l ON sg.song_id = l.song_id
GROUP BY 
    s.singer_id, s.singer_name, a.album_id, a.album_name。

参考答案和输出示例给出的是接收红包的用户的id，但是题设给的要求是发出红包用户id。
而且参考答案里的year(rcv_datetime) != 1900也不知所谓。

啥也没说

sum(
   case
      when chinese >= 110 then 1 
      when math >= 110 then 1
      when english >= 110 then 1
      else 0
   end
) >= 2
这种判断不能按预想的执行，因为case when 短路了，第一个条件执行完后面的就不判断了，所以sum永远不能得到2

啥也没说

sum(
 case 
when
)

啥也没说

涨跌幅如果是按照给出的公式计算的话，应该是小于1的小数吧，不是百分比吧？

啥也没说

有很多订单有应答后面又被取消了，所以应答时间和是否取消订单没关系啊

啥也没说

提问：SQL语句的执行顺序不是from-where-group by-having-select吗？为什么在select里声明的ser_typ可以用在group by里呢？执行group by的时候SQL怎么知道有ser_typ？

还有一个隐藏的动作 execute（执行），select你可以理解为渲染、挑选，把执行后的结果捡出来，捡的实际动作就是纯捡，不产生任何新的字段。

对日期使用 between and 不是默认两个边界值都是0点吗？between '2024-09-01' and '2024-09-30'就是2024-09-01 00:00:00到2024-09-30 00:00:00，那么如果表里有9月30号0点以后的记录也不会被查询到了？

是的 看第一题 你可以实验一下