select gd.gd_id, gd.gd_nm, gd.gd_typ,
 case when fav.mch_id is null and pchs.mch_id is null then '未被收藏也未被购买的'
 when fav.mch_id is not null and pchs.mch_id is null then '只被收藏未被购买过的'
 when fav.mch_id is null and pchs.mch_id is not null then '只被购买未被收藏过的'
 else '既被收藏也被购买的' end as category
from gd_inf gd left join xhs_fav_rcd fav on gd.gd_id = fav.mch_id
 left join xhs_pchs_rcd pchs on gd.gd_id = pchs.mch_id
group by gd.gd_id, gd.gd_nm, gd.gd_typ
order by gd.gd_id

select distinct gd.gd_id, gd.gd_nm, gd.gd_typ
from xhs_pchs_rcd pchs left join xhs_fav_rcd fav on pchs.mch_id = fav.mch_id
 left join gd_inf gd on pchs.mch_id = gd.gd_id
where fav.mch_id is null

select gd.gd_id, gd.gd_nm, gd.gd_typ
from xhs_pchs_rcd pchs left join xhs_fav_rcd fav on pchs.mch_id = fav.mch_id
 left join gd_inf gd on pchs.mch_id = gd.gd_id
where fav.mch_id is null

select gd.gd_id, gd.gd_nm, gd.gd_typ
from xhs_fav_rcd fav left join gd_inf gd on gd.gd_id = fav.mch_id
 left join xhs_pchs_rcd pchs on gd.gd_id = pchs.mch_id
where pchs.mch_id is null
group by gd.gd_id, gd.gd_nm, gd.gd_typ

select gd.gd_typ, count(distinct pchs.cust_uid) as buyer_count
from gd_inf gd join xhs_pchs_rcd pchs on gd.gd_id = pchs.mch_id
group by gd.gd_typ
order by buyer_count desc
limit 1

select gd.gd_id, gd.gd_nm, count(fav_trq) as fav_count
from xhs_fav_rcd fav join gd_inf gd on gd.gd_id = fav.mch_id
group by gd.gd_id, gd.gd_nm
order by fav_count desc
limit 1

select gd.gd_id, gd.gd_nm, count(fav_trq) as fav_count
from xhs_fav_rcd fav join gd_inf gd on gd.gd_id = fav.mch_id
group by gd.gd_id, gd.gd_nm
order by fav_count desc

with count_all as(
select 
'all' as mch_typ,
mch_nm,
count(trx_amt) as trx_cnt,
dense_rank()over(order by count(trx_amt) desc) as rnk
from cmb_usr_trx_rcd
where usr_id = 5201314520
group by mch_nm
),
count_each as(
select 
 b.mch_typ,
 b.mch_nm,
 count(trx_amt) as trx_cnt,
 dense_rank()over(partition by mch_typ order by count(trx_amt) desc )as rnk
from cmb_usr_trx_rcd a join cmb_mch_typ b on a.mch_nm = b.mch_nm
where usr_id = 5201314520 and b.mch_typ in ('交通出行','休闲娱乐','咖啡奶茶')
group by b.mch_typ, b.mch_nm),
allrnnk as(
select 
mch_typ,
mch_nm,
trx_cnt,
rnk
from count_all
where rnk<=1
),
eachrnk as(
select 
mch_typ,
mch_nm,
trx_cnt,
rnk
from count_each
where rnk <= 1
)
select 
mch_typ,
mch_nm,
trx_cnt,
rnk
from allrnnk
union all
select
mch_typ,
mch_nm,
trx_cnt,
rnk
from eachrnk
order by mch_typ, rnk, mch_nm, trx_cnt

with yearly_rn as(
select 
 '2024' as trx_mon,
 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_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
),
monthly_rn as 
(select 
 trx_mon, mch_nm, sum_trx_amt
from monthly_merchants
where rn <= 3
)
select trx_mon, mch_nm, sum_trx_amt
from yearly_rn
union all
select trx_mon, mch_nm, sum_trx_amt
from monthly_rn

with first_time as(
select min(trx_time) as first_trx_time
from cmb_usr_trx_rcd
where usr_id = 5201314520 and mch_nm like '%红玫瑰%'
)
select usr_id, mch_nm, trx_time, trx_amt
from cmb_usr_trx_rcd
where usr_id = 5201314520 and trx_time between (select first_trx_time from first_time) and (select date_add(first_trx_time,interval 2 hour) from first_time)
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 = '红玫瑰按摩保健休闲'

select '2022-10-03 05: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 = '红玫瑰按摩保健休闲'

select '2022-10-03 05:20:20' as time_he_love_me,
 datediff(current_date,'2022-10-03') as days_we_falling_love,
 timestampdiff(hour,'2022-10-03 05: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 = '红玫瑰按摩保健休闲'

select '2022-10-03 05:20:20' as time_he_love_me,
 datediff(current_date,'2022-10-03') as days_we_falling_love,
 timestampdiff(hour,'2022-10-03 05:20:20',now()) as hours_we_falling_love,
 datediff(min(trx_time),current_date) as days_he_fvck_else
from cmb_usr_trx_rcd
where usr_id = 5201314520 and mch_nm = '红玫瑰保健休闲'

select u.trx_mon,
 coalesce(t.last_day,'1900-01-01') as last_day,
 coalesce(t.day_of_mon,0) as day_of_mon,
 coalesce(t.trx_amt,0) as trx_amt,
 coalesce(t.trx_cnt,0) as trx_cnt,
 coalesce(round(t.avg_day_amt,2),0) as avg_day_amt,
 coalesce(round(t.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')u 
left join
(select date_format(trx_time,'%Y-%m') 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(last_day(max(trx_time))) as avg_day_amt,
 count(trx_amt)/day(last_day(max(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
wherea.usr_id = 5201314520
       AND DATE_FORMAT(a.trx_time, '%Y-%m') BETWEEN '2023-01' AND '2024-06'
       AND (b.mch_typ = '休闲娱乐' OR b.mch_typ IS NULL)
       AND HOUR(a.trx_time) IN (23, 0, 1, 2)
       AND a.trx_amt >= 288
group by trx_mon
order by trx_mon)t 
on u.trx_mon = t. trx_mon
order by u.trx_mon

select u.trx_mon,
 coalesce(t.last_day,'1900-01-01') as last_day,
 coalesce(t.day_of_mon,0) as day_of_mon,
 coalesce(t.trx_amt,0) as trx_amt,
 coalesce(t.trx_cnt,0) as trx_cnt,
 coalesce(round(t.avg_day_amt,2),0) as avg_day_amt,
 coalesce(round(t.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')u 
left join
(select date_format(trx_time,'%Y-%m') 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(last_day(max(trx_time))) as avg_day_amt,
 count(trx_amt)/day(last_day(max(trx_time))) as avg_day_cnt
from cmb_usr_trx_rcd a join cmb_mch_typ b on a.mch_nm = b.mch_nm
wherea.usr_id = 5201314520
       AND DATE_FORMAT(a.trx_time, '%Y-%m') BETWEEN '2023-01' AND '2024-06'
       AND (b.mch_typ = '休闲娱乐' OR b.mch_typ IS NULL)
       AND HOUR(a.trx_time) IN (23, 0, 1, 2)
       AND a.trx_amt >= 288
group by trx_mon
order by trx_mon)t 
on u.trx_mon = t. trx_mon
order by u.trx_mon

select u.trx_mon,
 coalesce(t.last_day,'1900-01-01') as last_day,
 coalesce(t.day_of_mon,0) as day_of_mon,
 coalesce(t.trx_amt,0) as trx_amt,
 coalesce(t.trx_cnt,0) as trx_cnt,
 coalesce(round(t.avg_day_amt,2),0) as avg_day_amt,
 coalesce(round(t.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')u 
left join
(select date_format(trx_time,'%Y-%m') 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(last_day(max(trx_time))) as avg_day_amt,
 count(trx_amt)/day(last_day(max(trx_time))) as avg_day_cnt
from cmb_usr_trx_rcd a join cmb_mch_typ b on a.mch_nm = b.mch_nm
where usr_id = 5201314520
and hour(a.trx_time) in (23,0,1,2)
and a.trx_amt > 288
and (b.mch_typ = '休闲娱乐' or b.mch_typ is null)
and DATE_FORMAT(a.trx_time, '%Y-%m') BETWEEN '2023-01' AND '2024-06'
group by trx_mon
order by trx_mon)t 
on u.trx_mon = t. trx_mon
order by u.trx_mon

select u.trx_mon,
 coalesce(t.last_day,'1900-01-01') as last_day,
 coalesce(t.day_of_mon,0) as day_of_mon,
 coalesce(t.trx_amt,0) as trx_amt,
 coalesce(t.trx_cnt,0) as trx_cnt,
 coalesce(round(t.avg_day_amt,2),0) as avg_day_amt,
 coalesce(round(t.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')u 
left join
(select date_format(trx_time,'%Y-%m') 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(last_day(max(trx_time))) as avg_day_amt,
 count(trx_amt)/day(last_day(max(trx_time))) as avg_day_cnt
from cmb_usr_trx_rcd a join cmb_mch_typ b on a.mch_nm = b.mch_nm
where usr_id = 5201314520
and hour(a.trx_time) in (23,0,1,2)
and a.trx_amt > 288
and (b.mch_typ = '休闲娱乐' or b.mch_typ is null)
and DATE_FORMAT(a.trx_time, '%Y-%m') BETWEEN '2023-01' AND '2024-06'
group by trx_mon
order by trx_mon)t 
on u.trx_mon = t. trx_mon
order by t.trx_mon

select u.trx_mon,
 coalesce(t.last_day,'1900-01-01') as last_day,
 coalesce(t.day_of_mon,0) as day_of_mon,
 coalesce(t.trx_amt,0) as trx_amt,
 coalesce(t.trx_cnt,0) as trx_cnt,
 coalesce(round(t.avg_day_amt,2),0) as avg_day_amt,
 coalesce(round(t.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')u 
left join
(select date_format(trx_time,'%Y-%m') 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(last_day(max(trx_time))) as avg_day_amt,
 count(trx_amt)/day(last_day(max(trx_time))) as avg_day_cnt
from cmb_usr_trx_rcd a join cmb_mch_typ b on a.mch_nm = b.mch_nm
where hour(a.trx_time) in (23,0,1,2)
and a.trx_amt > 288
and (b.mch_typ = '休闲娱乐' or b.mch_typ is null)
and DATE_FORMAT(a.trx_time, '%Y-%m') BETWEEN '2023-01' AND '2024-06'
group by trx_mon
order by trx_mon)t 
on u.trx_mon = t. trx_mon
order by t.trx_mon

select u.trx_mon,
 coalesce(t.last_day,'1900-01-01') as last_day,
 coalesce(t.day_of_mon,0) as day_of_mon,
 coalesce(t.trx_amt,0) as trx_amt,
 coalesce(t.trx_cnt,0) as trx_cnt,
 coalesce(t.avg_day_amt,0) as avg_day_amt,
 coalesce(t.avg_day_cnt,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')u 
left join
(select date_format(trx_time,'%Y-%m') 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(last_day(max(trx_time))) as avg_day_amt,
 count(trx_amt)/day(last_day(max(trx_time))) as avg_day_cnt
from cmb_usr_trx_rcd a join cmb_mch_typ b on a.mch_nm = b.mch_nm
where hour(trx_time) in (23,0,1,2)
and trx_amt >= 288
and (b.mch_typ = '休闲娱乐' or b.mch_typ is null)
and DATE_FORMAT(a.trx_time, '%Y-%m') BETWEEN '2023-01' AND '2024-06'
group by trx_mon
order by trx_mon)t 
on u.trx_mon = t. trx_mon
order by t.trx_mon