select 
case when s2.score >= 90 and s2.score < 110 then "[90,110)"
 when s2.score >= 60 and s2.score < 90 then "[60,90)"
 when s2.score >= 110 and s2.score <= 120 then "[110,120]"
 when s2.score >= 0 and s2.score < 60 then "[0,60)"
 else null
 end as score_range,
count(s1.student_id) as num_students
from students s1
inner join scores s2
 on s1.student_id = s2.student_id
where s2.exam_date = "2024-06-30" and s2.subject = "数学"
group by score_range
order by score_range desc;

select 
t1.prd_id,
t2.prd_nm, 
sum(case when t1.if_snd = 1 then 1 else 0 end) as exposure_count
from tb_pg_act_rcd t1
inner join tb_prd_map t2
on t1.prd_id = t2.prd_id
group by t1.prd_id,
t2.prd_nm
order by 3 desc
limit 1;

select 
t1.prd_id,
t2.prd_nm, 
sum(case when t1.if_snd = 1 then 1 else 0 end) as exposure_count
from tb_pg_act_rcd t1
inner join tb_prd_map t2
on t1.prd_id = t2.prd_id
group by t1.prd_id,
t2.prd_nm
order by 3 desc;

select
a1.*
from didi_sht_rcd a1
inner join loc_nm_ctg a2
 on a1.end_loc = a2.loc_nm
where a2.loc_ctg = "餐饮"
order by a1.start_tm;

select
*
from didi_sht_rcd a1
inner join loc_nm_ctg a2
 on a1.end_loc = a2.loc_nm
where a2.loc_ctg = "餐饮"
order by a1.start_tm;

select
k1.live_id,
k2.live_nm,
count(*) as enter_cnt
from ks_live_t1 k1
inner join ks_live_t2 k2
 on k1.live_id = k2.live_id
where date_format(k1.enter_time , "%Y-%m-%d %H") = "2021-09-12 23"
group by k1.live_id,k2.live_nm
order by 3 desc
limit 5;

select
k1.live_id,
k2.live_nm,
count(*) as enter_cnt
from ks_live_t1 k1
inner join ks_live_t2 k2
 on k1.live_id = k2.live_id
group by k1.live_id , k2.live_nm
order by 3 desc
limit 5;

select * from ks_live_t1 limit 5;

select
singer_name,
char_length(singer_name) as len
fromsinger_info;

select
upper(singer_name) as uppered_name
from singer_info;

select
dt,
tmp_h,
tmp_l,
con
 from weather_rcd_china
 where con like "%雪%" and city = "beijing";

select
city,
sum(case when con like "%多云%" then 1 else 0 end) as cloudy_days,
concat(cast(sum(case when con like "%多云%" then 1 else 0 end)/count(1) * 100 as decimal(10,2)) , "%")as p
from weather_rcd_china
where year(dt) = 2021
group by city
order by p desc;

select
left(name , 1) as surname,
count(left(name , 1)) as cnt
from students
group by surname
order by cnt desc
limit 5;

select 
city,
sum(case when con like "%多云%" then 1 else 0 end) as cloudy_days,
concat(cast(sum(case when con like "%多云%" then 1 else 0 end)/count(*) * 100 as decimal(4,2)) , "%") as p
from weather_rcd_china
where year(dt) = 2021
group by city
order by p desc;

select * from hand_permutations
where left(card1 , 1 ) = left(card2 , 1)
order by id;

select
*
from hand_permutations
where right(card1 , 1) = right(card2 , 1)
and
(card1 like "A%" or card2 like "A%")
order by id;

select
case when mch_nm like "%按摩保健休闲%" then "按摩保健休闲" 
when lower(mch_nm) rlike "(按摩|保健|休闲|养生|spa|会所)" then "按摩、保健、休闲、养生、SPA、会所" 
 else null
 end as reg_rules,
count(distinct mch_nm) as mch_cnt
from cmb_usr_trx_rcd
group by 1
having reg_rules is not null
order by mch_cnt desc;

select
case when lower(mch_nm) rlike "(按摩|保健|休闲|养生|SPA|会所)" then "按摩、保健、休闲、养生、SPA、会所" 
 when mch_nm like "%按摩保健休闲%" then "按摩保健休闲"
 else null
 end as reg_rules,
count(distinct mch_nm) as mch_cnt
from cmb_usr_trx_rcd
group by 1
having reg_rules is not null
order by mch_cnt desc;

select
count(distinct case when mch_nm like "%按摩保健休闲%" then 1 else null end) as mch_cnt
from cmb_usr_trx_rcd;

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