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

select avg(timestampdiff(second,call_time,grab_time)) as avg_response_time_seconds
from didi_order_rcd
where cancel_time = "1970-01-01 00:00:00"

select round(count(*) /2) as cnt, round(52*51/2) as ttl_cnt, round(round(count(*) /2) / round(52*51/2), 3) as p
from hand_permutations
where right(card1,1) = right(card2,1)

select 
		case 
 when mch_nm regexp '按摩保健休闲' then '按摩保健休闲'
 when mch_nm regexp '按摩' or mch_nm regexp '保健' or mch_nm regexp '休闲' or mch_nm regexp '养生' or mch_nm regexp 'SPA' or mch_nm regexp '会所' then '按摩、保健、休闲、养生、SPA、会所'
else ' d'end as reg_rules,
count(distinct mch_nm) as mch_cnt
from cmb_usr_trx_rcd
group by reg_rules
limit 1,2

select 
		case when trx_amt = 288 then '1.WithHand'
	 when trx_amt = 388 then '2.WithMimi'
 when trx_amt = 588 then '3.BlowJobbie'
 when trx_amt = 888 then '4.Doi'
 when trx_amt = 1288 then '5.DoubleFly'
 else '6.other'
 end as ser_typ,
count(*) as trx_cnt, min(date(trx_time)) as first_date
from cmb_usr_trx_rcd
where usr_id = '5201314520' and mch_nm = '红玫瑰按摩保健休闲'
group by ser_typ
order by ser_typ

select * 
from stock_info
where area = '北京' and industry = '银行'
order by list_date

select t520.mch_nm as asshole_tried, t520.trx_cnt, t521.mch_nm as darling_tried
from (select mch_nm, count(mch_nm) as trx_cnt
from cmb_usr_trx_rcd
where usr_id = '5201314520' and year(trx_time) in ('2023', '2024')
group by mch_nm
having trx_cnt >= 20) t520
left join (select distinct mch_nm
from cmb_usr_trx_rcd
where usr_id = '5211314521') t521
on t520.mch_nm = t521.mch_nm
order by trx_cnt desc

select distinct t520.mch_nm 
from (select * 
from cmb_usr_trx_rcd
where usr_id ='5201314520' and year(trx_time) = '2024') t520
join (select * 
from cmb_usr_trx_rcd
where usr_id ='5211314521' and year(trx_time) = '2024') t521
on t520.mch_nm = t521.mch_nm 
order by t520.mch_nm desc

select snd_usr_id
from
(select snd_usr_id, count(*) as hw
from
(select * 
from tx_red_pkt_rcd
where pkt_amt in (200, 520)
) a
group by snd_usr_id
having hw >= 5) b

select rcv_usr_id, sum(pkt_amt)as sum_trx_amt
from tx_red_pkt_rcd
where rcv_datetime != '1900-01-01 00:00:00'
group by 1
order by 2 desc limit 10

select snd_usr_id, sum(pkt_amt) as sum_trx_amt
from tx_red_pkt_rcd
group by snd_usr_id
order by sum_trx_amt desc limit 10

select student_id, max(score) as max_score, min(score) as min_score, avg(score) as avg_score
from scores
where (student_id = '460093'or student_id = '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

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

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

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

select name,subject, class_code, graduate_date
from teachers
where year(graduate_date) between '1994' and '1997'

selectname, subject, class_code, qualification
from teachers
where (fir_degr regexp '北京大学' or fir_degr regexp '清华大学') and head_teacher regexp '^c'

select name, subject, class_code, qualification
from teachers
where subject = '数学' and qualification = 'Senior'
order by name