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(grab_time-call_time) as avg_response_time_seconds
from didi_order_rcd
where cancel_time = '1970-01-01 00:00:00'

select avg(grab_time) as avg_response_time_seconds
from didi_order_rcd
where cancel_time = '1970-01-01 00:00:00'

select k1.live_id,k2.live_nm,count(*) as enter_cnt
from ks_live_t1 k1
join ks_live_t2 k2 on k1.live_id = k2.live_id
where hour(k1.enter_time) = 23
group by k1.live_id,k2.live_nm
order by enter_cnt desc
limit 5;

select date_format(enter_time,"%H") as hour_entered, count(*) as enter_count
from ks_live_t1
group by date_format(enter_time,"%H")
order by date_format(enter_time,"%H")

SELECT DATE_FORMAT(enter_time, '%H') AS hour_entered, COUNT(*) AS enter_count
FROM ks_live_t1
GROUP BY DATE_FORMAT(enter_time, '%H')
ORDER BY hour_entered;

SELECT HOUR(enter_time) AS hour_entered, COUNT(*) AS enter_count
FROM ks_live_t1
GROUP BY HOUR(enter_time)
ORDER BY hour_entered;

select hour(enter_time) as hour_entered, count(1) as enter_count
from ks_live_t1
group by hour(enter_time)
order by hour(enter_time)

select hour(enter_time) as hour_entered, count(1) as enter_count
from ks_live_t1
group by hour(enter_time)

select id, card1, card2
from hand_permutations
where concat(card1,card2) like '%K%K%' or
		concat(card1,card2) like '%A%A%' or
		concat(card1,card2) like '%A%K%' or
		concat(card1,card2) like '%K%A%' 
 ORDER BY ID;

select usr_id, mch_nm, trx_time, trx_amt
from cmb_usr_trx_rcd
where usr_id = 5201314520 and YEAR(trx_time) = 2024
		and trx_amt = (
select max(trx_amt)
from cmb_usr_trx_rcd
where usr_id = 5201314 and YEAR(trx_time) = 2024
)

select *
from cmb_usr_trx_rcd
where usr_id=5201314520 
	and year(trx_time) = 2024 
and trx_amt = (SELECT MAX(trx_time) 
 FROM cmb_usr_trx_rcd 
 WHERE usr_id = 5201314520 and year(trx_time) = 2024)

select count(distinct mch_nm) as mch_cnt
from cmb_usr_trx_rcd
where mch_nm like '%按摩保健休闲%'

select count(1) as mch_cnt
from cmb_usr_trx_rcd
where mch_nm like '%按摩保健休闲%'

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(1) 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 1

select usr_id, mch_nm, sum(trx_amt) as trx_amt,
 		count(*) as trx_cnt,
min(trx_time) as first_time
 from cmb_usr_trx_rcd
 where usr_id = 5201314520 and trx_amt >= 288
 group by mch_nm, usr_id
 order by trx_cnt desc

select usr_id, mch_nm, sum(trx_amt) as trx_amt,
 		count(*) as trx_cnt,
min(trx_time) as first_time
 from cmb_usr_trx_rcd
 where usr_id = 5201314520
 group by mch_nm, usr_id
 having sum(trx_amt) >= 288
 order by trx_cnt desc

select usr_id, mch_nm, sum(trx_amt) as trx_amt,
 		count(*) as trx_cnt,
min(trx_time) as first_time
 from cmb_usr_trx_rcd
 where usr_id = 5201314520 
 group by mch_nm
 having sum(trx_amt) >= 288
 order by trx_cnt desc

select usr_id, mch_nm, sum(trx_amt) as trx_amt,
 		count(*) as trx_cnt,
min(trx_time) as first_time
 from cmb_usr_trx_rcd
 where usr_id = 5201314520 
 group by mch_nm
 having sum(trx_amt) >= 288
 order by trx_cnt

select usr_id, min(trx_time)as first_time, mch_nm
from cmb_usr_trx_rcd
where mch_nm = '红玫瑰按摩保健休闲' and usr_id=5201314520