with month_cost as (
select
substr(a.trx_time,1,7) as trx_mon,
sum(a.trx_amt) as trx_amt
from
cmb_usr_trx_rcd a
join
cmb_mch_typ b
on a.mch_nm = b.mch_nm
where
a.usr_id = 5201314520
and b.mch_typ = '休闲娱乐'
and year(a.trx_time) between 2023 and 2024
group by 1
order by 1
),
cul_cost as (
select
trx_mon,
sum(trx_amt)over(order by trx_mon) as cumulative_trx_amt
from
month_cost
)
select
trx_mon,
cumulative_trx_amt as trx_amt
from cul_cost
order by 1
with overall_rank as (
select
'all' as mch_typ,
mch_nm,
count(1) as trx_cnt,
dense_rank()over(order by count(1) desc) as rnk
from cmb_usr_trx_rcd
where usr_id = 5201314520
group by mch_nm
),
part_rank as (
select
b.mch_typ as mch_typ,
b.mch_nm as mch_nm,
count(1) as trx_cnt,
dense_rank()over(partition by b.mch_typ order by count(1) desc) as rnk
from
cmb_usr_trx_rcd a
left join cmb_mch_typ b
on a.mch_nm = b.mch_nm
where
usr_id = 5201314520
group by 1,2
)
select
o.mch_typ as mch_typ,
o.mch_nm as mch_nm,
o.trx_cnt as trx_cnt,
o.rnk as rnk
from overall_rank o
where rnk < 2
union all
select
p.mch_typ as mch_typ,
p.mch_nm as mch_nm,
p.trx_cnt as trx_cnt,
p.rnk as rnk
from part_rank p
where rnk < 2
and p.mch_typ in ('交通出行','休闲娱乐','咖啡奶茶')
with first_fuck as (
select
min(trx_time) as first_fuck_time
from cmb_usr_trx_rcd
where usr_id = 5201314520 and mch_nm = '红玫瑰按摩保健休闲')
select *
from cmb_usr_trx_rcd
where
usr_id = 5201314520
and trx_time between
(select first_fuck_time from first_fuck)
and
(select date_add(first_fuck_time, interval 2 hour) from first_fuck)
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 = '红玫瑰按摩保健休闲'
with real_record as(
select
substr(a.trx_time,1,7) as trx_mon,
last_day(max(a.trx_time)) as last_day,
day(last_day(max(a.trx_time))) as day_of_mon,
sum(trx_amt) as trx_amt,
count(1) as trx_cnt,
round(sum(trx_amt)/day(last_day(max(a.trx_time))),2) as avg_day_amt,
round(count(1)/day(last_day(max(a.trx_time))),2) as avg_day_cnt
from
cmb_usr_trx_rcd a
left join cmb_mch_typ b
on a.mch_nm = b.mch_nm
where
a.usr_id = 5201314520
and (year(a.trx_time) = 2023 or (year(a.trx_time) = 2024 and month(a.trx_time) in (1,2,3,4,5,6)))
and a.trx_amt > 288
and hour(a.trx_time) in (23,0,1,2)
and (b.mch_typ = '休闲娱乐' or b.mch_typ is null)
group by 1
),
mon_table as (
select
distinct(substr(date_value,1,7)) as trx_mon
from date_table
where
year(date_value) = 2023 or (year(date_value) = 2024 and month(date_value) in (1,2,3,4,5,6))
order by 1
)
select
b.trx_mon as trx_mon,
coalesce(a.last_day, '1900-01-01') as last_day,
coalesce(a.day_of_mon, 0) as day_of_mon,
coalesce(a.trx_amt,0) as trx_amt,
coalesce(a.trx_cnt,0) as trx_cnt,
coalesce(a.avg_day_amt,0) as avg_day_amt,
coalesce(a.avg_day_cnt,0) as avg_day_cnt
from
mon_table b
left join real_record a
on a.trx_mon = b.trx_mon
order by 1
select
substr(a.trx_time,1,7) as trx_mon,
last_day(max(a.trx_time)) as last_day,
day(last_day(max(a.trx_time))) as day_of_mon,
sum(trx_amt) as trx_amt,
count(1) as trx_cnt,
sum(trx_amt)/day(last_day(max(a.trx_time))) as avg_day_amt,
count(1)/day(last_day(max(a.trx_time))) as avg_day_cnt
from
cmb_usr_trx_rcd a
left join cmb_mch_typ b
on a.mch_nm = b.mch_nm
where a.usr_id = 5201314520
and year(a.trx_time) in (2023,2024)
and b.mch_typ = '休闲娱乐'
group by 1
order by 1
select substr(trx_time,1,7) as trx_mon ,last_day(max(trx_time)) as last_day, day(last_day(max(trx_time)) ) as day_of_mon
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 year(a.trx_time) in (2023, 2024) and m.mch_typ='休闲娱乐'
group by substr(a.trx_time,1,7)
order by 1
select
substr(trx_time,1,7) as trx_mon,
last_day(max(trx_time)) as last_day,
day(last_day(max(trx_time))) as day_of_mon
from cmb_usr_trx_rcd
where usr_id = 5201314520 and year(trx_time) in (2023,2024)
group by 1
order by 1
select substr(trx_time,1,7) as trx_mon ,last_day(max(trx_time)) as last_day
from cmb_usr_trx_rcd
where usr_id=5201314520 and year(trx_time) in (2023, 2024)
group by substr(trx_time,1,7)
order by 1
select
b.mch_typ as mch_typ,
a.mch_nm as mch_nm,
count(1) as trx_cnt,
sum(a.trx_amt) as trx_amt
from
cmb_usr_trx_rcd a
left join cmb_mch_typ b
on a.mch_nm = b.mch_nm
where a.usr_id = 5201314520 and year(trx_time) = 2024 and b.mch_typ is null
group by 1,2
order by 3 desc
select
b.mch_typ as mch_typ,
count(1) as trx_cnt,
sum(a.trx_amt) as trx_amt
from
cmb_usr_trx_rcd a
left join cmb_mch_typ b
on a.mch_nm=b.mch_nm
where
usr_id = 5201314520
and year(a.trx_time) = 2024
group by 1
order by 2 desc
select
substr(trx_time,1,7) as trx_mon,
count(1) 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,23,1,2)
or
upper(mch_nm) rlike "足疗|保健|按摩|养生|SPA")
group by trx_mon
order by trx_mon
select
substr(trx_time,1,7) as trx_mon,
count(1) 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' or truncate(trx_amt,0) like '%98') and trx_amt >=200
and hour(trx_time) in (23,0,1,2)
or upper(mch_nm) rlike "足疗|保健|按摩|养生|SPA"
group by trx_mon
order by trx_mon
select
case
when (truncate(trx_amt, 0) like '%88' or truncate(trx_amt, 0) like '%98')
and trx_amt >= 200
and (hour(trx_time) in (23,0,1,2,3))
then 'illegal'
else 'other'
end as trx_typ,
count(1) as trx_cnt,
sum(trx_amt) as trx_amt,
count(distinct mch_nm) as mch_cnt
from cmb_usr_trx_rcd
where usr_id = 5201314520
group by trx_typ
order by trx_cnt desc
select *
from cmb_usr_trx_rcd
where date(trx_time) between '2024-06-08' and '2024-06-10'
and (hour(trx_time) in(11,12,18,19))
and mch_nm = '红玫瑰按摩保健休闲'
order by trx_time desc
select *
from cmb_usr_trx_rcd
where usr_id = 5201314520
and
(date(trx_time) between '2024-06-08' and '2024-06-10'
or date(trx_time) between '2024-09-15' and '2024-09-17')
order by trx_time
with month_2023 as (
select date_format(date_value, '%Y-%m') as trx_mon
from date_table
where year(date_table.date_value) = 2023
and month(date_table.date_value) between 1 and 12
group by date_format(date_table.date_value, '%Y-%m')
),
monthly_pay 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_2023 ms
left join monthly_pay mt on ms.trx_mon = mt.trx_mon
)
select
trx_mon,
sum(monthly_trx_amt) over(order by trx_mon) as trx_amt
from combined_trx
order by trx_mon
with monthly_pay 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) between 2023 and 2024
group by
trx_mon
order by
trx_mon
)
select
trx_mon,
sum(monthly_trx_amt) over(order by trx_mon) as trx_amt
from monthly_pay
order by trx_mon