select * from cmb_usr_trx_rcd
where trx_amt = (select max(trx_amt) fromcmb_usr_trx_rcd where year(trx_time)= '2024')

select count(distinct usr_id) as 
active_users
from user_login_log limit 5;

select
dt,
tmp_h,
tmp_l,
con
from weather_rcd_china
where city = 'beijing' and (con like '%雪%' or con like '雪%' or con like '%雪')

select
dt,
tmp_h,
tmp_l,
con
from weather_rcd_china
where city = 'beijing' and (con like '%雪' or con like '雪%')

select
dt,
tmp_h,
tmp_l,
con
from weather_rcd_china
where city = 'beijing' and (con like '%雪%' or con like '雪%')

select
dt,
tmp_h,
tmp_l
con
from weather_rcd_china
where city = 'beijing' and (con like '%雪%' or con like '雪%')

select
year(list_date) as Y,
count(distinct ts_code)as cnt
from stock_info
where industry = '银行'
and ts_code like '%SZ'
group by year(list_date)

select
*
from cmb_usr_trx_rcd
where usr_id=5201314520 and year(trx_time) = 2024 
and (select max(trx_amt) from cmb_usr_trx_rcd)

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 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 '%按摩保健休闲%'
and upper(mch_nm) rlike '.*(按摩|保健|休闲|养生|SPA|会所).*'
group by reg_rules
order by mch_cnt desc

select
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

select
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

select
rcv_usr_id,
sum(pkt_amt) as sum_trx_amt
from tx_red_pkt_rcd
group by rcv_usr_id
order by sum_trx_amt desc
limit 10;

select
snd_usr_id,
sum(pkt_amt) as sum_trx_amt
from tx_red_pkt_rcd
group by snd_usr_id
order by sum_trx_amt desc
limit 10;

select
usr_id,
sum(trx_amt) as total_amt,
(select avg(trx_amt) from cmb_usr_trx_rcd) as platform_avg_amt
from cmb_usr_trx_rcd
group by usr_id
order by total_amt desc;

select *
from cmb_usr_trx_rcd
where trx_amt > (select avg(trx_amt) from cmb_usr_trx_rcd)
order by trx_amt desc

select 
subject,
avg(score) as average_score
from scores
where exam_date = '2024-06-30'
group by subject
order by subject asc;

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 sum(order_gmv) desc
limit 10

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 '%按摩保健休闲%'