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;
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 ( 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;
with first_trx as(
select min(trx_time) as first_trx_time
from cmb_usr_trx_rcd
where usr_id='5201314520' and mch_nm='红玫瑰按摩保健休闲'
group by mch_nm)
SELECT *
FROM cmb_usr_trx_rcd
WHERE usr_id = 5201314520
AND trx_time BETWEEN (
SELECT first_trx_time FROM first_trx
) AND (
SELECT DATE_ADD(first_trx_time, INTERVAL 2 hour) FROM first_trx
) order by trx_time;
select '2022-10-03 17:20:20' as time_he_love_me,
datediff(current_date, '2022-10-03') as days_we_falling_love,
timestampdiff(hour, '2022-10-03 17:20:20', now()) as hours_we_falling_love,
datediff(min(trx_time), '2022-10-03') as days_he_fvck_else
from cmb_usr_trx_rcd
where usr_id='5201314520' and mch_nm='红玫瑰按摩保健休闲'
group by mch_nm
SELECT
a.trx_mon,
COALESCE(b.last_day, '1900-01-01') AS last_day,
COALESCE(b.day_of_mon, 0) AS day_of_mon,
COALESCE(b.trx_amt, 0) AS trx_amt,
COALESCE(b.trx_cnt, 0) AS trx_cnt,
COALESCE(ROUND(b.avg_day_amt, 2), 0) AS avg_day_amt,
COALESCE(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
DATE_FORMAT(a.trx_time, '%Y-%m') AS trx_mon,
LAST_DAY(MAX(a.trx_time)) AS last_day,
DAY(LAST_DAY(MAX(a.trx_time))) AS day_of_mon,
SUM(a.trx_amt) AS trx_amt,
COUNT(*) AS trx_cnt,
SUM(a.trx_amt) / DAY(LAST_DAY(MAX(a.trx_time))) AS avg_day_amt,
COUNT(*) / DAY(LAST_DAY(MAX(a.trx_time))) AS avg_day_cnt
FROM cmb_usr_trx_rcd a
LEFT JOIN cmb_mch_typ m ON a.mch_nm = m.mch_nm
WHERE a.usr_id = 5201314520
AND DATE_FORMAT(a.trx_time, '%Y-%m') BETWEEN '2023-01' AND '2024-06'
AND (m.mch_typ = '休闲娱乐' OR m.mch_typ IS NULL)
AND HOUR(a.trx_time) IN (23, 0, 1, 2)
AND a.trx_amt >= 288
GROUP BY DATE_FORMAT(a.trx_time, '%Y-%m')
ORDER BY 1
) b
ON a.trx_mon = b.trx_mon
ORDER BY a.trx_mon;
select distinct left(trx_time, 7) as trx_mon,
last_day(max(trx_time)) as last_day,
day(last_day(max(trx_time))) as day_of_mon,
sum(trx_amt) as trx_amt,
count(trx_amt) as trx_cnt,
sum(trx_amt)/day(max(trx_time)) as avg_day_amt,
count(trx_amt)/day(max(trx_time)) as avg_day_cnt
from cmb_usr_trx_rcd u left join cmb_mch_typ m on u.mch_nm=m.mch_nm
where usr_id = '5201314520'
and trx_time between '2023-01-01' and '2024-06-30'
and (mch_typ='休闲娱乐' or mch_typ is null)
and trx_amt >288
and hour(trx_time) not between 3 and 22
group by trx_mon
order by trx_mon
select distinct left(trx_time, 7) as trx_mon,
last_day(max(trx_time)) as last_day,
day(max(trx_time)) as day_o_mon,
sum(trx_amt) as trx_amt,
count(trx_amt) as trx_cnt,
sum(trx_amt)/day(max(trx_time)) as avg_day_amt,
count(trx_amt)/day(max(trx_time)) as avg_day_cnt
from cmb_usr_trx_rcd u left join cmb_mch_typ m on u.mch_nm=m.mch_nm
where usr_id = '5201314520'
and trx_time between '2023-01-01' and '2024-06-30'
and (mch_typ='休闲娱乐' or mch_typ is null)
and trx_amt >288
and hour(trx_time) not between 3 and 22
group by trx_mon
order by trx_mon
select distinct left(trx_time,7) as trx_mon,
last_day(max(trx_time)) as last_day,
day(last_day(max(trx_time))) as days_of_mon,
sum(trx_amt) as trx_amt,
count(trx_amt) as trx_cnt,
sum(trx_amt)/day(last_day(max(trx_time))) as avg_day_amt,
count(trx_amt)/day(last_day(max(trx_time))) as avg_day_amt
from cmb_usr_trx_rcd u left join cmb_mch_typ m on u.mch_nm=m.mch_nm
where usr_id='5201314520' and year(trx_time) in (2023, 2024) and mch_typ='休闲娱乐'
group by trx_mon
order by trx_mon
select distinct left(trx_time, 7) as trx_mon, last_day(trx_time) as last_day, day(last_day(trx_time)) as days_of_mon
from cmb_usr_trx_rcd u left join cmb_mch_typ m on u.mch_nm=m.mch_nm
where usr_id='5201314520' and year(trx_time) in (2023, 2024) and mch_typ='休闲娱乐'
order by trx_mon
select distinct left(trx_time, 7) as trx_mon, last_day(trx_time) as last_day, day(last_day(trx_time)) as days_of_mon
from cmb_usr_trx_rcd
where usr_id='5201314520' and year(trx_time) in (2023, 2024)
order by trx_mon
select distinct left(trx_time, 7) as trx_mon, last_day(trx_time) as last_day, day(trx_time) as days_of_mon
from cmb_usr_trx_rcd
where usr_id='5201314520' and year(trx_time) in (2023, 2024)
order by trx_mon
select distinct left(trx_time, 7) as trx_mon, last_day(trx_time) as last_day
from cmb_usr_trx_rcd
where year(trx_time) in (2023, 2024) and usr_id = '5201314520'
order by trx_mon
select left(trx_time, 7) as trx_mon, last_day(trx_time) as last_day
from cmb_usr_trx_rcd
where year(trx_time) in (2023, 2024) and usr_id = '5201314520'
order by trx_mon
select left(trx_time, 7) as trx_mon, last_day(trx_time) as last_day
from cmb_usr_trx_rcd
where year(trx_time) in (2023, 2024) and usr_id = '5201314520'
select
m.mch_typ, u.mch_nm,
count(u.trx_amt) as trx_cnt,
sum(u.trx_amt) as trx_cmt
from cmb_usr_trx_rcd u left join cmb_mch_typ m on u.mch_nm =m.mch_nm
where u.usr_id = '5201314520' and year(u.trx_time)=2024 and m.mch_typ is null
group by m.mch_typ, u.mch_nm
order by trx_cnt desc
select m.mch_typ, u.mch_nm, count(u.trx_amt) as trx_cnt, sum(u.trx_amt) as trx_cmt
from cmb_usr_trx_rcd u left join cmb_mch_typ m on u.mch_nm =m.mch_nm
where u.usr_id = '5201214520' and year(u.trx_time)=2024 and m.mch_typ is null
group by m.mch_typ, u.mch_nm
order by trx_cnt desc
select mch_typ, count(trx_amt) as trx_cnt, sum(trx_amt) as trx_amt
from cmb_usr_trx_rcd left join cmb_mch_typ on cmb_usr_trx_rcd.mch_nm = cmb_mch_typ.mch_nm
where usr_id='5201314520' and year(trx_time)=2024
group by mch_typ
order by trx_cnt desc
select mch_typ,count(distinct cmb_usr_trx_rcd.mch_nm) as total_mch , count(distinct cmb_usr_trx_rcd.mch_nm) as unique_mch_cnt
from cmb_usr_trx_rcd left join cmb_mch_typ on cmb_usr_trx_rcd.mch_nm = cmb_mch_typ.mch_nm
group by mch_typ
order by unique_mch_cnt desc
select distinct a.mch_nm_1 as asshole_tried,
trx_cnt,
mch_nm_2 as darling_tried
from
(select distinct mch_nm as mch_nm_1, count(1) as trx_cnt
from cmb_usr_trx_rcd
where usr_id = '5201314520' and year(trx_time) in (2023, 2024)
group by mch_nm_1
having count(trx_amt) >=20 ) as a
left join
(select mch_nm as mch_nm_2
from cmb_usr_trx_rcd
where usr_id = '5211314521' and year(trx_time) in (2023, 2024) ) as b
on a.mch_nm_1 = b.mch_nm_2
order by trx_cnt desc