select
mch_nm
,sum(trx_amt)as mch_total_amt
from cmb_usr_trx_rcd
group by mch_nm
having sum(trx_amt)>(select avg(trx_amt) from cmb_usr_trx_rcd)*10
order by mch_total_amt desc
select
distinct 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
(select date(order_time) order_date,
goods_id, sum(order_gmv) total_gmv,
rank() over(partition by date(order_time) order by sum(order_gmv)) ranking
from order_info
where order_time like '2024-10%'
group by order_date, goods_id) t
where ranking <= 3
with daily_gmv as
(select
date(order_time)as order_date
,goods_id
,sum(order_gmv)as total_gmv
from order_info
where year(order_time)=2024
and month(order_time)=10
group by date(order_time),goods_id),
ranked_goods as(
select
order_date
,goods_id
,total_gmv
,row_number()over(
partition by order_date
order by total_gmv)as ranking
from daily_gmv)
select
order_date
,goods_id
,total_gmv
,ranking
from ranked_goods
where ranking<=3
order by order_date,ranking
select
t1.mch_nm as asshole_tried
,t1.trx_cnt
,case when t2.mch_nm is not null then t2.mch_nm else null end as darling_tried
from
(select
mch_nm
,count(*)as trx_cnt
from cmb_usr_trx_rcd
where usr_id=5201314520
and year(trx_time)in(2023,2024)
group by mch_nm
having count(*)>=20)t1
left join
(select distinct mch_nm
from cmb_usr_trx_rcd
where usr_id=5211314521
and year(trx_time)in(2023,2024))t2
on t1.mch_nm=t2.mch_nm
order by t1.trx_cnt desc
select distinct a.mch_nm
from cmb_usr_trx_rcd a
inner join cmb_usr_trx_rcd b
on a.mch_nm=b.mch_nm
where a.usr_id='5201314520'
and b.usr_id='5211314521'
and year(a.trx_time)=2024
and year(b.trx_time)=2024
order by a.mch_nm desc
select
substr(trx_time,1,7) as trx_mon
,count(*)as trx_cnt
,sum(trx_amt)as trx_amt
from cmb_usr_trx_rcd
where
usr_id=5201314520
and date(trx_time) > '2022-11-01' and date(trx_time) <= '2024-12-31'
and ((truncate(trx_amt,0)rlike'88$|98$'and trx_amt>=200 and hour(trx_time)in(0,1,2,23))
or upper(mch_nm) rlike('足疗|保健|按摩|养生|SPA'))
group by usr_id,trx_mon
order by trx_mon
select
trx_amt
,count(*)as total_trx_cnt
,count(distinct usr_id) as unique_usr_cnt
,count(*)/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
date(trx_time)as trx_date
,max(trx_amt)as max_trx_amt
,min(trx_amt)as min_trx_amt
,avg(trx_amt)as avg_trx_amt
,sum(trx_amt)as total_trx_amt
from cmb_usr_trx_rcd
where
mch_nm='红玫瑰按摩保健休闲'
and left(trx_time,7)='2024-09'
group by date(trx_time)
order by trx_date
select
trx_amt
,count(*)as trx_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_trx_amt
,min(trx_amt)as min_trx_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='红玫瑰按摩保健休闲'
and left(trx_time,7)='2024-09'
group by date(trx_time)
order by trx_date
select
date(trx_time)as trx_date
,max(trx_amt)as max_trx_amt
,min(trx_amt)as min_trx_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='红玫瑰按摩保健休闲'
and left(date(trx_time),7)='2024-09'
group by date(trx_time)
order by trx_date
select*
from
cmb_usr_trx_rcd
where usr_id =5201314520
and date(trx_time) between '2024-09-01' and '2024-09-30'
and (hour(trx_time)>='22:00:00' or hour(trx_time)<='05:00:00')
order by trx_time
select*
from
cmb_usr_trx_rcd
where usr_id =5201314520
and date(trx_time) between '2024-09-01' and '2024-09-30'
and hour(trx_time)>'22:00:00' or hour(trx_time)<='05:00:00'
order by trx_time