select user_id,  DAYNAME(start_time)day_of_week,count(if_finished)listens_per_day
from listen_rcd
group by user_id,day_of_week
order by user_id,day_of_week

select user_id,  DAYNAME(start_time)day_of_week,sum(if_finished)listens_per_day
from listen_rcd
group by user_id,day_of_week
order by user_id,day_of_week

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 
    students s
JOIN 
    scores sc ON s.student_id = sc.student_id
WHERE 
    sc.subject = '数学' 
    AND sc.exam_date = '2024-06-30'
GROUP BY 
    score_range
ORDER BY 
    score_range DESC;

select r.prd_id,m.prd_nm,sum(if_snd)exposure_count
from tb_pg_act_rcd r
join tb_prd_map m using(prd_id)
group by prd_id,prd_nm
order by exposure_count desc
limit 1

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

selectt1.live_id	,t2.live_nm, count(distinct usr_id)enter_cnt
from ks_live_t1 t1
join ks_live_t2t2 using(live_id)
where time(enter_time)<="23:59:59" and time(enter_time)>="23:00:00"
group by live_nm,t1.live_id
order by enter_cnt desc
limit 5

selectt1.live_id	,t2.live_nm, count(distinct usr_id)enter_cnt
from ks_live_t1 t1
join ks_live_t2t2 using(live_id)
where time(enter_time)<"23:59:59" and time(enter_time)>"23:00:00"
group by live_nm,t1.live_id
order by enter_cnt desc
limit 5

select*
from scores
where 
    (
      (subject = '历史' and score >= 90)
    or (subject = '地理' and score >= 90)
    or (subject = '政治' and score >= 90)
       )
    and exam_date='2024-06-30'
order by score desc ,student_id,subject

select*
from scores
where subject="英语" and( score between 100 and 110) and exam_date="2024-06-30"

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

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

select exam_date, MAX(CASE WHEN subject = '语文' THEN score ELSE NULL END) AS chinese_score,
    MAX(CASE WHEN subject = '数学' THEN score ELSE NULL END) AS math_score,
    MAX(CASE WHEN subject = '英语' THEN score ELSE NULL END) AS english_score
from scores
WHERE student_id = 460093 AND subject IN ('语文', '数学', '英语')
group by exam_date
order by exam_date

select student_id,	sum(score)total_score
from scores
where exam_date='2024-06-30'and subject IN ('语文', '数学', '英语')
group by student_id
having sum(score)>330

select student_id,	sum(score)total_score
from scores
where exam_date='2024-06-30'
group by student_id
having sum(score)>330

select subject 
from teachers
 group by subject
 having count(*)>=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 when year(enter_date)>=2010 then "青年教师"
 when year(enter_date)<2000 then "资深教师" 
 else "中年教师"end )as teacher_type,	count(*)teacher_count
from teachers
group by teacher_type

select if(gender="m","男",'女')as gender_text ,count(1)student_count
from students
group by gender

select student_id,max(score)max_score,min(score)	min_score,avg(score)	avg_score
from scores
where (student_id=460093 or student_id=735011)and subject='数学'
group by student_id

select max(score)max_score,min(score)	min_score,avg(score)	avg_score
from scores
where (student_id=460093 or student_id=735011)and subject='数学'
group by student_id