select
	SUM(CASE WHEN DATEDIFF(now(),login_time)<=5 THEN 1 ELSE 0 END) AS days_1_to_5,
SUM(CASE WHEN DATEDIFF(now(),login_time)<=10 THEN 1 ELSE 0 END) AS days_6_to_10,
SUM(CASE WHEN DATEDIFF(now(),login_time)<=20 THEN 1 ELSE 0 END) AS days_11_to_20,
SUM(CASE WHEN DATEDIFF(now(),login_time)<=180 THEN 1 ELSE 0 END) AS days_over_20
from user_login_log

select YEAR(dt) AS Y,
	ROUND(AVG(CASE WHEN city="beijing" THEN tmp_hELSE null END),2) AS '北京',
ROUND(AVG(CASE WHEN city="shanghai" THEN tmp_hELSE null END),2) AS '上海',
ROUND(AVG(CASE WHEN city="shenzhen" THEN tmp_hELSE null END),2) AS '深圳',
ROUND(AVG(CASE WHEN city="guangzhou" THEN tmp_hELSE null END),2) AS '广州'
from weather_rcd_china
GROUP BY year(dt)

select 
	sum(grab_time-call_time)/count(*) as avg_response_time_seconds
from didi_order_rcd
where grab_time != "1970-01-01 00:00:00"

select 
	AVG(grab_time-call_time) as avg_response_time_seconds
from didi_order_rcd
where cancel_time != "1970-01-01 00:00:00"
	AND grab_time != "1970-01-01 00:00:00"

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) BETWEEN 00 AND 05) or (HOUR(trx_time) BETWEEN 22 AND 23))

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) BETWEEN 01 AND 05) or (HOUR(trx_time) BETWEEN 22 AND 23))

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) BETWEEN 01 AND 05)

select * from cmb_usr_trx_rcd
WHERE usr_id=5201314520 AND (trx_time BETWEEN "2024-09-01" AND "2024-09-30")
order by trx_time

select * from cmb_usr_trx_rcd
WHERE usr_id=5201314520 AND (trx_time BETWEEN "2024-09-01" AND "2024-09-30")

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

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

select student_id, max(score) as max_score,
min(score) as min_score,
avg(score) as 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) as max_score,
min(score) as min_score
from scores
group by subject
order by subject

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

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

select car_cls,count(*) as trip_count
from didi_sht_rcd
group by car_cls

select 
	CASE 
WHEN trx_amt=1288 THEN "5.DoubleFly"
WHEN trx_amt=888 THEN "4.Doi"
WHEN trx_amt=588 THEN "3.BlowJobbie"
WHEN trx_amt=388 THEN "2.WithMimi"
WHEN trx_amt=288 THEN "1.WithHand"
ELSE "6.other"
END AS ser_type,
COUNT(*) as trx_cnt,
DATE(MIN(trx_time)) as first_date
from cmb_usr_trx_rcd
WHere usr_id=5201314520 AND mch_nm="红玫瑰按摩保健休闲"
group by ser_type
order by ser_type

select 
	CASE 
WHEN trx_amt>=1288 THEN "5.DoubleFly"
WHEN trx_amt>=888 THEN "4.Doi"
WHEN trx_amt>=588 THEN "3.BlowJobbie"
WHEN trx_amt>=388 THEN "2.WithMimi"
WHEN trx_amt>=288 THEN "1.WithHand"
ELSE "6.other"
END AS ser_type,
COUNT(*) as trx_cnt,
DATE(MIN(trx_time)) as first_date
from cmb_usr_trx_rcd
WHere usr_id=5201314520 AND mch_nm="红玫瑰按摩保健休闲"
group by ser_type
order by ser_type

select 
	CASE 
WHEN trx_amt>=1288 THEN "5.DoubleFly"
WHEN trx_amt>=888 THEN "4.Doi"
WHEN trx_amt>=588 THEN "3.BlowJobbie"
WHEN trx_amt>=388 THEN "2.WithMimi"
WHEN trx_amt>=288 THEN "1.WithHand"
END AS ser_type,
COUNT(*) as trx_cnt,
MIN(trx_time) as first_date
from cmb_usr_trx_rcd
WHere usr_id=5201314520 AND mch_nm="红玫瑰按摩保健休闲"
group by ser_type
order by ser_type