with tem as(
select 
i.cust_uid,
i.age,
i.gdr,
case when i.age<25 and i.gdr='F' then 1 else 0 end as young_lady,
count(case when r.if_vw=1 then r.prd_id else null end) as browsed_count,
count(case when r.if_buy=1 then r.prd_id else null end) as buy_count
from tb_cst_bas_inf i
left join tb_pg_act_rcd r on i.cust_uid=r.cust_uid
group by 1,2,3
)
select 
cust_uid,
age,
gdr,
young_lady,
case when browsed_count>=2 then 1 else 0 end as browsed_multiple_products,
case when buy_count>=2 then 1 else 0 end as bought_multiple_products
from tem
order by 1

Note一下写法

啥也没说

select i.video_id,
i.title,
cast(sum(timestampdiff(second,w.start_time,w.end_time)/3600)as decimal(10,2)) as total_play_duration_hours
from ks_video_inf i
left join ks_video_wat_log w on i.video_id=w.video_id
where date(w.start_time)>adddate(current_date,interval -1 month)
group by 1,2
order by 3 desc
limit 5;

提醒自己：先算second 再换回hour，不要直接用hour

啥也没说

select distinct t100.usr_id from bilibili_t100 t100
left join bilibili_t20 t20 on t100.usr_id=t20.usr_id
where t20.usr_id is null

啥也没说

SELECT 
    v.video_id,
    v.title,
    COALESCE(AVG(TIMESTAMPDIFF(SECOND, w.start_time, w.end_time) / v.duration), 0) AS avg_completion_rate
FROM ks_video_inf v
LEFT JOIN ks_video_wat_log w ON v.video_id = w.video_id
GROUP BY v.video_id, v.title
ORDER BY avg_completion_rate DESC;

为什么这道题答案要使用inner join 呢？原题中要求‘如果视频没有被观看过，则显示完成率为0’，如果inner join 不应该就排除掉了没有被观看过的那些视频吗？

啥也没说

select 
video_id,author_id,title
from ks_video_inf
where current_date-date(release_time)<=7 and screen_type='p'
order by release_time desc;
这个代码为何不能通过？

能通过啊

with t as (
select
year(dt) as year,
cast(avg(replace(tmp_h, '℃', '')) as decimal(10,2)) as avg_tmp_h,
lag(round(avg(tmp_h), 2))over(order by year(dt)) as lastyear_avg_tmp_h
from weather_rcd_china
where year(dt) between 2011 and 2022 and city='shenzhen'
group by 1
)
select year,
avg_tmp_h,
case 
        when lastyear_avg_tmp_h is null then 'No' 
        when abs(avg_tmp_h - lastyear_avg_tmp_h) >= 1 then 'Yes' 
        else 'No' 
    end as significant_change
from t
order by 1

啥也没说

with active_users_before_august as (
    select 
        usr_id,
        date_format(login_time, '%Y-%m') as month
    from 
        user_login_log
    where 
        login_time < '2024-08-01'
    group by 
        usr_id, date_format(login_time, '%Y-%m')
    having 
        count(*) >= 10
),
active_users_after_august as (
    select 
        usr_id,
        date_format(login_time, '%Y-%m') as month
    from 
        user_login_log
    where 
        login_time >= '2024-08-01'
    group by 
        usr_id, date_format(login_time, '%Y-%m')
    having 
        count(*) >= 10
)
select 
    count(distinct au1.usr_id) as inactive_user_count
from 
    active_users_before_august au1
left join 
    active_users_after_august au2 
    on au1.usr_id = au2.usr_id
where 
    au2.usr_id is null;

啥也没说

select date_format(trx_time,'%Y-%m') as trx_mon,
count(1) as trx_cnt,
sum(trx_amt) as trx_amt
from cmb_usr_trx_rcd
where 
usr_id = 5201314520 
and date(trx_time) > '2022-11-01' and date(trx_time) <= '2024-12-31'
and ((truncate(trx_amt,0) rlike"88$|98$" and trx_amt>200) and hour(trx_time)  in (0,23,1,2) or upper(mch_nm) rlike "足疗|保健|养生|SPA")
group by 1
order by 1;
注意全角竖线和半角竖线！！！！

啥也没说

select user_id,
dayname(start_time)as day_of_week,
count(*) as listens_per_day
from listen_rcd
group by 1,2
order by 1,2

参考答案中join用户表的意义在于？

本题的输出结果是一样的，不加也行

select *
from subject_score
where if(chinese>=110,1,0)+if(math>=110,1,0)+if(english>=110,1,0) >=2

with t as(
select 
student_id,chinese,math,english,
case when chinese>=110 then 1 else 0 end as chinese_lag,
case when math>=110 then 1 else 0 end as math_lag,
case when english>=110 then 1 else 0 end as english_lag
from subject_score
)
select student_id,chinese,math,english
from t
where chinese_lag + math_lag + english_lag >= 2
order by 1;

啥也没说

select r.prd_id,m.prd_nm,
sum(case when i.gdr='M' then 1 else 0 end) as male_count,
sum(case when i.gdr='F' then 1 else 0 end) as female_count,
sum(case when i.gdr='M' then 1 else 0 end) + sum(case when i.gdr='F' then 1 else 0 end) as total_count
from tb_pg_act_rcd r
left join tb_prd_map m on r.prd_id=m.prd_id
left join tb_cst_bas_inf i on i.cust_uid=r.cust_uid
where r.if_buy=1
group by 1,2
order by r.prd_id

啥也没说

SELECT 
    COUNT(DISTINCT usr_id) AS active_users
FROM 
    user_login_log
WHERE 
    login_time >= ADDDATE(DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH), '%Y-%m-01 00:00:00')
    AND login_time < DATE_FORMAT(CURRENT_DATE, '%Y-%m-01 00:00:00');

啥也没说

SELECT 
    avg(TIMESTAMPDIFF(SECOND, call_time, grab_time)) AS avg_response_time_seconds
FROM 
    didi_order_rcd
WHERE 
    grab_time != '1970-01-01 00:00:00';

啥也没说

select date_format(purchase_time,'%Y%u') as week_number,
case when product_type='iPhone' then'iPhone'
     else 'Not iPhone' end as category,
count(distinct user_id) as user_count
from apple_pchs_rcd
group by 1,2
order by 1,2
标记一个 date_format 在周数上的应用

啥也没说

select 
'2022-10-03 17:20:20'as time_he_love_me,
datediff(current_date,'2022-10-03')as days_we_falling_love,
timestampdiff(hour,'2022-10-03 17:20:20',current_timestamp)as hours_we_falling_love,
datediff(min(trx_time),'2022-10-03') as days_he_fvck_else
from cmb_usr_trx_rcd
where
 usr_id = '5201314520' and mch_nm ='红玫瑰按摩保健休闲'
group by 
    mch_nm; 

啥也没说

WITH user_events AS (
    SELECT 
        live_id,
        enter_time AS event_time,
        1 AS user_change 
    FROM ks_live_t1
    UNION ALL
    SELECT 
        live_id,
        leave_time AS event_time,
        -1 AS user_change 
    FROM ks_live_t1
),
online_timeline AS (
    SELECT 
        live_id,
        event_time,
        SUM(user_change) OVER (
            PARTITION BY live_id 
            ORDER BY event_time, user_change DESC  
        ) AS current_online
    FROM user_events
)
SELECT 
    o.live_id,
    l.live_nm,
    MAX(o.current_online) AS max_online_users
FROM online_timeline o
LEFT JOIN ks_live_t2 l ON o.live_id = l.live_id
GROUP BY o.live_id, l.live_nm, l.live_type
ORDER BY max_online_users DESC;

为什么无法通过呢？

 MAX(o.current_online) AS max_online_users 这里也要分组取最大

with t1 as(
select t1.live_id,t2.live_nm,t2.live_type,
sum(timestampdiff(second,t1.enter_time,t1.leave_time)) as total_duration,
count(distinct t1.usr_id) as total_users,
sum(timestampdiff(second,t1.enter_time,t1.leave_time)) /count(distinct t1.usr_id) as avg_duration
from ks_live_t1 t1
left join ks_live_t2 t2 on t1.live_id=t2.live_id
where t1.enter_time between '2021-09-12 23:00:00' and '2021-09-13 03:59:59' 
group by 1,2,3
order by 1
),
t2 as (
select live_id,live_nm,live_type,total_duration,total_users,avg_duration,
row_number()over(partition by live_type order by avg_duration desc) as ranking
from t1
)
SELECT live_id,live_nm,live_type,total_duration,total_users,avg_duration
FROM t2 
WHERE ranking = 1
ORDER BY live_id;

啥也没说

select r.cust_uid,r1.cust_uid as cust_uid_1,r.mch_nm 
from mt_trx_rcd1 r
left join mt_trx_rcd1 r1 on r.mch_nm=r1.mch_nm 
where r.cust_uid='MT10000' and r.mch_nm='兰州李晓明拉面馆' and r.cust_uid!=r1.cust_uid
group by 1,2
order by 2

啥也没说

select i.gdr,
 count(distinct r.snd_usr_id) as sender_count,
 count(*) as total_send_count,
 count(distinct r.rcv_usr_id) as total_distinct_receiver_count,
 round(sum(r.pkt_amt)/count(distinct r.snd_usr_id),2) as avg_amount,
 round((sum(case when r.rcv_datetime!='1900-01-01 00:00:00' then 1 else 0 end)/count(*))*100,2) as receive_rate
 from tx_red_pkt_rcd r 
 left join tx_usr_bas_info i on r.snd_usr_id=i.usr_id
 where date(r.snd_datetime)='2021-02-13' and i.gdr is not null
 group by 1;

这里的发送给多少人不应该是 所有人数去重吗？参考答案的意思似乎是如果有多人同时给一个人发送，那么被发送的那个人可以计算多次？

你的理解是正确的。 已修改答案。
恭喜你彻底搞清楚了 count(1)\count(distinct )

with t1 as(
select fav.mch_id, count(distinct cust_uid) as fav_user_count
from xhs_fav_rcd fav
group by mch_id
),
t2 as(
select fav.mch_id,
count(distinct fav.cust_uid)as conv_user_count
from xhs_fav_rcd fav
left join xhs_pchs_rcd pch on fav.mch_id=pch.mch_id and fav.cust_uid=pch.cust_uid and fav.fav_tm<pch.pchs_tm
where pch.mch_id is not null
group by 1     
)

select t1.mch_id,t1.fav_user_count,
COALESCE(t2.conv_user_count, 0) as conv_user_count ,
COALESCE(ROUND((cast(t2.conv_user_count as float) / t1.fav_user_count)*100,2), 0) as conversion_rate
from t1
left join t2 on t1.mch_id=t2.mch_id
order by 4 desc
注意最后要处理小数点的问题

啥也没说

select 
'2022-10-03 17:20:20'as time_he_love_me,
datediff(current_date,'2022-10-03')as days_we_falling_love,
timestampdiff(hour,'2022-10-03',current_timestamp)as hours_we_falling_love,
datediff(min(trx_time),'2022-10-03') as days_he_fvck_else
from cmb_usr_trx_rcd
where
 usr_id = '5201314520' and mch_nm ='红玫瑰按摩保健休闲'
group by 
    mch_nm;
这为什么过不了呢

timestampdiff(hour,'2022-10-03',current_timestamp)as hours_we_falling_love，被减数要精确到小时。
蝶衣说过，说好的一辈子，少一天、一小时都不算。

select
year(list_date) as Y,
count(distinct ts_code)as cnt
from stock_info
where year(list_date) between 2021 and 2024 and name rlike '.*(医|药|生物).*'
group by 1
order by 1
答案包括2020年的，错了吧

已调整。

select ts_code,symbol,name,area,industry,list_date from
(
select 
*,
row_number()over(partition by industry order by list_date) as ranking
from stock_info
)t
where t.ranking=1
order by t.list_date;
为什么这个代码无法通过呢？

1、数据纰漏。有些股票的industry为空，你的可以找出来，正确答案是非空的
2、统计下你找出答案的行数，和正确答案找出的行数，看是谁多了或者谁少了

这个答案为何无法通过？
select *
from cmb_usr_trx_rcd
where usr_id=5201314520 and date(trx_time) between '2024-09-01' and '2024-09-30' and (format(trx_time,'%H-%i-%s') >='22:00:00' or format(trx_time,'%H-%i-%s')<='05:00:00' )
order by trx_time

原答案中的hour(trx_time) between 0 and 5 会包含05:59:59 不符合题意吧？

1、5点多的，只要不超过6点，都应被算进去；
2、你的代码细节有问题，详见下面
select *
from cmb_usr_trx_rcd
where usr_id=5201314520 and date(trx_time) between '2024-09-01' and '2024-09-30' and (date_format(trx_time,'%H:%i:%s') >='22:00:00' or date_format(trx_time,'%H:%i:%s')<='06:00:00' )
order by trx_time

select student_id
from scores
group by student_id
having sum(case when score is null then 1 else 0 end)=0 
order by student_id

手动点赞。你跟下面的米斯兰迪同学， 都是好样的，学以致用，举一反三！

WITH ranked_scores AS (
  SELECT 
    s.student_id,
    s.name,
    sc.score,
    ROW_NUMBER() OVER (ORDER BY sc.score DESC) AS rnk
  FROM students s
  JOIN scores sc ON s.student_id = sc.student_id
  WHERE s.grade_code = 'S1' AND sc.subject = '物理'
)
SELECT student_id, name, score, rnk
FROM ranked_scores
WHERE rnk <= 10
ORDER BY rnk,student_id
为什么不加入 PARTITION BY s.grade_code 代码会显示错误呢？理论上where先执行，然后再执行窗口函数，where后就已经得到了S1的物理成绩了，按理说，加与不加得到的结果应该是一致的呀？

1、跟你说的执行顺序无关；
2、评论区里所有人的问题都指向一类错误：排序字段未枚举全的时候，排名具有随机性。你看下你的答案，物理满分的同学有5个，黄志强、吴勇、柯玉珍、李秀云、赵龙。网站列出的正确答案，也是这5个人，只是名次不同。
3、理解了排名的随机性，本题就算学到了。