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

select ts_code,open_price,close_price
from daily_stock_prices
where pct_change>1 and trade_date='2023-10-09'

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

select a.user_id,
dayname(start_time) as day_of_week,
count(*) as listens_per_day
from listen_rcd a
left join song_info b on a.song_id=b.song_id
group by user_id,day_of_week
order by 1,2

select
case 
when score<60 then '[0,60)'
when score>=60 and score<90 then '[60,90)'
when score>=90 and score<110 then '[90,110)'
when score>=110 and score<=120 then '[110,120]'
end as score_range,
count(*) as num_students
from scores a 
left join students b on a.student_id=b.student_id
where exam_date='2024-06-30' and subject='数学'
group by score_range
order by 1 desc

select mch_nm,sum(trx_amt) as mch_total_amt
from cmb_usr_trx_rcd
group by mch_nm
having sum(trx_amt)>(select avg(trx_amt)*10
from cmb_usr_trx_rcd)
order by 2 desc

select *
from cmb_usr_trx_rcd
where usr_id not in (select distinct usr_id
from cmb_usr_trx_rcd
where mch_nm='肯德基')
order by usr_id,trx_time

select usr_id,mch_nm,trx_time,trx_amt
from cmb_usr_trx_rcd
where trx_amt>(select avg(trx_amt) from cmb_usr_trx_rcd) 
order by 4 desc

select usr_id,mch_nm,trx_time,trx_amt,
(select avg(trx_amt) from cmb_usr_trx_rcd)as avg_trx_amt
from cmb_usr_trx_rcd
where usr_id=5201314520
order by 3 desc

select usr_id,
sum(trx_amt) as total_amt,
(select avg(trx_amt) from cmb_usr_trx_rcd) as platform_avg_amt
from cmb_usr_trx_rcd
group by 1
order by 2 desc

select
length(singer_name) ,
count(singer_id) 
from singer_info
 group by 1

select singer_name,
char_length(singer_name) as len
from singer_info

select
upper(singer_name) as uppered_name
from singer_info

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

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

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

select 
count(distinct mch_nm) as mch_cnt
from cmb_usr_trx_rcd
where mch_nm like "%按摩保健休闲%"
group by mch_nm

select*
from cmb_usr_trx_rcd
 where usr_id=5201314520 and 
(date(trx_time) between '2024-06-08' and '2024-06-10' or date(trx_time) between '2024-09-15' and '2024-09-17')
order by 3

select*
from cmb_usr_trx_rcd
 where usr_id=5201314520 and 
date(trx_time) between '2024-06-08' and '2024-06-10' or date(trx_time) between '2024-09-15' and '2024-09-17'
order by 3

select city,
sum(case 
when con like '%雪%' then 1
 else 0
 end) as snowy_days
from weather_rcd_china
where month(dt) in (12,1,2) 
group by city
order by 2 desc