select
	t2.live_id,
t2.live_nm,
count(1) as enter_cnt
from ks_live_t1 t1
	left 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(1) 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 HOUR(enter_time),COUNT(1)
FROM ks_live_t1
group by HOUR(enter_time)

select 
    sum(case when right(card1,1)=right(card2,1) then 1 else 0 end)/2 as cnt 
    , count(1)/2 as ttl_cnt 
    ,cast(sum(case when right(card1,1)=right(card2,1) then 1 else 0 end)/count(1) AS DECIMAL(4,3)) as p 
from hand_permutations

select 
    sum(case when right(card1,1)=right(card2,1) then 1 else 0 end)/2 as cnt 
    , count(1)/2 as ttl_cnt 
    ,sum(case when right(card1,1)=right(card2,1) then 1 else 0 end)/count(1)as p 
from hand_permutations

select * from hand_permutations 
WHERE LEFT(CARD1,1) = LEFT(card2,1)

select * from hand_permutations 
where
	LEFT(card1,1) = LEFT(card2,1)

select * from hand_permutations 
where
	RIGHT(card1,2) = RIGHT(card2,2)

select * from hand_permutations 
where
	RIGHT(card1,1) = RIGHT(card2,1)

select
	*
from
	hand_permutations
where
	(card1 LIKE 'A%'
OR card2 LIKE 'A%')
AND RIGHT(card1,1) = RIGHT(CARD2,1)
order by id;

select
	*
from
	hand_permutations
where
	card1 LIKE 'A%'
OR card2 LIKE 'A%'
AND RIGHT(card1,1) = RIGHT(CARD2,1)
order by id;

select
	*
from
	hand_permutations
where
	card1 LIKE 'A%'
OR card2 LIKE 'A%'
AND LEFT(card1,2) = LEFT(CARD2,2)
order by id;

select
	*
from
	hand_permutations
where
	card1 LIKE '%A%'
OR card2 LIKE '%A%'
AND LEFT(card1,2) = LEFT(CARD2,2)
order by id;

select
	*
from
	hand_permutations
where
	card1 LIKE '%A%'
OR card2 LIKE '%A%'
AND LEFT(card1,2) = LEFT(CARD2,2)

select
	*
from
	hand_permutations
where
	card1 LIKE '%A%'
OR card2 LIKE '%A%'

select a.* from 
(select distinct mch_nm
from cmb_usr_trx_rcd
where year(trx_time)=2024 and usr_id='5211314521')a
inner 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
    *
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
    case 
        when mch_nm like '%按摩保健休闲%' then '按摩保健休闲'
        when lower(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 lower(mch_nm) rlike '.*(按摩|保健|休闲|spa|养生|会所).*'
group by reg_rules
order by mch_cnt desc;

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

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