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 *
from didi_order_rcd
where cust_uid = 'kjhd30' and finish_time = '1970-01-01 00:00:00'
order by call_time
limit 1;

select sum(timestampdiff(second,call_time,grab_time))/count(*) as avg_response_time_seconds
from didi_order_rcd
where grab_time != '1970-01-01 000:00:00'

select sum(timestampdiff(second,call_time,grab_time))/count(*) as avg_response_time_seconds
from didi_order_rcd
where finish_time != '1970-01-01 000:00:00'

select sum(timestampdiff(second,call_time,grab_time))/count(*) as avg_response_time_seconds
from didi_order_rcd
where cancel_time is not null;

select t2.live_id,t2.live_nm,count(*) as enter_cnt
from ks_live_t1 t1
join ks_live_t2 t2
on t1.live_id = t2.live_id
where DATE_FORMAT(t1.enter_time,'%Y-%m-%d %H') = '2021-09-12 23'
group by t1.live_id,t2.live_nm
order by enter_cnt desc
limit 5;

select lpad(hour_entered,2,'0') as hour_entered, enter_count
from(
 select hour(enter_time) as hour_entered,
count(*) as enter_count
from ks_live_t1
group by hour(enter_time)) as new_table
order by hour_entered asc;

select lpad(hour_entered,2,'0') as hour_entered, enter_count
from(
 select hour(enter_time) as hour_entered,
count(*) as enter_count
from ks_live_t1 t1 join ks_live_t2 t2 on t1.live_id = t2.live_id
group by hour(enter_time)) as new_table
order by hour_entered asc;

select *
from hand_permutations
where left(card1,1) = left(card2,1)
order by id asc;

select *
from hand_permutations
where right(card1,1) = right(card2,1) and (card1 like 'A%' or card2 like 'A%')
order by id asc;

select *
from hand_permutations
where concat(card1,card2) like '%A%A%' or
concat(card1,card2) like '%A%K%' or
concat(card1,card2) like '%K%K%' or 
concat(card1,card2) like '%K%A%'
order by id

select *
from hand_permutations
where concat(card1,card2) like '%A%A' or
concat(card1,card2) like '%A%K' or
concat(card1,card2) like '%K%K' or 
concat(card1,card2) like '%K%A'
order by id

select goods_id,sum(order_gmv) as total_gmv
from order_info
where date(order_time)='2024-09-10'
group by goods_id
order by total_gmv desc
limit 10;

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

select *
from cmb_usr_trx_rcd
where trx_amt = (
select max(trx_amt)
from cmb_usr_trx_rcd
where usr_id = '5201314520' and year(trx_time) = 2024 )
and 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 
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(trx_amt) 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
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(trx_amt) 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
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(trx_amt) 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

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