select 
	user_id, 
	dayname(start_time) as day_of_week, 
count(if_finished) as listen_per_day
from listen_rcd 
group by
	user_id,dayname(start_time)
order by
	user_id,day_of_week

select 
	"[110, 120]" as score_range,
count(*) as num_students
from 
	scores
where
	date_format(exam_date, "%Y-%m-%d") = "2024-06-30"
and subject = "数学"
and score >= 110
union
select 
	"[90, 110)" as score_range,
count(*) as num_students
from 
	scores
where
	date_format(exam_date, "%Y-%m-%d") = "2024-06-30"
and subject = "数学"
and score >= 90 and score <110
union
select 
	"[60, 90)" as score_range,
count(*) as num_students
from 
	scores
where
	date_format(exam_date, "%Y-%m-%d") = "2024-06-30"
and subject = "数学"
and score >= 60 and score <90
union
select 
	"[0, 60)" as score_range,
count(*) as num_students
from 
	scores
where
	date_format(exam_date, "%Y-%m-%d") = "2024-06-30"
and subject = "数学"
and score <60

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

select t2.live_id,t2.live_nm, count(distinct t1.usr_id) as enter_cnt
from ks_live_t1 as t1
join ks_live_t2 as t2 using(live_id)
where t1.leave_time >= "2021-09-12 23:00:00" and t1.leave_time < "2021-09-13 00:00:00"
group by t2.live_id,t2.live_nm
order by enter_cnt desc
limit 5;

select t2.live_id,t2.live_nm, count(t1.live_id) as enter_cnt
from ks_live_t1 as t1
join ks_live_t2 as t2 using(live_id)
where t1.leave_time >= "2021-09-12 23:00:00" and t1.leave_time < "2021-09-13 00:00:00"
group by t2.live_id,t2.live_nm
order by enter_cnt desc
limit 5;

select t2.live_id,t2.live_nm, count(t1.live_id) as enter_cnt
from ks_live_t1 as t1
join ks_live_t2 as t2 using(live_id)
where t1.leave_time >= "2021-09-12 23:00:00" and t1.leave_time < "2021-09-13 00:00:00"
group by t2.live_id,t2.live_nm
order by enter_cnt desc

select 
	zy.*,
ny.*
from 
	(select 
 	mch_nm as asshole_tried, 
 	count(mch_nm) as trx_cnt
 from cmb_usr_trx_rcd
 where 
 	usr_id = 5201314520 
 	and year(trx_time) between 2023 and 2024
 group by mch_nm
 having count(mch_nm) >= 20
) as zy
left join 
	(select 
 	mch_nm as darling_tried
 from cmb_usr_trx_rcd
 where 
 	usr_id = 5211314521 
 	and year(trx_time) between 2023 and 2024
 group by mch_nm
) as ny
	on zy.asshole_tried = ny.darling_tried
order by
	zy.trx_cnt desc

select 
	a.*
from
	(select 
 	distinct mch_nm
from
	cmb_usr_trx_rcd
where
	year(trx_time) = 2024 and usr_id = 5201314520) as a
join
	(select 
 	distinct mch_nm
from
	cmb_usr_trx_rcd
where
	year(trx_time) = 2024 and usr_id = 5211314521) as b
	ON
	a.mch_nm = b.mch_nm
order by
	1 desc

select 
	distinct mb.mch_nm
from 
	cmb_usr_trx_rcd as zn
join
	cmb_usr_trx_rcd as mb ON zn.mch_nm = mb.mch_nm
where 
	year(zn.trx_time) = 2024 and
(zn.usr_id = 5201314520 and mb.usr_id = 5211314521)
order by 1 desc

select 
	distinct mb.mch_nm
from 
	cmb_usr_trx_rcd as zn
join
	cmb_usr_trx_rcd as mb ON zn.mch_nm = mb.mch_nm
where 
	year(zn.trx_time) = 2024 and
(zn.usr_id = 5201314520 and mb.usr_id = 5211314521)

select 
	distinct mb.mch_nm
from 
	cmb_usr_trx_rcd as zn
join
	cmb_usr_trx_rcd as mb ON zn.mch_nm = mb.mch_nm
where 
	year(zn.trx_time) = 2024 and
zn.usr_id = 5201314520 and mb.usr_id = 5211314521

select 
	distinct mb.mch_nm
from 
	cmb_usr_trx_rcd as zn
join
	cmb_usr_trx_rcd as mb ON zn.mch_nm = mb.mch_nm
where zn.usr_id = 5201314520 and mb.usr_id = 5211314521

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) from cmb_usr_trx_rcd)*10
order by 
	sum(trx_amt) desc

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

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

select * from cmb_usr_trx_rcd 
where 
	usr_id NOT IN 
(select distinct usr_id from cmb_usr_trx_rcd where mch_nm = "肯德基")

SELECT *
FROM cmb_usr_trx_rcd
where usr_id IN(
 SELECT DISTINCT usr_id 
FROM cmb_usr_trx_rcd 
WHERE mch_nm = '肯德基'
)
ORDER BY usr_id, trx_time;

select * 
from 
cmb_usr_trx_rcd 
where mch_nm = "肯德基"

select 
	* 
from
	cmb_usr_trx_rcd
where 
	trx_amt > (select avg(trx_amt) from cmb_usr_trx_rcd) 
order by
	trx_amt DESC

select 
	*,
(select avg(trx_amt) from cmb_usr_trx_rcd) 
from cmb_usr_trx_rcd
where usr_id = 5201314520