select 
user_id,
dayname(start_time) day_of_week,
count(1) listens_per_day
from listen_rcd
group by user_id,day_of_week
order by 1,2

select 
user_id,
dayname(start_time) day_of_week,
count(1) listens_per_day
from listen_rcd
group by user_id,day_of_week
order by 1,2
limit 5;

select 
case 
	WHEN score >= 110 THEN '[110, 120]'
        WHEN score >= 90 THEN '[90, 110)'
        WHEN score >= 60 THEN '[60, 90)'
        ELSE '[0, 60)'
end
score_range,
count(*) num_students
from scores
where exam_date = '2024-06-30' and subject = '数学'
group by score_range
order by 1 desc

select 
case 
	when score >= 110 then '[110, 120]'
when score >= 90 then '[90, 110）'
when score >= 60 then '[60, 90）'
else '[0, 60)'
end
score_range,
count(*) num_students
from scores
where exam_date = '2024-06-30' and subject = '数学'
group by score_range
order by 1 desc

select 
case 
	when score >= 110 then '[110,120]'
when score >= 90 then '[90,110）'
when score >= 60 then '[60,90）'
else '[0,60)'
end
score_range,
count(*) num_students
from scores
where exam_date = '2024-06-30' and subject = '数学'
group by score_range
order by 1 desc

select 
case 
	when score >= 110 then '[110,120]'
when score >= 90 then '[90，110）'
when score >= 60 then '[60，90）'
else '[0,60)'
end
score_range,
count(*) num_students
from scores
where exam_date = '2024-06-30' and subject = '数学'
group by score_range
order by 1 desc

select 
case 
	when score >= 110 then '[110,120]'
when score >= 90 then '[90，110）'
when score >= 60 then '[60，90）'
else '[0,60)'
end
score_range,
count(*) num_students
from scores
where exam_date = '2024-06-30' and subject = '数学'
group by score_range
order by 1

with math_rank as (
select
	student_id
,case when
	score>=110 then '[110, 120]'
when 
	score between 90 and 109 then '[90, 110)'
when
	score between 60 and 89 then '[60, 90)'
		else '[0, 60)'
end as score_range
from
	scores
where	
	exam_date='2024-06-30'
and subject='数学')
select
	score_range
,count(*) as num_students
from
	math_rank
group by
	score_range

select 
case 
	when score >= 110 then '[110,120]'
when score >= 90 then '[90，110）'
when score >= 60 then '[60，90）'
else '[0,60)'
end
score_range,
count(*) num_students
from scores
where exam_date = '2024-06-30' and subject = '数学'
group by score_range
order by 2

select 
case 
	when score >= 110 then '[110,120]'
when score >= 90 then '[90，110）'
when score >= 60 then '[60，90）'
else '[0,60)'
end
score_range,
count(*) num_students
from scores
where exam_date = '2024-06-30' and subject = '数学'
group by score_range
order by 2 desc

select 
case 
	when score >= 110 then '[110,120]'
when score >= 90 then '[90，110）'
when score >= 60 then '[60，90）'
else '[0,60)'
end
score_range,
count(*) num_students
from scores
where exam_date = '2024-06-30' and subject = '数学'
group by score_range

select 
case 
	when score >= 110 then '[110,120]'
when score >= 90 then '[90，110）'
when score >= 60 then '[60，90）'
else '[0,60)'
end
score_range,
count(*) num_students
from scores
where exam_date = '2024-06-30'
group by score_range

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

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

select
a.live_id live_id,a.live_nm live_nm,count(1) enter_cnt
from 
ks_live_t2 a
left join
ks_live_t1 b
on a.live_id = b.live_id
where b.enter_time < '2021-9-12 23:00:00' and b.leave_time> '2021-9-12 23:00:00'
group by live_id,live_nm
order by 3 desc

select 
a.mch_nmasshole_tried,
a.trx_cnt trx_cnt,
b.mch_nmdarling_tried
from 
(select mch_nm,count(1) trx_cnt from cmb_usr_trx_rcd 
 where trx_time rlike '2023|2024' and usr_id = 5201314520
 group by mch_nm 
 having trx_cnt >= 20) a
 left join 
 (select distinct mch_nm from cmb_usr_trx_rcd 
where trx_time rlike '2023|2024' and usr_id = 5211314521) b
 on a.mch_nm = b.mch_nm
 order by 2 desc

select 
a.mch_nmasshole_tried,
a.trx_cnt trx_cnt,
b.mch_nmdarling_tried
from 
(select mch_nm,count(1) trx_cnt from cmb_usr_trx_rcd 
 where trx_time rlike '2023|2024' and usr_id = 5201314520
 group by mch_nm 
 having trx_cnt >= 20) a
 left join 
 (select distinct mch_nm from cmb_usr_trx_rcd 
where year(trx_time) in (2023,2024) and usr_id = 5211314521) b
 on a.mch_nm = b.mch_nm
 order by 2 desc

select 
a.mch_nmasshole_tried,
a.trx_cnt trx_cnt,
b.mch_nmdarling_tried
from 
(select mch_nm,count(1) trx_cnt from cmb_usr_trx_rcd 
 where trx_time rlike '2023|2024' and usr_id = 5201314520
 group by mch_nm 
 having trx_cnt >= 20) a
 left join 
 (select distinct mch_nm from cmb_usr_trx_rcd 
where trx_time in (2023,2024) and usr_id = 5211314521) b
 on a.mch_nm = b.mch_nm
 order by 2 desc

select distinct a.* from 
(select mch_nm from cmb_usr_trx_rcd where usr_id = 5201314520 and left(trx_time,4)=2024) a
inner join
(select mch_nm from cmb_usr_trx_rcd where usr_id = 5211314521 and left(trx_time,4)=2024) b
on a.mch_nm = b.mch_nm
order by 1 desc

select distinct a.* from 
(select mch_nm from cmb_usr_trx_rcd where usr_id = 5201314520 and left(trx_time,4)) a
inner join
(select mch_nm from cmb_usr_trx_rcd where usr_id = 5211314521 and left(trx_time,4)) b
on a.mch_nm = b.mch_nm
order by 1 desc