select
gd_id,
gd_nm,
gd_typ
from
gd_inf gd
join
xhs_pchs_rcd pchs
on gd.gd_id = pchs.mch_id
left join
xhs_fav_rcd fav
on gd.gd_id = fav.mch_id
where
fav.mch_id is null
group by
gd_id,
gd_nm,
gd_typ
select
gd_id,
gd_nm,
gd_typ
from
gd_inf as gi
join
xhs_fav_rcd as xfr
on
xfr.mch_id = gi.gd_id
left join
xhs_pchs_rcd as xpr
on
gi.gd_id = xpr.mch_id
where
xpr.mch_id is null
group by
gd_id,
gd_nm,
gd_typ
select
gi.gd_typ,
count(distinct xpr.cust_uid) as buyer_count
from
gd_inf as gi
join
xhs_pchs_rcd as xpr
on
gi.gd_id = xpr.mch_id
group by
gi.gd_typ
order by
buyer_count desc
limit 1
select
gd_id,
gd_nm,
count(cust_uid) as fav_count
from
xhs_fav_rcd xfr
right join
gd_inf gi
on
xfr.mch_id = gi.gd_id
group by
gd_id,
gd_nm
order by
fav_count desc
limit 1
with monthly_trx_amt as (
select date_format(u.trx_time, '%Y-%m') as trx_mon,
sum(u.trx_amt) as monthly_trx_amt
from cmb_usr_trx_rcd u
join
cmb_mch_typ t on u.mch_nm = t.mch_nm
where
u.usr_id = 5201314520
and left(u.trx_time,7) between '2023-01' and '2024-12'
and t.mch_typ = '休闲娱乐'
group by
trx_mon
order by
trx_mon
),
cumulative_trx as(
select trx_mon,
monthly_trx_amt,
sum(monthly_trx_amt)over (order by trx_mon) as cumulative_trx_amt
from
monthly_trx_amt
)
select trx_mon,
cumulative_trx_amt as trx_amt
from
cumulative_trx
order by
trx_mon
with all_top1_trx as (
select
'all' as mch_typ,
mch_nm,
count(*) as trx_cnt,
dense_rank()over(order by count(*) desc) as rnk
from cmb_usr_trx_rcd
where usr_id = 5201314520
group by mch_nm
),
category_top1_trx as(
select
t.mch_typ,
t.mch_nm,
count(u.trx_amt) as trx_cnt,
dense_rank()over(partition by t.mch_typ order by count(u.trx_amt) desc) as rnk
from cmb_mch_typ t
join cmb_usr_trx_rcd u on t.mch_nm = u.mch_nm
where u.usr_id = 5201314520
and t.mch_typ in ('交通出行', '休闲娱乐','咖啡奶茶')
group by t.mch_typ, t.mch_nm
),
filtered_all_top1_trx as (
select
mch_typ,
mch_nm,
trx_cnt,
rnk
from
all_top1_trx
where
rnk <= 1
),
filtered_category_top1_trx as (
select
mch_typ,
mch_nm,
trx_cnt,
rnk
from category_top1_trx
where rnk <= 1
)
select
mch_typ,
mch_nm,
trx_cnt,
rnk
from filtered_all_top1_trx
union all
select
mch_typ,
mch_nm,
trx_cnt,
rnk
from
filtered_category_top1_trx
order by
mch_typ
with yearly_top_merchants as (
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
limit 3 ),
monthly_top_merchants as (
select
date_format(trx_time, '%Y-%m') as trx_mon,
mch_nm,
sum(trx_amt) as sum_trx_amt,
row_number() over(partition by date_format(trx_time, '%Y-%m') order by sum(trx_amt) desc ) as rn
from
cmb_usr_trx_rcd
where
usr_id = 5201314520
and year(trx_time) = 2024
group by
trx_mon,mch_nm
),
filtered_monthly_top_merchants as (
select
trx_mon,
mch_nm,
sum_trx_amt
from
monthly_top_merchants
where
rn <= 3
)
select
'2024' as trx_mon,
mch_nm,
sum_trx_amt
from
yearly_top_merchants
union all
select
trx_mon,
mch_nm,
sum_trx_amt
from
filtered_monthly_top_merchants
order by
trx_mon,
sum_trx_amt desc
select
case when trx_amt >= 200
and (mod(floor(trx_amt),100) = 88 or mod(floor(trx_amt),100) = 98)
and (hour(trx_time) >= 23 or hour(trx_time) <= 3)
then 'illegal'
else 'other'
end as trx_typ,
count(1) as trx_cnt,
sum(trx_amt) as trx_amt,
count(distinct(mch_nm)) as mch_cnt
from cmb_usr_trx_rcd
where usr_id = '5201314520'
group by trx_typ
order by mch_cnt desc
select
case when trx_amt > 200
and (mod(floor(trx_amt),100) = 88 or mod(floor(trx_amt),100) = 98)
and (hour(trx_time >= 23) or hour(trx_time) <= 3)
then 'illegal'
else 'other'
end as type,
count(trx_amt) as trx_cnt,
sum(trx_amt) as trx_amt,
count(distinct(mch_nm)) as mch_cnt
from cmb_usr_trx_rcd
where usr_id = '5201314520'
group by type
order by mch_cnt desc
select
case when mch_nm like '%按摩保健休闲%' then '按摩保健休闲'
when mch_nm rlike '(?i)按摩|保健|休闲|SPA|养生|会所' then '按摩、保健、休闲、养生、SPA、会所'
end as reg_rules,
count(distinct(mch_nm)) as mch_cnt
from cmb_usr_trx_rcd
where mch_nm like '%按摩保健休闲%' or mch_nm rlike '(?i)按摩|保健|休闲|SPA|养生|会所'
group by reg_rules
order by mch_cnt desc
select
case when mch_nm like '%按摩保健休闲%' then '按摩保健休闲'
when mch_nm rlike '(?i)(按摩|保健|休闲|SPA|养生|会所)' then '按摩、保健、休闲、养生、SPA、会所'
end as reg_rules,
count(distinct(mch_nm)) as mch_cnt
from cmb_usr_trx_rcd
where mch_nm like '%按摩保健休闲%' or mch_nm rlike '(?i)(按摩|保健|休闲|SPA|养生|会所)'
group by reg_rules
order by mch_cnt desc
select
case when mch_nm like '%按摩保健休闲%' then '按摩保健休闲'
when mch_nm rlike '(?i).*(按摩|保健|休闲|SPA|养生|会所).*' then '按摩、保健、休闲、养生、SPA、会所'
end as reg_rules,
count(distinct(mch_nm)) as mch_cnt
from cmb_usr_trx_rcd
where mch_nm like '%按摩保健休闲%' or mch_nm rlike '(?i).*(按摩|保健|休闲|SPA|养生|会所).*'
group by reg_rules
order by mch_cnt desc
select
case when mch_nm like '%按摩保健休闲%' then '按摩保健休闲'
when mch_nm rlike '(?i).*(按摩|保健|休闲|SPA|养生|会所).*' then '按摩、保健、休闲、SPA、养生、会所'
end as reg_rules,
count(distinct(mch_nm)) as mch_cnt
from cmb_usr_trx_rcd
where mch_nm like '%按摩保健休闲%' or mch_nm rlike '(?i).*(按摩|保健|休闲|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,
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
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 usr_id, mch_nm
order by trx_cnt desc