with rank_wl_s1 as (
	select 
	s.student_id
	,s.name
	,sc.score
	,rank()over(ORDER BY sc.score DESC) AS ranking
	FROM students s
left 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 
    rank_wl_s1
WHERE 
    ranking <= 10
ORDER BY 
    ranking;

select
	u.user_id
	,dayname(start_time)AS day_of_week
	,COUNT(*) AS listens_per_day
from 
	qqmusic_user_info u
left join
	 listen_rcd lr ON u.user_id = lr.user_id
group by
	u.user_id, day_of_week
order by
	u.user_id ASC, day_of_week ASC;

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
    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 
    login_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 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
    2
desc

SELECT 
    sum(TIMESTAMPDIFF(SECOND, call_time, grab_time))/count(1) AS avg_response_time_seconds
FROM 
    didi_order_rcd
WHERE 
    grab_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) >= 22) 
        or
        (hour(trx_time) between 0 and 5) 
    )
    and usr_id = '5201314520' 
order by trx_time

select
usr_id	
,mch_nm	
,trx_time	
,trx_amt
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) between 1 and 5 
order by trx_time

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

select 
    cust_uid, mch_nm 
from 
    mt_trx_rcd1 
where 
    cust_uid='MT10000'
group by
    cust_uid, mch_nm 
order by 2

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
class_code
,min(birth_date) as min_birth_date
from
students
group by
class_code
order by
class_code

select
	subject
,max(score)
,min(score)
from
	scores
group by
	subject
order by
	subject

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

select
	class_code
,count(1) AS student_count
from
	students
group by
	class_code	
order by
	student_count

select
	car_cls
	,count(1) AS trip_count
from
	didi_sht_rcd
group by
	car_cls
order by
	trip_count desc