with ranked_sales as (
select
goods_id,
sum(order_gmv) as total_gmv,
rank() over (order by sum(order_gmv) desc) as ranking
from
order_info
where
date(order_time) = '2024-09-10'
group by
goods_id
)
select
goods_id,
total_gmv,
ranking
from
ranked_sales
where
ranking <= 10
order by
ranking,
total_gmv desc;
select a.mch_nm as asshole_tried,a.trx_cnt as trx_cnt , b.mch_nm as darling_tried
from
(select mch_nm ,count(1) as trx_cnt from cmb_usr_trx_rcd where year(trx_time) in (2023,2024) and usr_id='5201314520' group by mch_nm having trx_cnt >=20 )a
left join
(select distinct mch_nm from cmb_usr_trx_rcd where year(trx_time) in (2023,2024)and usr_id='5211314521')b
on a.mch_nm = b.mch_nm order by trx_cnt desc
select a.mch_nm as asshole_tried,a.trx_cnt as trx_cnt , b.mch_nm as darling_tried
from
(select mch_nm ,count(1) as trx_cnt from cmb_usr_trx_rcd where year(trx_time) in (2023,2024) and usr_id='5201413520' group by mch_nm having trx_cnt >=20 )a
left join (select distinct mch_nm from cmb_usr_trx_rcd where year(trx_time) in (2023,2024)and usr_id='5211314521')b
on a.mch_nm = b.mch_nm order by trx_cnt desc
select a.mch_nmfrom
(select distinct mch_nm from cmb_usr_trx_rcd where usr_id='5201314520'and year(trx_time)=2024) a
inner join
(select distinct mch_nm from cmb_usr_trx_rcd where usr_id='5211314521'and year(trx_time)=2024) b
on a.mch_nm = b.mch_nm
order by 1 desc
select a.mch_nmfrom
(select distinct mch_nm from cmb_usr_trx_rcd where usr_id='5201314520'and year(trx_time)=2024) a
left join
(select distinct mch_nm from cmb_usr_trx_rcd where usr_id='5211314521'and year(trx_time)=2024) b
on a.mch_nm = b.mch_nm
order by 1 desc
select usr_id,mch_nm,trx_time,trx_amt 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 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(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 trx_cnt 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 trx_cnt desc limit 5
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(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(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 order by trx_cnt desc limit 5
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' group by mch_nm order by trx_cnt desc