select
c2.mch_typ,
c1.mch_nm,
count(c1.mch_nm) as trx_cnt,
sum(c1.trx_amt) as trx_amt
from cmb_usr_trx_rcd c1
left join cmb_mch_typ c2 on c1.mch_nm = c2.mch_nm
where c1.usr_id = 5201314520 and year(c1.trx_time) = 2024
group by 1,2
having c2.mch_typ is null
order by trx_cnt desc;

select
 c2.mch_typ,
 count(c1.mch_nm) as trx_cnt,
 sum(c1.trx_amt) as trx_amt
from cmb_usr_trx_rcd c1
left join cmb_mch_typ c2 on c1.mch_nm = c2.mch_nm
where c1.usr_id = 5201314520 and year(c1.trx_time) = 2024
group by 1
order by 2 desc;

select
sin.singer_id,
sin.singer_name,
alb.album_id,
alb.album_name,
count(lis.song_id) as play_count
from singer_info sin
inner join album_info alb on sin.singer_id = alb.singer_id
left join song_info son on son.album_id = alb.album_id
left join listen_rcd lis on son.song_id = lis.song_id
group bysin.singer_id,
sin.singer_name,
alb.album_id,
alb.album_name
having play_count = 0;

select
date(trx_time) as trx_date,
max(trx_amt) as max_trx_amt,
min(trx_amt) as min_trx_amt,
avg(trx_amt) as avg_trx_amt,
sum(trx_amt) as total_trx_amt
from cmb_usr_trx_rcd
where date_format(trx_time, "%Y-%m") = "2024-09" and mch_nm = "红玫瑰按摩保健休闲"
group by 1
order by 1;

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;