with daily_unique_login as (
select usr_id, date(login_time) as login_date
from user_login_log
where login_time >= date_sub(current_date, interval 30 day)
group by usr_id, date(login_time)
),
retention_days as(
select dul1.usr_id, dul1.login_date as first_login_date, dul2.login_date as next_day_login_date,
datediff(dul2.login_date, dul1.login_date) as days_diff
from daily_unique_login dul1 left join daily_unique_login dul2
	on dul1.usr_id=dul2.usr_id and
dul2.login_date between dul1.login_date + interval 1 day and dul1.login_date + interval 14 day
)
SELECT
    first_login_date,
    ROUND(COUNT(CASE WHEN days_diff = 1 THEN usr_id END) * 100.0 / COUNT(distinct usr_id), 2) AS t_plus_1_retention_rate,
    ROUND(COUNT(CASE WHEN days_diff = 3 THEN usr_id END) * 100.0 / COUNT(distinct usr_id), 2) AS t_plus_3_retention_rate,
    ROUND(COUNT(CASE WHEN days_diff = 7 THEN usr_id END) * 100.0 / COUNT(distinct usr_id), 2) AS t_plus_7_retention_rate,
    ROUND(COUNT(CASE WHEN days_diff = 14 THEN usr_id END) * 100.0 / COUNT(distinct usr_id), 2) AS t_plus_14_retention_rate
FROM
    retention_days
GROUP BY
    first_login_date
ORDER BY
    first_login_date;

with daily_unique_login as (
select usr_id, date(login_time) as login_date
from user_login_log
where login_time >= date_sub(current_date, interval 30 day)
group by usr_id, date(login_time)
),
retention_days as(
select dul1.usr_id, dul1.login_date as first_login_date, dul2.login_date as next_day_login_date,
datediff(dul2.login_date, dul1.login_date) as days_diff
from daily_unique_login dul1 left join daily_unique_login dul2
	on dul1.usr_id=dul2.usr_id and
dul2.login_date between dul1.login_date + interval 1 day and dul1.login_date + interval 14 day
)
SELECT
    first_login_date,
    ROUND(COUNT(CASE WHEN days_diff = 1 THEN usr_id END) * 100.0 / COUNT(usr_id), 2) AS t_plus_1_retention_rate,
    ROUND(COUNT(CASE WHEN days_diff = 3 THEN usr_id END) * 100.0 / COUNT(usr_id), 2) AS t_plus_3_retention_rate,
    ROUND(COUNT(CASE WHEN days_diff = 7 THEN usr_id END) * 100.0 / COUNT(usr_id), 2) AS t_plus_7_retention_rate,
    ROUND(COUNT(CASE WHEN days_diff = 14 THEN usr_id END) * 100.0 / COUNT(usr_id), 2) AS t_plus_14_retention_rate
FROM
    retention_days
WHERE
    first_login_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY
    first_login_date
ORDER BY
    first_login_date;

with daily_unique_login as (
select usr_id, date(login_time) as login_date
from user_login_log
where login_time >= date_sub(current_date, interval 30 day)
group by usr_id, date(login_time)
),
retention_days as(
select dul1.usr_id, dul1.login_date as first_login_date, dul2.login_date as next_day_login_date,
datediff(dul2.login_date, dul1.login_date) as days_diff
from daily_unique_login dul1 left join daily_unique_login dul2
	on dul1.usr_id=dul2.usr_id and
dul2.login_date between dul1.login_date + interval 1 day and dul1.login_date + interval 14 day
)
SELECT
    first_login_date,
    ROUND(COUNT(CASE WHEN days_diff = 1 THEN usr_id END) * 100.0 / COUNT(DISTINCT usr_id), 2) AS t_plus_1_retention_rate,
    ROUND(COUNT(CASE WHEN days_diff = 3 THEN usr_id END) * 100.0 / COUNT(DISTINCT usr_id), 2) AS t_plus_3_retention_rate,
    ROUND(COUNT(CASE WHEN days_diff = 7 THEN usr_id END) * 100.0 / COUNT(DISTINCT usr_id), 2) AS t_plus_7_retention_rate,
    ROUND(COUNT(CASE WHEN days_diff = 14 THEN usr_id END) * 100.0 / COUNT(DISTINCT usr_id), 2) AS t_plus_14_retention_rate
FROM
    retention_days
WHERE
    first_login_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY
    first_login_date
ORDER BY
    first_login_date;

with T_login as(
select distinct usr_id, date(login_time) as login_date
from user_login_log
where datediff(current_date, date(login_time)) <=30
order by login_date asc
),
data2 as (
select T.usr_id, T.login_date, T_1.login_date as T_1_login_date
from T_login T left join T_login T_1 on T.usr_id = T_1.usr_id
anddatediff(T.login_date, T_1.login_date) = -1
 )
select login_date as first_login_date, 
	concat(round(avg(T_1_login_date is not null)*100, 2), '%') as T1_retention_rate 
from data2
group by login_date
order by login_date asc

with p_ranking as (
select user_id,
	product_type,
row_number()over(partition by user_id order by purchase_time asc) as p_time_rnk
from apple_pchs_rcd
order by user_id
),
rnk_filtered_first as(
select user_id, product_type, p_time_rnk
from p_ranking
where p_time_rnk =1
),
rnk_filtered_second as(
select user_id, product_type, p_time_rnk
from p_ranking
where p_time_rnk =2
),
first_second as(
select f.user_id, f.product_type as product_type_f, s.product_type as product_type_s
from rnk_filtered_first f left join rnk_filtered_second s on f.user_id=s.user_id
)
select user_id,
	case when product_type_f='Mac' and product_type_s ='iPhone' then 1 else 0 end as tag
from first_second

with purchase_time as(
select user_id,
	product_type,
row_number()over(partition by user_id order by purchase_time asc) as purchase_time_rnk
from apple_pchs_rcd
 ),
rnk_first as(
select user_id,
product_type
from purchase_time
where purchase_time_rnk =1
)
select user_id,
	case when product_type='Mac' then 1 else 0 end as tage
from rnk_first

with purchase_time as(
select user_id,
	product_type,
row_number()over(partition by user_id order by purchase_time asc) as purchase_time_rnk
from apple_pchs_rcd
 ),
rnk_first as(
select user_id,
product_type
from purchase_time
where purchase_time_rnk =1
)
select user_id,
	case when product_type='Mac' then 0 else 1 end as tage
from rnk_first

with ctg as(
select r.cust_uid, r.start_loc,r.end_loc, r.start_tm,
	ls.loc_ctg as start_ctg,
le.loc_ctg as end_ctg
from didi_sht_rcd r left join loc_nm_ctg ls on r.start_loc=ls.loc_nm
 left join loc_nm_ctg le on r.end_loc=le.loc_nm
where ls.loc_ctg='酒店'
),
ranking as(
select start_loc, end_loc,end_ctg as loc_ctg, count(start_tm) as trip_count,
	row_number()over(partition by end_ctg order by count(start_tm) desc ) as rnk
from ctg
group by start_loc, end_loc, end_ctg
order by trip_count desc
)
selectstart_loc, end_loc,loc_ctg,trip_count
from ranking
where rnk=1

with ctg as(
select r.cust_uid, r.start_loc,r.end_loc, r.start_tm,
	ls.loc_ctg as start_ctg,
le.loc_ctg as end_ctg
from didi_sht_rcd r left join loc_nm_ctg ls on r.start_loc=ls.loc_nm
 left join loc_nm_ctg le on r.end_loc=le.loc_nm
where ls.loc_ctg='酒店' and le.loc_ctg <>'酒店'
),
ranking as(
select start_loc, end_loc,end_ctg as loc_ctg, count(start_tm) as trip_count,
	row_number()over(partition by end_ctg order by count(start_tm) desc ) as rnk
from ctg
group by start_loc, end_loc, end_ctg
order by trip_count desc
)
selectstart_loc, end_loc,loc_ctg,trip_count
from ranking
where rnk=1

with ctg as(
select r.cust_uid, r.start_loc,r.end_loc, r.start_tm,
	ls.loc_ctg as start_ctg,
le.loc_ctg as end_ctg
from didi_sht_rcd r left join loc_nm_ctg ls on r.start_loc=ls.loc_nm
 left join loc_nm_ctg le on r.end_loc=le.loc_nm
where ls.loc_ctg='酒店'
)
select start_loc, end_loc,end_ctg as loc_ctg, count(start_tm) as trip_count
from ctg
group by start_loc, end_loc, end_ctg
order by trip_count desc

with rnk as(
select date_format(order_time, '%Y-%m-%d') as order_date,
	goods_id,
sum(order_gmv) as total_gmv,
row_number()over(partition by date_format(order_time, '%Y-%m-%d') order by sum(order_gmv) asc) as ranking
from order_info
where year(order_time) =2024 and month(order_time)=10
group by order_date, goods_id
order by order_date asc
)
select *
from rnk
where ranking <=3

with d_table as(
select date_value
from date_table
where year(date_value)=2024 and month(date_value)=9
order by date_value asc
),
categories as(
select date(trx_time) as date_value,
	count(*) as FvckCnt,
sum(case when trx_amt=288 then 1 else 0 end) as WithHand,
sum(case when trx_amt=388 then 1 else 0 end) as WithBalls,
sum(case when trx_amt=588 then 1 else 0 end) as BlowJobbie,
sum(case when trx_amt=888 then 1 else 0 end) as Doi,
sum(case when trx_amt=1288 then 1 else 0 end) as DoubleFly
from cmb_usr_trx_rcd t left join cmb_mch_typ m on t.mch_nm=m.mch_nm
where usr_id=5201314520 and year(trx_time)=2024 and month(trx_time)=9 and t.mch_nm rlike '按摩|保健|休闲|会所'
group by date_value
order by date_value asc
),
special as (
select *, 
	lag(trx_time, 1) over(order by trx_time asc) as last_time,
lag(trx_amt, 1) over(order by trx_time asc) as last_amt
 from cmb_usr_trx_rcd
where usr_id=5201314520 and year(trx_time)=2024 and month(trx_time)=9 and mch_nm rlike '按摩|保健|休闲|会所'
),
Ohya as (
select distinct(date(trx_time)) as special_date
from special
where last_amt = 888 and trx_amt=1288 and (date(trx_time)=date(last_time))
)
select d.date_value,
	ifnull(c.FvckCnt,0) as FvckCnt,
	ifnull(c.WithHand,0) as WithHand,
ifnull(c.WithBalls,0) as WithBalls,
ifnull(c.BlowJobbie,0) as BlowJobbie,
ifnull(c.Doi,0) as Doi,
ifnull(c.DoubleFly,0) as DoubleFly,
case when o.special_date is not null then 1 else 0 end as Ohya
from d_table d left join categories c on d.date_value=c.date_value
	left join Ohya o on d.date_value=o.special_date

with user_trans as(
select usr_id,
    trx_time,
    trx_amt,
    mch_nm,
lag(trx_time,3) over(partition by usr_id order by trx_time asc) as prev_3_records,
	datediff(trx_time,lag(trx_time,3) over(partition by usr_id order by trx_time asc)) as inte_days
from cmb_usr_trx_rcd
where mch_nm = '红玫瑰按摩保健休闲'
 )
select distinct(usr_id)
from user_trans
where inte_days<=3

select usr_id,trx_time, trx_amt, mch_nm,
	lag(trx_time,1) over(order by trx_time),
datediff(trx_time , lag(trx_time,1) over(order by trx_time))
from cmb_usr_trx_rcd
where usr_id=5201314520 and mch_nm='红玫瑰按摩保健休闲'

with monthly_trx as(
select concat(year(trx_time),'-Q', quarter(trx_time)) as trx_quarter,
	sum(case when trx_amt=288 then 1 else 0 end) as Withand,
sum(case when trx_amt=888 then 1 else 0 end) as Doi
from cmb_usr_trx_rcd
where year(trx_time) in (2023, 2024) and usr_id=5201314520 and mch_nm='红玫瑰按摩保健休闲'
group by trx_quarter
order by trx_quarter asc
)
select trx_quarter, 
	sum(Withand) over(order by trx_quarter) as Withhand,
sum(Doi) over(order by trx_quarter) as Doi
from monthly_trx

with monthly_amt as(
select date_format(t.trx_time, '%Y-%m') as trx_mon, sum(t.trx_amt) as trx_amt
from cmb_usr_trx_rcd t left join cmb_mch_typ m on t.mch_nm=m.mch_nm
where usr_id=5201314520 and year(t.trx_time)=2023 and m.mch_typ='休闲娱乐'
group by trx_mon
order by trx_mon asc
),
date_mon as(
select date_format(date_value, '%Y-%m') as dmon
from date_table
where year(date_value)=2023
group by dmon
order by dmon asc
)
select d.dmon, sum(m.trx_amt) over(order by d.dmon) as trx_amt
from date_mon d left join monthly_amt m on d.dmon=m.trx_mon

with monthly_amt as(
select date_format(t.trx_time, '%Y-%m') as trx_mon, sum(trx_amt) as trx_amt
from cmb_usr_trx_rcd t left join cmb_mch_typ m on t.mch_nm=m.mch_nm
where usr_id=5201314520 andyear(t.trx_time) in (2023, 2024) and m.mch_typ='休闲娱乐'
group by trx_mon
order by trx_mon
)
select trx_mon, sum(trx_amt) over(order by trx_mon) as trx_amt
from monthly_amt

with all_rnk as(
select mch_nm, count(trx_time) as trx_cnt
from cmb_usr_trx_rcd
where usr_id=5201314520
group by mch_nm
order by trx_cnt desc
limit 1),
top_type as(
select m.mch_typ, t.mch_nm, count(t.trx_time) as trx_cnt,
row_number()over(partition by m.mch_typ order by count(t.trx_time) desc) as rnk
from cmb_usr_trx_rcd t left join cmb_mch_typ m on t.mch_nm=m.mch_nm
where t.usr_id = 5201314520 and m.mch_typ in ('交通出行','休闲娱乐','咖啡奶茶')
group by m.mch_typ,t.mch_nm
)
select 'all' as mch_typ, mch_nm,trx_cnt, '1' as rnk
from all_rnk
union all
select *
from top_type
where rnk<=1

with yearly_top_mch 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_mch 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)
select '2024' as trx_mon,mch_nm,sum_trx_amt
from yearly_top_mch
union all
select trx_mon,mch_nm,sum_trx_amt
from monthly_top_mch
where rn <4

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