select * from hand_permutations
where right(card1, 1)= right(card2,1) and (card1 like 'A%' or card2 like '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;

with At as (
select distinct usr_id,
 date(login_time) as login_date
from user_login_log
where datediff(curdate(), login_time) <= 30
)
select At.login_date,
 concat(round(count(distinct Bt.usr_id)/count(distinct At.usr_id)*100, 2), '%') as T1_retention_rate
from At
 left join At Bt on At.usr_id = Bt.usr_id
and datediff(Bt.login_date, At.login_date) = 1
group by At.login_date

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 * from order_info 
where date(order_time) = '2024-09-10'
order by order_gmv desc
limit 10

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

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

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

select * from singer_info
where type2 = '港台' and type3 = '乐队'

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 distinct a.* from 
(selectmch_nm
from cmb_usr_trx_rcd
where year(trx_time)=2024 and usr_id='5211314521')a
inner join
(selectmch_nm
from cmb_usr_trx_rcd
where year(trx_time)=2024 and usr_id='5201314520')b
on a.mch_nm = b.mch_nm
order by 1 desc

select distinct a.mch_nm from 
(select mch_nm from cmb_usr_trx_rcd 
where usr_id ='5201314520' and year(trx_time)= '2024') as a 
inner join
(select mch_nm from cmb_usr_trx_rcd 
where usr_id ='5211314521' and year(trx_time)= '2024') as b
on a.mch_nm=b.mch_nm

select distinct a.mch_nm from 
(select mch_nm from cmb_usr_trx_rcd 
where usr_id ='5201314520') as a 
inner join
(select mch_nm from cmb_usr_trx_rcd 
where usr_id ='5211314521') as b
on a.mch_nm=b.mch_nm

select * from cmb_usr_trx_rcd
where usr_id = '5201314520' and year(trx_time) = '2024' 
order by trx_amt desc
limit 1

select
    case 
        when mch_nm like '%按摩保健休闲%' then '按摩保健休闲'
        when upper(mch_nm) rlike '.*(按摩|保健|休闲|SPA|养生|会所).*' then '按摩、保健、休闲、养生、SPA、会所'
    end as reg_rules,
    count(distinct mch_nm) as mch_cnt
from
    cmb_usr_trx_rcd
where mch_nm like '%按摩保健休闲%'
   or upper(mch_nm) rlike '.*(按摩|保健|休闲|SPA|养生|会所).*'
group by reg_rules
order by mch_cnt desc;

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
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
,max(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 
count(distinct mch_nm) as mch_nm
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 ser_typ

select 
usr_id,
mch_nm,
sum(trx_amt) as trx_amt,
count(1) 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