select 
'按摩、保健、休闲、养生、SPA、会所' as reg_rules,
count(*) as mch_cnt
from cmb_usr_trx_rcd 
where mch_nm rlike "按摩|保健|休闲|养生|SPA|会所"
union 
select 
'按摩保健休闲' as reg_rules,
count(*) as mch_cnt
from cmb_usr_trx_rcd 
where mch_nm rlike "按摩保健休闲"

select usr_id,min(trx_time) as first_time,mch_nm
from cmb_usr_trx_rcd 
where usr_id = 5201314520
and mch_nm = "红玫瑰按摩保健休闲"
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-01-01" and "2024-6-30"
and mch_nm = "红玫瑰按摩保健休闲"
group by trx_amt
order by total_trx_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 trx_time between "2023-01-01" and "2024-6-30"
and mch_nm = "红玫瑰按摩保健休闲"
group by trx_amt
order by total_trx_cnt desc
limit 5;

select trx_amt,count(1) as trd_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 trd_cnt desc
limit 5;

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

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 month(trx_time) = 9 
and year(trx_time) = 2024
and mch_nm = "红玫瑰按摩保健休闲" 
group by date(trx_time)
order by date(trx_time)

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 month(trx_time) = 9 
and mch_nm = "红玫瑰按摩保健休闲" 
group by date(trx_time)
order by date(trx_time)

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