select distinct user_id ,
 dayname(start_time) as day_of_week,
 count(*) as 	listens_per_day
from listen_rcd
group by user_id,day_of_week
order by user_id,day_of_week;

SELECT
    COUNT(DISTINCT CASE
        WHEN TIME(login_time) BETWEEN '07:30:00' AND '09:30:00'
             OR TIME(login_time) BETWEEN '18:30:00' AND '20:30:00' THEN usr_id
    END) AS commute,
    COUNT(DISTINCT CASE
        WHEN TIME(login_time) BETWEEN '11:30:00' AND '14:00:00' THEN usr_id
    END) AS lunch_break,
    COUNT(DISTINCT CASE
        WHEN TIME(login_time) BETWEEN '22:30:00' AND '23:59:59' THEN usr_id
        WHEN TIME(login_time) BETWEEN '00:00:00' AND '01:00:00' THEN usr_id
    END) AS bedtime
FROM
    user_login_log
WHERE
    login_time >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01')
    AND login_time < DATE_FORMAT(CURDATE(), '%Y-%m-01');

SELECT 
    COUNT(DISTINCT usr_id) AS active_users
FROM 
    user_login_log
WHERE 
   month(login_time)= month(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)) and year(login_time)=year(now());

select count(distinct usr_id) as active_users
from user_login_log 
wherelogin_time >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00')
    AND login_time < DATE_FORMAT(CURDATE(), '%Y-%m-01 00:00:00');

select count(distinct usr_id) as active_users
from user_login_log 
where month(login_time)=month(date_sub(current_date,interval 1 month));

select year(dt) as Y
    ,cast(avg(case when city='beijing' then tmp_h else null end) as decimal(4,2)) as '北京'
    ,cast(avg(case when city='shanghai' then tmp_h else null end) as decimal(4,2)) as 上海
    ,cast(avg(case when city='shenzhen' then tmp_h else null end) as decimal(4,2)) as 深圳
    ,cast(avg(case when city='guangzhou' then tmp_h else null end) as decimal(4,2)) as 广州
from
    weather_rcd_china
where 
    year(dt) between 2011 and 2022
group by 
    year(dt)

select 
 city , sum(case when con like '%雪%' then 1 else 0 end) as snowy_days
from weather_rcd_china 
where month(dt) in (12,1,2)
group by city
order by snowy_days desc;

select avg(timestampdiff(second,call_time,grab_time) ) as avg_response_time_seconds
from didi_order_rcd 
wheregrab_time != '1970-01-01 00:00:00';

select
    *
from
    cmb_usr_trx_rcd
where
    date(trx_time) 
    between '2024-09-01' and '2024-09-30' 
    and hour(trx_time) in (22,23,0,1,2,3,4,5)
    and usr_id = '5201314520' 
order by trx_time

select * from cmb_usr_trx_rcd where usr_id=5201314520 and year(trx_time)=2024 and month(trx_time)=9 and hour(trx_time) in (1,2,3,4,5);

select * from cmb_usr_trx_rcd where usr_id=5201314520 and date(trx_time) between '2024-09-01' and '2024-09-30';

WITH student_total_scores AS (
    SELECT 
        s.class_code, 
        s.student_id, 
        SUM(sc.score) AS total_score
    FROM 
        students s
    JOIN 
        scores sc ON s.student_id = sc.student_id
    WHERE 
        sc.exam_date = '2024-06-30'
    GROUP BY 
        s.class_code, s.student_id
),
class_averages AS (
    SELECT 
        sts.class_code, 
        COUNT(DISTINCT sts.student_id) AS num_students, 
        SUM(sts.total_score) AS class_total_score
    FROM 
        student_total_scores sts
    GROUP BY 
        sts.class_code
)
SELECT 
    ca.class_code, 
    ca.num_students, 
    ROUND(ca.class_total_score / ca.num_students, 2) AS avg_score, 
    t.name AS head_teacher_name
FROM 
    class_averages ca
JOIN 
    teachers t ON t.class_code LIKE CONCAT('%', ca.class_code, '%')
WHERE 
    t.head_teacher = ca.class_code
ORDER BY 
    avg_score DESC

WITH total_students AS (
    SELECT 
        t.name AS teacher_name, 
        COUNT(DISTINCT s.student_id) AS total_students
    FROM 
        teachers t
    JOIN 
        students s ON t.class_code LIKE CONCAT('%', s.class_code, '%')
    WHERE 
        t.subject = '化学'
    GROUP BY 
        t.name
),
failed_students AS (
    SELECT 
        t.name AS teacher_name, 
        COUNT(DISTINCT s.student_id) AS failed_students
    FROM 
        teachers t
    JOIN 
        students s ON t.class_code LIKE CONCAT('%', s.class_code, '%')
    JOIN 
        scores sc ON s.student_id = sc.student_id
    WHERE 
        t.subject = '化学'
        AND sc.subject = '化学'
        AND sc.score < 60
    GROUP BY 
        t.name
)
SELECT 
    ts.teacher_name, 
    ts.total_students, 
    fs.failed_students, 
    ROUND((fs.failed_students / ts.total_students) * 100, 2) AS failure_rate
FROM 
    total_students ts
JOIN 
    failed_students fs ON ts.teacher_name = fs.teacher_name
ORDER BY 
    failure_rate DESC;

SELECT 
    s.class_code, 
    COUNT(distinct s.student_id) AS num_students_90_plus, 
    ROUND(AVG(sc.score), 2) AS avg_score_90_plus, 
    t.name AS physics_teacher
FROM 
    students s
JOIN 
    scores sc ON s.student_id = sc.student_id
JOIN 
    teachers t ON t.class_code LIKE CONCAT('%', s.class_code, '%')
WHERE 
    sc.subject = '物理' 
    AND sc.score >= 90
    AND t.subject = '物理'
GROUP BY 
    s.class_code, t.name
ORDER BY 
    avg_score_90_plus DESC;

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

select * 
from scores 
where score>=90 and subject in ('历史','政治','地理')
order by score desc ,student_id,subject;

select 
    * 
from 
    scores
where subject='英语' 
and score between 100 and 110
and exam_date='2024-06-30'
order by score desc

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

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

select student_id 
from scores 
group by student_id
having sum(case when score is null then 1 else 0 end)>5
order by student_id