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 mch_nm = '红玫瑰按摩保健休闲'
and (year(trx_time) = 2023
 or
(year(trx_time) = 2024 
 and
 month(trx_time) between 1 and 6)
 )
group by
    trx_amt
order by
    avg_trx_per_user 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 mch_nm = '红玫瑰按摩保健休闲'
and (year(trx_time) = 2023
 or
(year(trx_time) = 2024 
 and
 month(trx_time) between 1 and 6)
 )
group by
    trx_amt
order by
    avg_trx_per_user desc

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

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

select trx_amt
,count(1) as total_trx_cnt
,count(distinct usr_id) as unique_usr_cnt
,count(1) / count(distinct usr_id) as avg_trx_per_user
from cmb_usr_trx_rcd
where mch_nm = '红玫瑰按摩保健休闲'
and (year(trx_time) = 2023)
or(year(trx_time) = 2024 
 and
 month(trx_time) between 1 and 6)
group by
    trx_amt
order by
    avg_trx_per_user desc

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

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

select 
	di.cust_uid,
di.start_loc,
di.end_loc,
di.start_tm,
di.car_cls
from
	didi_sht_rcd di
join
	loc_nm_ctg l
on di.end_loc =l.loc_nm
where l.loc_ctg ='餐饮'
order by di.start_tm

select student_id
,chinese
,math
,english
from subject_score
where chinese > 100
or math > 100
or english > 100
order by chinese

select student_id
,chinese
,math
,english
from subject_score
where chinese >= 100
or math >= 100
or english >= 100
order by chinese

select subject
from teachers
group by subject
having count(name) >= 3

select student_id,
max(score) max_score,
min(score) min_score,
avg(score) avg_score
from scores
group by student_id
having min(score) >= 80
order by student_id

select 
	case gender
	when 'm' then '男'
when 'f' then '女'
end as gender_text, 
count(*) as student_count
from students
group by gender_text

select subject
,avg(score)
from scores
where exam_date = '2024-06-30'
group by subject
order by subject

select class_code
,count(name) student_count
from students
group by class_code
order by student_count desc

select name
,subject
,class_code
,qualification
from teachers
where qualification = 'Senior'
and subject = '数学'
order by name

select student_id
,name
,birth_date
from students
where year(birth_date) = 2009
and gender = 'f'
order by birth_date

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

WITH ranked_scores AS (
    SELECT 
        s.student_id, 
        s.name, 
        sc.score,
        RANK() OVER (ORDER BY sc.score DESC) AS ranking
    FROM 
        students s
    JOIN 
        scores sc ON s.student_id = sc.student_id
    WHERE 
        s.grade_code = 'S1' 
        AND sc.subject = '物理'
)
SELECT 
    student_id, 
    name, 
    score, 
    ranking
FROM 
    ranked_scores
WHERE 
    ranking <= 10
ORDER BY 
    ranking;

select s1.student_id
,s1.name
,s2.score
,rank()over(partition by subject order by score desc) rnk
from students s1
join scores s2
on s1.student_id=s2.student_id
where s1.grade_code = "S1"
and s2.subject = "物理"
limit 12