select * 
from hand_permutations 
where (card1 like 'A%' or card2 like 'A%') and right(card1,1)=right(card2,1)

select * 
from hand_permutations 
where card1 like 'A%' or card2 like 'A%'

select * 
from hand_permutations 
where card1 like 'A%'

select * 
from hand_permutations 
where (card1 like'A%'or card1 like 'K%') and (card2 like'A%'or card2 like 'K%')

select 
date(a.login_time) login_date,
round(count(distinct b.usr_id)/count(distinct a.usr_id),2) as T1_retention_rate
from user_login_log a 
left join user_login_log b 
on a.usr_id = b.usr_id 
and date(b.login_time) = date(a.login_time) + interval 1 day
where 
date(a.login_time) >= date_sub(current_date, interval 30 day)
and a.usr_id is not null
group by 1
order by 1;

select
count(case when cnt between 1 and 5 then usr_id end),
count(case when cnt between 6 and 10 then usr_id end),
count(case when cnt between 11 and 20 then usr_id end),
count(case when cnt >20 then usr_id end)
from
(select
usr_id,
count(distinct login_date) as cnt
from
(select
usr_id,
date(login_time) as login_date
from user_login_log
where login_time>=date_sub(curdate(),interval 180 day))t1
group by usr_id)t2

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 goods_id,sum(order_gmv) total_gmv
from order_info 
where date(order_time)='2024-09-10'
group by goods_id
order by total_gmv desc
limit 10;

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

select distinct a.mch_nm asshole_tried,a.trx_cnt, b.mch_nm darling_tried
from (select mch_nm,count(1) 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 mch_nm
from cmb_usr_trx_rcd
 where year(trx_time) in (2023 ,2024) and usr_id=5211314521) b
ona.mch_nm=b.mch_nm 
order by 2 desc

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

select distinct a.mch_nm asshole_tried,count(a.trx_amt) trx_cnt, b.mch_nm darling_tried
from (select *
from cmb_usr_trx_rcd
 where year(trx_time) between 2023 and 2024 and usr_id=5201314520) a
left join 
 (select *
from cmb_usr_trx_rcd
 where year(trx_time) between 2023 and 2024 and usr_id=5211314521) b
ona.mch_nm=b.mch_nm 
group by a.mch_nm,b.mch_nm
having count(a.trx_amt)>=20
order by 2 desc

select distinct a.mch_nm asshole_tried,count(a.trx_amt) trx_cnt, b.mch_nm darling_tried
from (select *
from cmb_usr_trx_rcd
 where year(trx_time) between 2023 and 2024 and usr_id=5201314520) a
join 
 (select *
from cmb_usr_trx_rcd
 where year(trx_time) between 2023 and 2024 and usr_id=5211314521) b
ona.mch_nm=b.mch_nm 
group by a.mch_nm,b.mch_nm
having count(a.trx_amt)>=20
order by 2 desc

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

select distinct mch_nm
from cmb_usr_trx_rcd 
where usr_id=5211314521 or usr_id=5201314520 and year(trx_time)=2024
group by mch_nm
having count(distinct usr_id)=2
order by mch_nm desc

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 lower(mch_nm) rlike '.*(按摩|保健|休闲|养生|SPA|会所).*' then '按摩、保健、休闲、养生、SPA、会所'
 end as reg_rules,count(distinct mch_nm) mch_cnt
from cmb_usr_trx_rcd 
where mch_nm like '%按摩保健休闲%' or lower(mch_nm) rlike '.*(按摩|保健|休闲|养生|SPA|会所).*'
group by reg_rules
order by mch_cnt desc

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

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