SELECT 
    user_id,
    DAYNAME(start_time) AS day_of_week,
    COUNT(*) AS listens_per_day
FROM 
    listen_rcd lr 
GROUP BY 
    user_id, day_of_week
ORDER BY 
    user_id ASC, day_of_week ASC;

SELECT 
   *
FROM 
    listen_rcd
WHERE 
    date(start_time) BETWEEN '2023-01-01' AND '2023-12-31' 
    and song_id =13
order by start_time

SELECT 
    distinct snd_usr_id
FROM 
    tx_red_pkt_rcd r
WHERE 
 r.snd_usr_id in(
 select snd_usr_id
 from tx_red_pkt_rcd
 where pkt_amt in (520,200)
 group by snd_usr_id
 having count(casewhen pkt_amt in (520,200) then 1 end )>=5)
order by snd_usr_id;

SELECT 
    distinct snd_usr_id
FROM 
    tx_red_pkt_rcd r
WHERE 
    r.snd_usr_id IN (
        SELECT 
            snd_usr_id
        FROM 
            tx_red_pkt_rcd
        WHERE 
            pkt_amt IN (200, 520)
        GROUP BY 
            snd_usr_id
        HAVING 
            COUNT(CASE WHEN pkt_amt IN (520,200) THEN 1 END) >=5
    )
ORDER BY 
    r.snd_usr_id

select student_id
from subject_score
where chinese+english+math>=300

select 
    *
from subject_score
where 
(chinese >=110 and english>=110)
or
(chinese >=110 and math>=110)
or 
(english>=110 and math>=110)
or
(english>=110 and math>=110 and chinese>=110)
order by student_id

select *
from cmb_usr_trx_rcd 
where 
(
  date(trx_time) between '2024-06-08' and '2024-06-10'
or date(trx_time) between '2024-09-15' and '2024-09-17'
  )
and usr_id=5201314520
order by trx_time

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) AS total_score
FROM scores
WHERE subject IN ('语文', '数学', '英语') AND exam_date = '2024-06-30'
GROUP BY student_id
HAVING SUM(score) > 330;

SELECT subject
FROM teachers
GROUP BY subject
HAVING COUNT(teacher_id) >= 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 enter_date >= '2010-01-01' THEN '青年教师'
        WHEN enter_date < '2000-01-01' THEN '资深教师'
        ELSE '中年教师'
    END AS teacher_type,
    COUNT(*) AS teacher_count
FROM teachers
GROUP BY teacher_type;

SELECT
    CASE 
        WHEN gender = 'm' THEN '男'
        WHEN gender = 'f' THEN '女'
    END AS gender_text,
    COUNT(*) AS student_count
FROM students
GROUP BY gender;

SELECT 
    student_id,
    MAX(score) AS max_score, 
    MIN(score) AS min_score, 
    AVG(score) AS avg_score
FROM scores
WHERE student_id IN (460093, 735011) AND subject = '数学'
GROUP BY student_id;

SELECT 
    MAX(score) AS max_score, 
    MIN(score) AS min_score, 
    AVG(score) AS avg_score
FROM scores
WHERE student_id = 735011 AND subject = '语文';

SELECT class_code, MIN(birth_date) AS min_birth_date
FROM students
GROUP BY class_code
ORDER BY class_code ASC;

SELECT subject, MAX(score) AS max_score, MIN(score) AS min_score
FROM scores
GROUP BY subject
ORDER BY subject ASC;

SELECT subject, AVG(score) AS average_score
FROM scores
WHERE exam_date = '2024-06-30'
GROUP BY subject
ORDER BY subject ASC;

SELECT class_code, COUNT(student_id) AS student_count
FROM students
GROUP BY class_code
ORDER BY student_count DESC;

SELECT name, subject, class_code, enter_date
FROM teachers
WHERE enter_date < '2010-01-01' AND qualification IS NULL
ORDER BY enter_date ASC;