select
round(avg(datediff('2021-02-13',bth_dt)/365),2) as avg_age
from tx_red_pkt_rcd as r
join tx_usr_bas_info as i
onr.rcv_usr_id=i.usr_id
where pkt_amt=520.00
and date(rcv_datetime)='2021-02-13'
select
round(avg(datediff(now(),bth_dt)/365),2) as avg_age
from tx_red_pkt_rcd as r
join tx_usr_bas_info as i
onr.snd_usr_id=i.usr_id
where pkt_amt=520
and date(snd_datetime)='2021-02-13'
with a as(
select snd_usr_id
,pkt_amt
,snd_datetime
,i.cty
,m.cty_cls
from
tx_red_pkt_rcd as r
join
tx_usr_bas_info as i
on
r.snd_usr_id=i.usr_id
join
tx_cty_map as m
on
m.cty=i.cty
where
date(snd_datetime)='2021-02-13'
and
m.cty_cls='新一线')
select pkt_amt,count(*) as send_count from a
group by 1
order by 2 desc
limit 1
select distinct r1.snd_usr_id as user_a
,r1.rcv_usr_id as user_b
from tx_red_pkt_rcd as r1
join tx_red_pkt_rcd as r2
on r1.snd_usr_id=r2.rcv_usr_id
and r1.rcv_usr_id=r2.snd_usr_id
where date(r1.snd_datetime)='2021-02-13'
and date(r2.snd_datetime)='2021-02-13'
and r1.snd_usr_id <r1.rcv_usr_id
with a as (
select
concat(year(trx_time),'-Q',quarter(trx_time)) as trx_quarter,
mch_nm,
trx_amt
from cmb_usr_trx_rcd
where year(trx_time) >='2023'
and mch_nm = '红玫瑰按摩保健休闲'
and usr_id=5201314520
order by 1)
selectdistinct trx_quarter,
count(case when trx_amt=288 then trx_amt else null end)over(order by trx_quarter) as WithHand,
count(case when trx_amt=888 then trx_amt else null end)over(order by trx_quarter) as Doi
from a
order by 1
with quarterly_transactions as (
select
*,
concat(year(trx_time), '-', 'Q', quarter(trx_time)) as trx_quarter,
count(case when trx_amt = 288 then trx_amt else null end) over (order by concat(year(trx_time), '-', 'Q', quarter(trx_time))) as withhand,
count(case when trx_amt = 888 then trx_amt else null end) over (order by concat(year(trx_time), '-', 'Q', quarter(trx_time))) as doi
from
cmb_usr_trx_rcd
where
usr_id = 5201314520 and mch_nm='红玫瑰按摩保健休闲'
)
select
trx_quarter,
withhand,
doi
from
quarterly_transactions
group by
trx_quarter,
withhand,
doi
order by
trx_quarter;
with a as (
select
concat(year(trx_time),'-Q',quarter(trx_time)) as trx_quarter,
mch_nm,
trx_amt
from cmb_usr_trx_rcd
where year(trx_time) >='2023'
and mch_nm like '%红玫瑰%'
and usr_id=5201314520
order by 1)
selectdistinct trx_quarter,
count(case when trx_amt=288 then trx_amt else null end)over(order by trx_quarter) as WithHand,
count(case when trx_amt=888 then trx_amt else null end)over(order by trx_quarter) as Doi
from a
order by 1
with a as (
select
concat(year(trx_time),'-Q',quarter(trx_time)) as trx_quarter,
mch_nm,
trx_amt
from cmb_usr_trx_rcd
where year(trx_time) >='2023'
and mch_nm like '%红玫瑰%'
and usr_id=5201314520
order by 1),
b as (select trx_quarter,
count(case when trx_amt=288 then trx_amt else null end)over(order by trx_quarter) as WithHand,
count(case when trx_amt=888 then trx_amt else null end)over(order by trx_quarter) as Doi
from a
order by 1)
select trx_quarter
,sum(WithHand) WithHand
,sum(Doi)Doi
from b
group by 1
order by 1
with a as(
select date_format(date_value,'%Y-%m') as trx_mon
from date_table
where year(date_value)=2023
and month(date_value) between 1 and 12
group by 1
),
b as (
select date_format(trx_time,'%Y-%m') as trx_mon
,sum(trx_amt) as trx_amt
from cmb_usr_trx_rcd as r
join cmb_mch_typ as t on r.mch_nm=t.mch_nm
where usr_id=5201314520
and year(trx_time)=2023
and mch_typ='休闲娱乐'
group by 1
order by 1
)
select a.trx_mon
,sum(trx_amt)over(order by a.trx_mon ) as trx_amt
from a
left join b
on a.trx_mon=b.trx_mon
with month_sequence as (
select
date_format(dv.date_value, '%Y-%m') as trx_mon
from
date_table dv
where
year(dv.date_value) = 2023
and month(dv.date_value) between 1 and 12
group by date_format(dv.date_value, '%Y-%m')
),
monthly_trx as (
select
date_format(t.trx_time, '%Y-%m') as trx_mon,
sum(t.trx_amt) as monthly_trx_amt
from
cmb_usr_trx_rcd t
join
cmb_mch_typ m on t.mch_nm = m.mch_nm
where
t.usr_id = 5201314520
and m.mch_typ = '休闲娱乐'
and year(t.trx_time) = 2023
group by
trx_mon
),
combined_trx as (
select
ms.trx_mon,
coalesce(mt.monthly_trx_amt, 0) as monthly_trx_amt
from
month_sequence ms
left join
monthly_trx mt on ms.trx_mon = mt.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
combined_trx
)
select
trx_mon,
cumulative_trx_amt as trx_amt
from
cumulative_trx
order by
trx_mon;
select
a.trx_mon,
ifnull(b.last_day, '1900-01-01') as last_day,
ifnull(b.day_of_mon, 0) as day_of_mon,
ifnull(b.trx_amt, 0) as trx_amt,
ifnull(b.trx_cnt, 0) as trx_cnt,
ifnull(round(b.avg_day_amt, 2), 0) as avg_day_amt,
ifnull(round(b.avg_day_cnt, 2), 0) as avg_day_cnt
from
(select distinct date_format(date_value, '%Y-%m') as trx_mon
from date_table
where date_format(date_value, '%Y-%m') between '2023-01' and '2024-06'
) a
LEFT JOIN
(SELECT
trx_mon,
last_day,
day_of_mon,
SUM(trx_amt) AS trx_amt,
COUNT(*) AS trx_cnt,
SUM(trx_amt) / day_of_mon AS avg_day_amt,
COUNT(*) / day_of_mon AS avg_day_cnt
FROM (
SELECT
r.trx_time,
DATE_FORMAT(r.trx_time, '%Y-%m') AS trx_mon,
LAST_DAY(r.trx_time) AS last_day,
DAY(LAST_DAY(r.trx_time)) AS day_of_mon,
r.trx_amt
FROM
cmb_usr_trx_rcd AS r
LEFT JOIN
cmb_mch_typ AS t
ON
r.mch_nm = t.mch_nm
WHERE
r.usr_id = 5201314520
AND (trx_time BETWEEN '2023-01-01' AND '2024-06-30')
AND HOUR(r.trx_time) IN (23, 0, 1, 2)
AND (t.mch_typ = '休闲娱乐' OR t.mch_typ IS NULL)
AND r.trx_amt >= 288
) AS subquery
GROUP BY
trx_mon, last_day, day_of_mon
ORDER BY
trx_mon) as b
on a.trx_mon=b.trx_mon
order by 1
select a.trx_mon,
coalesce(d.last_day, '1900-01-01') last_day,
coalesce(d.days_of_mon, 0) day_of_mon,
coalesce(d.trx_amt, 0) trx_amt,
coalesce(d.trx_cnt, 0) trx_cnt,
coalesce(round(d.avg_day_amt, 2), 0) avg_day_amt,
coalesce(round(d.avg_day_cnt, 2), 0) avg_day_cnt
from
(select distinct left(trx_time, 7) trx_mon from cmb_usr_trx_rcd
where trx_time rlike '(2023|2024-0[1-6]).*'
order by trx_mon) a
left join
(select left(b.trx_time, 7) trx_mon,
last_day(max(b.trx_time)) last_day,
day(last_day(max(b.trx_time))) days_of_mon,
sum(b.trx_amt) trx_amt,
count(*) trx_cnt,
sum(b.trx_amt) / day(last_day(max(b.trx_time))) avg_day_amt,
count(*) / day(last_day(max(b.trx_time))) avg_day_cnt
from cmb_usr_trx_rcd b
left join cmb_mch_typ c on b.mch_nm = c.mch_nm
where b.usr_id = 5201314520 and b.trx_amt >= 288
and b.trx_time rlike '(2023|2024-0[1-6]).*'
and hour(b.trx_time) in (0, 1, 2, 23)
and (c.mch_typ is null or c.mch_typ = '休闲娱乐')
group by trx_mon order by trx_mon
) d
on a.trx_mon = d.trx_mon
order by a.trx_mon
with a as (
select mch_typ2
,year
,count(cnt) as repeat_users
from (
select mch_typ2
,year(trx_dt) as year
,cust_uid
,count(cust_uid) cnt
from mt_trx_rcd1
where year(trx_dt)=2021
group by 1,2,3
having count(cust_uid)>=2 ) as t
group by 1,2
),
b as (
select mch_typ2
,year(trx_dt) as year
,count(distinct cust_uid) as total_users
from mt_trx_rcd1
group by 1,2)
select a.mch_typ2
,a.year
,total_users
,repeat_users
,round((repeat_users/nullif(total_users,0))*100,2) as repurchase_rate
from a
rightjoin b on a.mch_typ2=b.mch_typ2and a.year=b.year
order by 1,2
with a as (
select mch_typ2
,year
,count(cnt) as repeat_users
from (
select mch_typ2
,year(trx_dt) as year
,cust_uid
,count(cust_uid) cnt
from mt_trx_rcd1
where year(trx_dt)=2021
group by 1,2,3
having count(cust_uid)>=2 ) as t
group by 1,2
),
b as (
select mch_typ2
,year(trx_dt) as year
,count(distinct cust_uid) as total_users
from mt_trx_rcd1
group by 1,2)
select a.mch_typ2
,a.year
,total_users
,repeat_users
,round(repeat_users/nullif(total_users,0)*100,2) as repurchase_rate
from a
rightjoin b on a.mch_typ2=b.mch_typ2and a.year=b.year
order by 1,2
WITH user_transactions AS (
SELECT
mch_typ2,
cust_uid,
DATE_FORMAT(trx_dt, '%Y') AS year
FROM mt_trx_rcd1
),
user_transaction_counts AS (
SELECT
mch_typ2,
year,
cust_uid,
COUNT(*) AS transaction_count
FROM user_transactions
GROUP BY mch_typ2, year, cust_uid
),
total_users AS (
SELECT
mch_typ2,
year,
COUNT(DISTINCT cust_uid) AS total_users
FROM user_transactions
GROUP BY mch_typ2, year
),
repeat_users AS (
SELECT
mch_typ2,
year,
COUNT(cust_uid) AS repeat_users
FROM user_transaction_counts
WHERE transaction_count >= 2
GROUP BY mch_typ2, year
)
SELECT
tu.mch_typ2,
tu.year,
tu.total_users,
ru.repeat_users,
ROUND((ru.repeat_users / NULLIF(tu.total_users, 0)) * 100, 2) AS repurchase_rate
FROM total_users tu
LEFT JOIN repeat_users ru ON tu.mch_typ2 = ru.mch_typ2 AND tu.year = ru.year
ORDER BY tu.mch_typ2, tu.year;
with a as (
select mch_typ2
,year
,count(cnt) as repeat_users
from (
select mch_typ2
,year(trx_dt) as year
,cust_uid
,count(cust_uid) cnt
from mt_trx_rcd1
where year(trx_dt)=2021
group by 1,2,3
having count(cust_uid)>=2 ) as t
group by 1,2
),
b as (
select mch_typ2
,year(trx_dt) as year
,count(distinct cust_uid) as total_users
from mt_trx_rcd1
group by 1,2)
select a.mch_typ2
,a.year
,total_users
,repeat_users
,round(repeat_users/total_users*100,2) as repurchase_rate
from a
rightjoin b on a.mch_typ2=b.mch_typ2and a.year=b.year
order by 1,2
with a as (
select mch_typ2
,year
,count(cnt) as repeat_users
from (
select mch_typ2
,year(trx_dt) as year
,cust_uid
,count(cust_uid) cnt
from mt_trx_rcd1
where year(trx_dt)=2021
group by 1,2,3
having count(cust_uid)>=2 ) as t
group by 1,2
),
b as (
select mch_typ2
,year(trx_dt) as year
,count(distinct cust_uid) as total_users
from mt_trx_rcd1
group by 1,2)
select a.mch_typ2
,a.year
,total_users
,repeat_users
,round(repeat_users/total_users*100,2) as repurchase_rate
from a
left join b on a.mch_typ2=b.mch_typ2and a.year=b.year
order by 1,2
with a as (
select mch_typ2
,year
,count(cnt) as repeat_users
from (
select mch_typ2
,year(trx_dt) as year
,cust_uid
,count(cust_uid) cnt
from mt_trx_rcd1
where year(trx_dt)=2021
group by 1,2,3
having count(cust_uid)>=2 ) as t
group by 1,2
),
b as (
select mch_typ2
,year(trx_dt) as year
,count(distinct cust_uid) as total_users
from mt_trx_rcd1
group by 1,2)
select a.mch_typ2
,a.year
,total_users
,repeat_users
,round(repeat_users/total_users*100,2) as repurchase_rate
from a
left join bon a.mch_typ2=b.mch_typ2and a.year=b.year
order by 1,2
with a as (
select mch_typ2
,year
,count(cnt) as repeat_users
from (
select mch_typ2
,year(trx_dt) as year
,cust_uid
,count(cust_uid) cnt
from mt_trx_rcd1
where year(trx_dt)=2021
group by 1,2,3
having count(cust_uid)>=2 ) as t
group by 1,2
),
b as (
select mch_typ2
,year(trx_dt) as year
,count(distinct cust_uid) as total_users
from mt_trx_rcd1
group by 1,2)
select a.mch_typ2
,a.year
,total_users
,repeat_users
,round(repeat_users/total_users*100,2) as repurchase_rate
from a
join bon a.mch_typ2=b.mch_typ2and a.year=b.year
order by 1,2