select
lr.user_id,
dayname(lr.start_time) d_o_f,
count(1) listens_per_day
from listen_rcd lr
left join qqmusic_user_info qui
on lr.user_id = qui.user_id
group by lr.user_id,d_o_f
order by lr.user_id,d_o_f

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

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

select 
t2.prd_id,
t2.prd_nm,
sum(t1.if_snd) as sum_snd
from tb_pg_act_rcd t1
left join tb_prd_map t2
on t1.prd_id = t2.prd_id
group by t2.prd_id,t2.prd_nm
order by sum_snd desc
limit 1;

select t1.*
from didi_sht_rcd t1
left join loc_nm_ctg t2
on t1.end_loc = t2.loc_nm
where t2.loc_ctg = '餐饮'
order by t1.start_tm;

select t1.*
from didi_sht_rcd t1
left join loc_nm_ctg t2
on t1.end_loc = t2.loc_nm
where t2.loc_ctg = '餐饮'
order by t1.start_tm
limit 5;

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

select trx_amt,
count(*) as total_trx_cnt,
count(distinct usr_id) as unique_usr_cnt,
count(*)/count(distinct usr_id) as avg_trx_per_user
from cmb_usr_trx_rcd
where date(trx_time) between '2023-1-1' and '2024-6-30'
and mch_nm = '红玫瑰按摩保健休闲'
group by trx_amt
order by avg_trx_per_user desc
limit 5;

select trx_amt,
count(1)as trx_cnt
from cmb_usr_trx_rcd
where mch_nm = '红玫瑰按摩保健休闲'
and date(trx_time) between '2024-01-01' and '2024-07-31'
group by trx_amt
order by trx_cnt desc
limit 5;

select sum(trx_amt) as trx_amt,
count(1)as trx_cnt
from cmb_usr_trx_rcd
where mch_nm = '红玫瑰按摩保健休闲'
and date(trx_time) between '2024-01-01' and '2024-07-31'
group by trx_amt
order by trx_cnt desc
limit 5;

select date(trx_time) as trx_date,
max(trx_amt) max_trx_amt,
min(trx_amt) min_trx_amt,
avg(trx_amt) avg_trx_amt,
sum(trx_amt) total_trx_amt
from cmb_usr_trx_rcd
where mch_nm = '红玫瑰按摩保健休闲'
and date(trx_time) between '2024-09-01' and '2024-09-30'
group by date(trx_time)
order by trx_date

select date(trx_time) as trx_date,
max(trx_time) max_trx_amt,
min(trx_time) min_trx_amt,
avg(mch_nm) avg_trx_amt,
sum(trx_time) total_trx_amt
from cmb_usr_trx_rcd
where mch_nm = '红玫瑰按摩保健休闲'
and date(trx_time) between '2024-09-01' and '2024-09-30'
group by date(trx_time)
order by trx_date

select mch_nm,sum(trx_amt) as sum_trx_amt 
from cmb_usr_trx_rcd
where year(trx_time) = '2024' 
and usr_id = 5201314520
group by mch_nm
order by sum_trx_amt desc

select * from cmb_usr_trx_rcd
where date(trx_time) between '2024-09-01' and '2024-09-30'
and (hour(trx_time) >=22 or hour(trx_time) between 0 and 5)
and usr_id = 5201314520
order by trx_time

select * from cmb_usr_trx_rcd
where date(trx_time) between '2024-09-01' and '2024-09-30'
and hour(trx_time) between 1 and 5
and usr_id = 5201314520
order by trx_time

select * 
from cmb_usr_trx_rcd
where date(trx_time) between '2024-09-01'and '2024-09-30'
and usr_id = 5201314520
order by trx_time

select * 
from students
where class_code in ('C219','C220','C221')
and gender = 'f'
order by student_id

select * 
from cmb_usr_trx_rcd 
where date(trx_time) between '2024-09-01' and '2024-09-30'
and (hour(trx_time) >= 22
or hour(trx_time) between 0 and 5)
and usr_id = 5201314520
order by trx_time

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

select * 
from cmb_usr_trx_rcd 
where date(trx_time) between '2024-09-01' and '2024-09-30'
and hour(trx_time) between 1 and 5
and usr_id ='5201314520'
order by trx_time