with data1 as (
select distinct
usr_id,
date(login_time) as login_date
from
user_login_log
where
datediff(current_date, date(login_time)) <= 30
),
data2 as (
select
T.usr_id,
T.login_date as T_date,
T_1.login_date as T_1_date
from
data1 as T
left join
data1 as T_1
on
T.usr_id = T_1.usr_id
and datediff(T.login_date, T_1.login_date) = -1
)
select
T_date as first_login_date,
concat(round(avg(T_1_date is not null)*100, 2), '%') as T1_retention_rate
from
data2
group by
T_date
order by
T_date;
with date1 as (
select usr_id,date(login_time) as date from user_login_log
where datediff(current_date,login_time)<=30
),
date2 as(
select T1.date as last_day ,T2.date as p from date1 T1 left join date1 T2 on T1.usr_id=T2.usr_id and datediff(T1.date,T2.date)=1
) select distinct last_day as login_time,concat(round(avg(p is not null)*100,2),'%') as T1_retention_rate from date2 group by last_day order by 1
select goods_id as oods_id,sum(order_gmv) as total_gmv
from order_info where date(order_time)= '2024-09-10' group by goods_id order by 2 desc limit 10;
select a.mch_nm as asshole_tried,a.trx_cnt,b.mch_nm as darling_tried from (
select mch_nm,count(1) trx_cnt
from cmb_usr_trx_rcd
where year(trx_time) in (2023,2024) and usr_id='5201314520'
group bymch_nm
having count(1)>=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 2 desc
with a as
(
select distinct mch_nm,count(1) as trx_cnt from cmb_usr_trx_rcd where
usr_id='5201314520' and year(trx_time) between 2023 and 2024
group by mch_nm having trx_cnt>=20
)select distinct a.mch_nm as
asshole_tried,a.trx_cnt as trx_cnt,b.mch_nm as darling_tried from a left join cmb_usr_trx_rcd b on a.mch_nm=b.mch_nm
order by 2 desc
with a as
(
selectmch_nm,count(1) as trx_cnt from cmb_usr_trx_rcd where
usr_id='5201314520' and year(trx_time) between 2023 and 2024
group by mch_nm having trx_cnt>=20
)select distinct a.mch_nm as
asshole_tried,a.trx_cnt as trx_cnt,b.mch_nm as darling_tried from a left join cmb_usr_trx_rcd b on a.mch_nm=b.mch_nm
order by 2 desc
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 case
when mch_nm like '%按摩保健休闲%' then '按摩保健休闲'
when lower(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 lower(mch_nm) rlike '.*(按摩|保健|休闲|spa|养生|会所).*'
group by reg_rules
order by mch_cnt desc;
select
case
when mch_nm like '%按摩保健休闲%' then '按摩保健休闲'
when lower(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 lower(mch_nm) rlike '.*(按摩|保健|休闲|spa|养生|会所).*'
group by reg_rules
order by mch_cnt desc;
select
case
when mch_nm like '%按摩保健休闲%' then '按摩保健休闲'
when lower(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 lower(mch_nm) rlike '.*(按摩|保健|休闲|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
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
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(1) as trx_cnt
from cmb_usr_trx_rcd
where mch_nm='红玫瑰按摩保健休闲' and year(trx_time)=2024
and month(trx_time) in (1,2,3,4,5,6,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
mch_nm='红玫瑰按摩保健休闲'
and date(trx_time) between '2024-09-01' and '2024-09-30'
group by
date(trx_time)
order by
trx_date;
select mch_nm,sum(trx_amt) as sum_trx_amt from cmb_usr_trx_rcdwhere usr_id='5201314520' and year(trx_time)='2024' group by mch_nm order by sum_trx_amt desc;