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 t2.live_id, t2.live_nm,count(*) as enter_cnt
from ks_live_t1 t1 joinks_live_t2 t2 on t1.live_id = t2.live_id
where date_format(t1.enter_time, '%Y-%m_%d %H') = '2023-09-12 23'
group by t1.live_id,t2.live_nm
order by enter_cnt desc limit 5

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.* 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
distinct mch_nm
from cmb_usr_trx_rcd
where usr_id in ('5201314520', '5211314521') and year(trx_time) = 2024
group by mch_nm
having count(usr_id) = 2
order by mch_nm desc

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

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 4 desc

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 mch_nm,usr_id
order by trx_cnt desc limit 5

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 mch_nm,usr_id
order by trx_cnt desc limit 5

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 mch_nm
order by trx_cnt desc limit 5

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 = "红玫瑰按摩保健休闲"

select case trx_amt
when 288 then '1.WithHand'
when 388 then '2.WithMimi'
when 588 then '3.BlowJobbie'
when 888 then '4.Doi'
when 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 usr_id,min(trx_time) as first_time,mch_nm
from cmb_usr_trx_rcd
where usr_id = 5201314520 and mch_nm = "红玫瑰按摩保健休闲"

select trx_amt,count(1) as total_trx_cnt,count(distinct usr_id) as unique_usr_cnt,count(1)/count(distinct usr_id) as avg_trx_per_user
from cmb_usr_trx_rcd
where mch_nm = "红玫瑰按摩保健休闲" and 
(
(year(trx_time) = 2023 and month(trx_time) between 1 and 12) or (year(trx_time) = 2024 and month(trx_time) between 1 and 6)
)
group by trx_amt
order by avg_trx_per_user desc limit 5

select trx_amt,count(1) as total_trx_cnt,count(distinct usr_id) as unique_usr_cnt,count(1)/count(distinct usr_id) as avg_trx_per_user
from cmb_usr_trx_rcd
where mch_nm = "红玫瑰按摩保健休闲" and (year(trx_time) = 2023 and month(trx_time) between 1 and 12) or (year(trx_time) = 2024 and month(trx_time) between 1 and 6)
group by trx_amt
order by avg_trx_per_user desc limit 5

select trx_amt,count(trx_amt) astrx_cnt
from cmb_usr_trx_rcd
where mch_nm = "红玫瑰按摩保健休闲" and year(trx_time) = 2024 and month(trx_time) between 1 and 7
group by trx_amt
order by trx_cnt desc limit 5

select date(trx_time) as trx_date,max(trx_amt) as max_tra_amt,min(trx_amt) as min_tra_amt,avg(trx_amt) as avg_trx_amt,sum(trx_amt) as total_trx_amt
from cmb_usr_trx_rcd
where mch_nm = "红玫瑰按摩保健休闲" anddate(trx_time) between '2024-9-1' and '2024-9-30'
group by date(trx_time)
order by trx_date asc

select date(trx_time) as trx_date,max(trx_amt) as max_tra_amt,min(trx_amt) as min_tra_amt,avg(trx_amt) as avg_trx_amt,sum(trx_amt) as total_trx_amt
from cmb_usr_trx_rcd
where usr_id = '5201314520' and mch_nm = "红玫瑰按摩保健休闲" andyear(trx_time) = 2024 and month(trx_time) = 9
group by date(trx_time)
order by trx_date

select mch_nm,sum(trx_amt) as sum_trx_amt
from cmb_usr_trx_rcd
where usr_id = '5201314520' and year(trx_time) = 2024
group by mch_nm
order by sum_trx_amt desc