with start_end_loc_map as (
select 
id
,user_id
,start_time
,end_time
,start_loc
,end_loc
from hello_bike_riding_rcd
where (start_loc in (select loc_nm from gd_loc_map where loc_type = '写字楼') and end_loc in (select loc_nm from gd_loc_map where loc_type = '地铁站')) 
or (start_loc in (select loc_nm from gd_loc_map where loc_type = '地铁站') and end_loc in (select loc_nm from gd_loc_map where loc_type = '写字楼')) 
),
start_end_time as (
select
id
,user_id
,date_format(start_time,'%Y%m') as start_time
,date_format(end_time,'%Y%m') as end_time
,start_loc
,end_loc
from start_end_loc_map
),
riding_times_count as (
select
user_id
,start_time
,count(start_time) as riding_times_count
from start_end_time
group by
user_id
,start_time
having count(start_time) >= 5
order by
user_id
,start_time
),
continous_record as (
select
user_id
,start_time
,lag(start_time,1) over (partition by user_id order by start_time ) as previous_one_record
,lag(start_time,2) over (partition by user_id order by start_time ) as previous_two_record
from riding_times_count
),
active_customer as (
select
 distinct user_id
 ,period_diff (start_time, previous_one_record) as monthdifference1
 ,period_diff (start_time, previous_two_record) as monthdifference2
from continous_record
where period_diff (start_time, previous_one_record) = 1 and period_diff (start_time, previous_two_record) = 2
)
select
distinct user_id
,case when hello_bike_riding_rcd.user_id in (select user_id from active_customer) then 1
else 0
 end as active_tag
from hello_bike_riding_rcd
order by user_id;

select user_id,
			case when MaxConsecutiveMonth <3 then 0 else 1 end as active_tag
from (
select user_id, 
		 max(ConsecutiveMonth) as MaxConsecutiveMonth
from (
select user_id,
		 grp,
 count(month1) as ConsecutiveMonth
from (
select user_id,
		 month as month1,
 row_number() over(partition by user_id order by month) as rn,
 month - interval row_number() over(partition by user_id order by month) month as grp
from (
select user_id,
		 date_format(start_time,'%Y-%m-01') as month,
 count(start_time) as monthcommutecnt
from hello_bike_riding_rcd 
where year(start_time) between 2020 and 2024
and (
(start_loc in (select loc_nm from gd_loc_map where loc_type='地铁站') and 
end_loc in (select loc_nm from gd_loc_map where loc_type='写字楼')
 ) 
		or (start_loc in (select loc_nm from gd_loc_map where loc_type='写字楼') and 
end_loc in (select loc_nm from gd_loc_map where loc_type='地铁站')
 )
)
group by 1,2
having count(start_time)>=5 
order by 1,2) t1 
) t2
group by user_id,grp 
order by user_id,grp ) t3
group by user_id 
order by user_id 
) t4

with UserFirstLog as (
select usr_id,
			min(date(login_time)) as first_login_date
from user_login_log
where year(login_time)=2024
group by usr_id
),
		DailyAllUserCnt as(
select date(login_time) as date,
			count(distinct usr_id) as dailyttlcnt
from user_login_log
where year(login_time)=2024
group by 1
order by 1),
		DailyUserLog as(
select distinct usr_id,date(login_time) as date,1 as tag 
from user_login_log
where date(login_time) between '2023-12-28' and '2024-12-31'
order by 1,2),
DailyNewUserCnt as(
select first_login_date,count(usr_id) as dailyNewUserCnt
from UserFirstLog
group by 1
order by 1),
		RetentUserCnt as(
select date1,count(distinct usr_id1) as retentusercnt
from(
select u1.date as date1,
			u1.usr_id as usr_id1,
u2.date as date2,
u2.usr_id as usr_id2
from DailyUserLog u1
left join DailyUserLog u2 
	on u1.usr_id=u2.usr_id and u2.date between u1.date - interval 3 day and u1.date - interval 1 day
where year(u1.date)=2024
order by u1.date,u1.usr_id) t1 
where usr_id2 is not null
group by date1),
		BackUserCnt as(
select date1, 
		count(distinct usr_id1) as BackUserCnt
from (
select u1.date as date1,
			u1.usr_id as usr_id1,
u2.date as date2,
u2.usr_id as usr_id2
from DailyUserLog u1
left join DailyUserLog u2 
	on u1.usr_id=u2.usr_id and u2.date < u1.date - interval 4 day
where year(u1.date)=2024
and u2.usr_id is not null
order by u1.date,u1.usr_id) t2 
group by date1) 
select date as login_date,
			concat(NewUserRate,', ',RetenUserRate,', ',BackUserRate) as pct
from (
select dauc.date,
dauc.dailyttlcnt as ttlUserCnt,
			coalesce(dnuc.dailyNewUserCnt,0) as NewUserCnt,
round(coalesce(dnuc.dailyNewUserCnt,0)/dauc.dailyttlcnt*100,2) as NewUserRate,
coalesce(ruc.retentusercnt,0) as retenUserCnt,
round(coalesce(ruc.retentusercnt,0)/dauc.dailyttlcnt*100,2) as RetenUserRate,
coalesce(buc.BackUserCnt,0) as backUserCnt,
round(coalesce(buc.BackUserCnt,0)/dauc.dailyttlcnt*100,2) as BackUserRate
from DailyAllUserCnt dauc
left join DailyNewUserCnt dnuc on dauc.date=dnuc.first_login_date
left join RetentUserCnt ruc on dauc.date=ruc.date1
left join BackUserCnt buc on dauc.date=buc.date1) t3

with MchOrderList as(
select distinct(mch_nm) as merchant_name, substring_index(mch_nm,'-',-1) as MchAbbr
from ccb_trx_rcd)
select merchant_name,
			case when MchAbbr like '%拼多多%' or MchAbbr like '%寻梦%' then '拼多多'
		when MchAbbr like '%京东%' then '京东' 
when MchAbbr like '%淘宝%'or MchAbbr like '%天猫%' or MchAbbr like '%阿里巴巴%' then '淘宝'
when MchAbbr like '%抖音%' or MchAbbr like '%字节跳动%' then '抖音'
when MchAbbr like '%小红书%' or MchAbbr like '%行吟%' then '小红书'
else '其他'
end as platform
from MchOrderList
order by 2 desc

with DistinctMchNm as (
select distinct(substring_index(mch_nm,'-',-1)) as mch_nm
from ccb_trx_rcd)
select mch_nm as merchant_name,
			case when (mch_nm like '%寻梦%' or mch_nm like '%拼多多%') then '拼多多'
		when mch_nm like '%京东%' then '京东' 
when (mch_nm like '%淘宝%'ormch_nm like '%天猫%' or mch_nm like '%阿里巴巴%') then '淘宝'
when (mch_nm like '%抖音%' or mch_nm like '%字节跳动%') then '抖音'
when (mch_nm like '%小红书%' or mch_nm like '%行吟%') then '小红书'
else '其他'
end as platform
from DistinctMchNm
order by 2 desc

WITH RecentOrder AS (
    SELECT cust_uid, DATEDIFF(CURRENT_DATE(), MAX(trx_dt)) AS recency_days
    FROM mt_trx_rcd_f
    GROUP BY cust_uid
),
ActiveDaysFrequency AS (
    SELECT cust_uid, COUNT(DISTINCT DATE(trx_dt)) AS frequency_days
    FROM mt_trx_rcd_f
    GROUP BY cust_uid
),
AverageSpending AS (
    SELECT cust_uid, AVG(trx_amt) AS avg_monetary
    FROM mt_trx_rcd_f
    GROUP BY cust_uid
),
CalculateRecencyScores AS (
    SELECT 
        cust_uid AS user_id,
        NTILE(3) OVER (ORDER BY recency_days DESC) AS recency_score 
    FROM RecentOrder
),
CalculateFrequencyScores AS (
    SELECT 
        cust_uid AS user_id,
        NTILE(3) OVER (ORDER BY frequency_days) AS frequency_score
    FROM ActiveDaysFrequency
),
CalculateMonetaryScores AS (
    SELECT 
        cust_uid AS user_id,
        NTILE(3) OVER (ORDER BY avg_monetary) AS monetary_score
    FROM AverageSpending
),
UserRfmScores AS (
    SELECT 
        crs.user_id,
        crs.recency_score + cfs.frequency_score + cms.monetary_score AS total_score
    FROM CalculateRecencyScores crs
    JOIN CalculateFrequencyScores cfs ON crs.user_id = cfs.user_id
    JOIN CalculateMonetaryScores cms ON crs.user_id = cms.user_id
),
HighValueUsers AS (
    SELECT user_id
    FROM UserRfmScores
    WHERE total_score >= 7
),
FavoriteMerchantTypes AS (
    SELECT 
        mtrf.mch_typ2,
        COUNT(*) AS visit_count
    FROM mt_trx_rcd_f mtrf
    WHERE mtrf.cust_uid IN (SELECT user_id FROM HighValueUsers)
    GROUP BY mtrf.mch_typ2
)
SELECT 
    mch_typ2,
    visit_count
FROM FavoriteMerchantTypes
ORDER BY visit_count DESC;

WITH RecentOrder AS (
    SELECT cust_uid, DATEDIFF(CURRENT_DATE(), MAX(trx_dt)) AS recency_days
    FROM mt_trx_rcd_f
    GROUP BY cust_uid
),
ActiveDaysFrequency AS (
    SELECT cust_uid, COUNT(DISTINCT DATE(trx_dt)) AS frequency_days
    FROM mt_trx_rcd_f
    GROUP BY cust_uid
),
CalculateRecencyScores AS (
    SELECT 
        cust_uid AS user_id,
        NTILE(3) OVER (ORDER BY recency_days DESC) AS recency_score
    FROM RecentOrder
),
CalculateFrequencyScores AS (
    SELECT 
        cust_uid AS user_id,
        NTILE(3) OVER (ORDER BY frequency_days) AS frequency_score
    FROM ActiveDaysFrequency
),
UserRfmScores AS (
    SELECT 
        crs.user_id,
        crs.recency_score,
        cfs.frequency_score
    FROM CalculateRecencyScores crs
    JOIN CalculateFrequencyScores cfs ON crs.user_id = cfs.user_id
),
HighRiskUsers AS (
    SELECT user_id
    FROM UserRfmScores
    WHERE recency_score = 1 AND frequency_score <= 2
),
MerchantTypeDistribution AS (
    SELECT 
        mtrf.mch_typ2,
        COUNT(*) AS cnt
    FROM mt_trx_rcd_f mtrf
    WHERE mtrf.cust_uid IN (SELECT user_id FROM HighRiskUsers)
    GROUP BY mtrf.mch_typ2
)
SELECT 
    mch_typ2,
    cnt
FROM MerchantTypeDistribution
ORDER BY cnt DESC;

WITH RecentOrder AS (
    SELECT cust_uid, DATEDIFF(CURRENT_DATE(), MAX(trx_dt)) AS recency_days
    FROM mt_trx_rcd_f
    GROUP BY cust_uid
),
ActiveDaysFrequency AS (
    SELECT cust_uid, COUNT(DISTINCT DATE(trx_dt)) AS frequency_days
    FROM mt_trx_rcd_f
    GROUP BY cust_uid
),
AverageSpending AS (
    SELECT cust_uid, AVG(trx_amt) AS avg_monetary
    FROM mt_trx_rcd_f
    GROUP BY cust_uid
),
CalculateRecencyScores AS (
    SELECT 
        cust_uid AS user_id,
        NTILE(3) OVER (ORDER BY recency_days DESC) AS recency_score 
    FROM RecentOrder
),
CalculateFrequencyScores AS (
    SELECT 
        cust_uid AS user_id,
        NTILE(3) OVER (ORDER BY frequency_days) AS frequency_score
    FROM ActiveDaysFrequency
),
CalculateMonetaryScores AS (
    SELECT 
        cust_uid AS user_id,
        NTILE(3) OVER (ORDER BY avg_monetary) AS monetary_score
    FROM AverageSpending
),
UserRfmScores AS (
    SELECT 
        crs.user_id,
        crs.recency_score + cfs.frequency_score + cms.monetary_score AS total_score
    FROM CalculateRecencyScores crs
    JOIN CalculateFrequencyScores cfs ON crs.user_id = cfs.user_id
    JOIN CalculateMonetaryScores cms ON crs.user_id = cms.user_id
),
RfmScoreDistribution AS (
    SELECT 
        CASE 
                WHEN total_score < 5 THEN '低价值用户'
                WHEN total_score BETWEEN 6 AND 8 THEN '中价值用户'
                ELSE '高价值用户'
             END AS score_range,
        COUNT(*) AS cnt
    FROM UserRfmScores
    GROUP BY CASE 
                WHEN total_score < 5 THEN '低价值用户'
                WHEN total_score BETWEEN 6 AND 8 THEN '中价值用户'
                ELSE '高价值用户'
             END
)
SELECT 
    score_range,
    CONCAT(REPEAT('■', cnt), ' ', cnt) AS user_distribution
FROM RfmScoreDistribution

WITH RecentOrder AS (
    SELECT cust_uid, DATEDIFF(CURRENT_DATE(), MAX(trx_dt)) AS recency_days
    FROM mt_trx_rcd_f
    GROUP BY cust_uid
),
ActiveDaysFrequency AS (
    SELECT cust_uid, COUNT(DISTINCT DATE(trx_dt)) AS frequency_days
    FROM mt_trx_rcd_f
    GROUP BY cust_uid
),
AverageSpending AS (
    SELECT cust_uid, AVG(trx_amt) AS avg_monetary
    FROM mt_trx_rcd_f
    GROUP BY cust_uid
),
CalculateRecencyScores AS (
    SELECT 
        cust_uid AS user_id,
        NTILE(3) OVER (ORDER BY recency_days DESC) AS recency_score 
    FROM RecentOrder
),
CalculateFrequencyScores AS (
    SELECT 
        cust_uid AS user_id,
        NTILE(3) OVER (ORDER BY frequency_days) AS frequency_score
    FROM ActiveDaysFrequency
),
CalculateMonetaryScores AS (
    SELECT 
        cust_uid AS user_id,
        NTILE(3) OVER (ORDER BY avg_monetary) AS monetary_score
    FROM AverageSpending
),
UserRfmScores AS (
    SELECT 
        crs.user_id,
        crs.recency_score,
        cfs.frequency_score,
        cms.monetary_score
    FROM CalculateRecencyScores crs
    JOIN CalculateFrequencyScores cfs ON crs.user_id = cfs.user_id
    JOIN CalculateMonetaryScores cms ON crs.user_id = cms.user_id
)
SELECT 
    user_id,
    recency_score,
    frequency_score,
    monetary_score
FROM UserRfmScores
WHERE recency_score = 1 AND frequency_score <= 2
order by user_id

WITH RecentOrder AS (
    SELECT cust_uid, DATEDIFF(CURRENT_DATE(), MAX(trx_dt)) AS recency_days
    FROM mt_trx_rcd_f
    GROUP BY cust_uid
),
ActiveDaysFrequency AS (
    SELECT cust_uid, COUNT(DISTINCT DATE(trx_dt)) AS frequency_days
    FROM mt_trx_rcd_f
    GROUP BY cust_uid
),
AverageSpending AS (
    SELECT cust_uid, AVG(trx_amt) AS avg_monetary
    FROM mt_trx_rcd_f
    GROUP BY cust_uid
),
CalculateRecencyScores AS (
    SELECT 
        cust_uid AS user_id,
        NTILE(3) OVER (ORDER BY recency_days DESC) AS recency_score 
    FROM RecentOrder
),
CalculateFrequencyScores AS (
    SELECT 
        cust_uid AS user_id,
        NTILE(3) OVER (ORDER BY frequency_days) AS frequency_score
    FROM ActiveDaysFrequency
),
CalculateMonetaryScores AS (
    SELECT 
        cust_uid AS user_id,
        NTILE(3) OVER (ORDER BY avg_monetary) AS monetary_score
    FROM AverageSpending
),
UserRfmScores AS (
    SELECT 
        crs.user_id,
        CONCAT('R', crs.recency_score, 'F', cfs.frequency_score, 'M', cms.monetary_score) AS rfm_category
    FROM CalculateRecencyScores crs
    JOIN CalculateFrequencyScores cfs ON crs.user_id = cfs.user_id
    JOIN CalculateMonetaryScores cms ON crs.user_id = cms.user_id
),
RfmCategoryCount AS (
    SELECT 
        rfm_category,
        COUNT(*) AS user_count
    FROM UserRfmScores
    GROUP BY rfm_category
)
SELECT 
    rfm_category,
    user_count
FROM RfmCategoryCount
ORDER BY rfm_category;

WITH RecentOrder AS (
    SELECT cust_uid, DATEDIFF(CURRENT_DATE(), MAX(trx_dt)) AS recency_days
    FROM mt_trx_rcd_f
    GROUP BY cust_uid
),
ActiveDaysFrequency AS (
    SELECT cust_uid, COUNT(DISTINCT DATE(trx_dt)) AS frequency_days
    FROM mt_trx_rcd_f
    GROUP BY cust_uid
),
AverageSpending AS (
    SELECT cust_uid, AVG(trx_amt) AS avg_monetary
    FROM mt_trx_rcd_f
    GROUP BY cust_uid
),
CalculateRecencyScores AS (
    SELECT 
        cust_uid AS user_id,
        NTILE(3) OVER (ORDER BY recency_days DESC) AS recency_score 
    FROM RecentOrder
),
CalculateFrequencyScores AS (
    SELECT 
        cust_uid AS user_id,
        NTILE(3) OVER (ORDER BY frequency_days) AS frequency_score
    FROM ActiveDaysFrequency
),
CalculateMonetaryScores AS (
    SELECT 
        cust_uid AS user_id,
        NTILE(3) OVER (ORDER BY avg_monetary) AS monetary_score
    FROM AverageSpending
),
UserRfmScores AS (
    SELECT 
        crs.user_id,
        crs.recency_score + cfs.frequency_score + cms.monetary_score AS total_score,
        crs.recency_score,
        cfs.frequency_score,
        cms.monetary_score
    FROM CalculateRecencyScores crs
    JOIN CalculateFrequencyScores cfs ON crs.user_id = cfs.user_id
    JOIN CalculateMonetaryScores cms ON crs.user_id = cms.user_id
),
LowValueUsers AS (
    SELECT 
        user_id,
        recency_score,
        frequency_score,
        monetary_score
    FROM UserRfmScores
    WHERE total_score <= 4
),
LastRestaurantVisit AS (
    SELECT 
        mtrf.cust_uid,
        mtrf.mch_nm AS last_restaurant,
        ROW_NUMBER() OVER (PARTITION BY mtrf.cust_uid ORDER BY mtrf.trx_dt DESC) AS visit_rank
    FROM mt_trx_rcd_f mtrf
    WHERE mtrf.cust_uid IN (SELECT user_id FROM LowValueUsers)
)
SELECT 
    lvu.user_id,
    lvu.recency_score,
    lvu.frequency_score,
    lvu.monetary_score,
    lrv.last_restaurant
FROM LowValueUsers lvu
LEFT JOIN LastRestaurantVisit lrv ON lvu.user_id = lrv.cust_uid AND lrv.visit_rank = 1
order by user_id

WITH RecentOrder AS (
    SELECT cust_uid, DATEDIFF(CURRENT_DATE(), MAX(trx_dt)) AS recency_days
    FROM mt_trx_rcd_f
    GROUP BY cust_uid
),
ActiveDaysFrequency AS (
    SELECT cust_uid, COUNT(DISTINCT DATE(trx_dt)) AS frequency_days
    FROM mt_trx_rcd_f
    GROUP BY cust_uid
),
AverageSpending AS (
    SELECT cust_uid, AVG(trx_amt) AS avg_monetary
    FROM mt_trx_rcd_f
    GROUP BY cust_uid
),
CalculateRecencyScores AS (
    SELECT 
        cust_uid AS user_id,
        NTILE(3) OVER (ORDER BY recency_days DESC) AS recency_score 
    FROM RecentOrder
),
CalculateFrequencyScores AS (
    SELECT 
        cust_uid AS user_id,
        NTILE(3) OVER (ORDER BY frequency_days) AS frequency_score
    FROM ActiveDaysFrequency
),
CalculateMonetaryScores AS (
    SELECT 
        cust_uid AS user_id,
        NTILE(3) OVER (ORDER BY avg_monetary DESC) AS monetary_score
    FROM AverageSpending
),
UserRfmScores AS (
    SELECT 
        crs.user_id,
        crs.recency_score + cfs.frequency_score + cms.monetary_score AS total_score,
        crs.recency_score,
        cfs.frequency_score,
        cms.monetary_score
    FROM CalculateRecencyScores crs
    JOIN CalculateFrequencyScores cfs ON crs.user_id = cfs.user_id
    JOIN CalculateMonetaryScores cms ON crs.user_id = cms.user_id
),
HighValueUsers AS (
    SELECT 
        user_id,
        recency_score,
        frequency_score,
        monetary_score
    FROM UserRfmScores
    WHERE total_score = 9
),
Top3Restaurants AS (
    SELECT 
        mtrf.cust_uid,
        mtrf.mch_nm,
        COUNT(*) AS visit_count,
        ROW_NUMBER() OVER (PARTITION BY mtrf.cust_uid ORDER BY COUNT(*) DESC) AS restaurant_rank
    FROM mt_trx_rcd_f mtrf
    WHERE mtrf.cust_uid IN (SELECT user_id FROM HighValueUsers)
    GROUP BY mtrf.cust_uid, mtrf.mch_nm
),
Top3RestaurantsAggregated AS (
    SELECT 
        tr.cust_uid,
        GROUP_CONCAT(tr.mch_nm ORDER BY tr.restaurant_rank SEPARATOR ', ') AS Top3_mch_nm
    FROM Top3Restaurants tr
    WHERE tr.restaurant_rank <= 3
    GROUP BY tr.cust_uid
)
SELECT 
    hvu.user_id,
    hvu.recency_score,
    hvu.frequency_score,
    hvu.monetary_score,
    COALESCE(t3ra.Top3_mch_nm, '无数据') AS Top3_mch_nm
FROM HighValueUsers hvu
LEFT JOIN Top3RestaurantsAggregated t3ra ON hvu.user_id = t3ra.cust_uid;

WITH RecentOrder AS (
    SELECT cust_uid, DATEDIFF(CURRENT_DATE(), MAX(trx_dt)) AS recency_days
    FROM mt_trx_rcd_f
    GROUP BY cust_uid
),
ActiveDaysFrequency AS (
    SELECT cust_uid, COUNT(DISTINCT DATE(trx_dt)) AS frequency_days
    FROM mt_trx_rcd_f
    GROUP BY cust_uid
),
AverageSpending AS (
    SELECT cust_uid, AVG(trx_amt) AS avg_monetary
    FROM mt_trx_rcd_f
    GROUP BY cust_uid
),
CalculateRecencyScores AS (
    SELECT 
        cust_uid AS user_id,
        NTILE(3) OVER (ORDER BY recency_days DESC) AS recency_score 
    FROM RecentOrder
),
CalculateFrequencyScores AS (
    SELECT 
        cust_uid AS user_id,
        NTILE(3) OVER (ORDER BY frequency_days) AS frequency_score
    FROM ActiveDaysFrequency
),
CalculateMonetaryScores AS (
    SELECT 
        cust_uid AS user_id,
        NTILE(3) OVER (ORDER BY avg_monetary DESC) AS monetary_score
    FROM AverageSpending
),
UserRfmScores AS (
    SELECT 
        crs.user_id,
        crs.recency_score + cfs.frequency_score + cms.monetary_score AS total_score,
        crs.recency_score,
        cfs.frequency_score,
        cms.monetary_score
    FROM CalculateRecencyScores crs
    JOIN CalculateFrequencyScores cfs ON crs.user_id = cfs.user_id
    JOIN CalculateMonetaryScores cms ON crs.user_id = cms.user_id
)
SELECT 
    user_id,
    recency_score,
    frequency_score,
    monetary_score
FROM UserRfmScores
WHERE total_score = 9;

WITH RecentOrder AS (
    SELECT cust_uid, DATEDIFF(CURRENT_DATE(), MAX(trx_dt)) AS recency_days
    FROM mt_trx_rcd_f
    GROUP BY cust_uid
),
ActiveDaysFrequency AS (
    SELECT cust_uid, COUNT(DISTINCT DATE(trx_dt)) AS frequency_days
    FROM mt_trx_rcd_f
    GROUP BY cust_uid
),
AverageSpending AS (
    SELECT cust_uid, AVG(trx_amt) AS avg_monetary
    FROM mt_trx_rcd_f
    GROUP BY cust_uid
),
FirstTransaction AS (
    SELECT 
        cust_uid AS user_id,
        DATE_FORMAT(MIN(trx_dt), '%Y-%m') AS first_trx_month 
    FROM mt_trx_rcd_f
    GROUP BY cust_uid
),
CalculateRecencyScores AS (
    SELECT 
        cust_uid AS user_id,
        NTILE(3) OVER (ORDER BY recency_days DESC) AS recency_score 
    FROM RecentOrder
),
CalculateFrequencyScores AS (
    SELECT 
        cust_uid AS user_id,
        NTILE(3) OVER (ORDER BY frequency_days) AS frequency_score
    FROM ActiveDaysFrequency
),
CalculateMonetaryScores AS (
    SELECT 
        cust_uid AS user_id,
        NTILE(3) OVER (ORDER BY avg_monetary DESC) AS monetary_score
    FROM AverageSpending
),
UserRfmScores AS (
    SELECT 
        crs.user_id,
        crs.recency_score + cfs.frequency_score + cms.monetary_score AS total_score,
        ft.first_trx_month
    FROM CalculateRecencyScores crs
    JOIN CalculateFrequencyScores cfs ON crs.user_id = cfs.user_id
    JOIN CalculateMonetaryScores cms ON crs.user_id = cms.user_id
    JOIN FirstTransaction ft ON crs.user_id = ft.user_id
),
UserQuality AS (
    SELECT 
        user_id,
        first_trx_month,
        CASE 
            WHEN total_score >= 9 THEN '高质量'
            WHEN total_score BETWEEN 5 AND 8 THEN '中等质量'
            ELSE '低质量'
        END AS quality
    FROM UserRfmScores
),
MonthlyQualityDistribution AS (
    SELECT 
        first_trx_month,
        quality,
        COUNT(user_id) AS user_count
    FROM UserQuality
    GROUP BY first_trx_month, quality
)
SELECT 
    first_trx_month,
    cast(SUM(CASE WHEN quality = '高质量' THEN user_count ELSE 0 END) * 100.0 / SUM(user_count) as decimal(4,2)) AS "高质量占比",
    cast(SUM(CASE WHEN quality = '中等质量' THEN user_count ELSE 0 END) * 100.0 / SUM(user_count) as decimal(4,2)) AS "中等质量占比",
    cast(SUM(CASE WHEN quality = '低质量' THEN user_count ELSE 0 END) * 100.0 / SUM(user_count) as decimal(4,2)) AS "低质量占比"
FROM MonthlyQualityDistribution
GROUP BY first_trx_month
ORDER BY first_trx_month;

with UserTable as(
select cust_uid as user_id,
		 date_format(min(date(trx_dt)),'%Y-%m') as first_trx_month,
timestampdiff(day,max(date(trx_dt)),curdate()) as recency,
count(distinct date(trx_dt)) as frequency,
avg(trx_amt) as monetary
from mt_trx_rcd_f
group by 1
order by 1 ) 
select user_id,
			ntile(3) over(order by recency desc) as recency_score,
ntile(3) over(order by frequency) as frequency_score,
ntile(3) over(order by monetary desc) as monetary_score,
first_trx_month
from UserTable
order by user_id

WITH RecentOrder AS (
    SELECT cust_uid, DATEDIFF(CURRENT_DATE(), MAX(trx_dt)) AS recency_days
    FROM mt_trx_rcd_f
    GROUP BY cust_uid
),
ActiveDaysFrequency AS (
    SELECT cust_uid, COUNT(DISTINCT DATE(trx_dt)) AS frequency_days
    FROM mt_trx_rcd_f
    GROUP BY cust_uid
),
AverageSpending AS (
    SELECT cust_uid, AVG(trx_amt) AS avg_monetary
    FROM mt_trx_rcd_f
    GROUP BY cust_uid
),
CalculateRecencyScores AS (
    SELECT 
        cust_uid AS user_id,
        NTILE(3) OVER (ORDER BY recency_days DESC) AS recency_score 
    FROM RecentOrder
),
CalculateFrequencyScores AS (
    SELECT 
        cust_uid AS user_id,
        NTILE(3) OVER (ORDER BY frequency_days) AS frequency_score
    FROM ActiveDaysFrequency
),
CalculateMonetaryScores AS (
    SELECT 
        cust_uid AS user_id,
        NTILE(3) OVER (ORDER BY avg_monetary) AS monetary_score
    FROM AverageSpending
)
SELECT 
    crs.user_id,
    crs.recency_score,
    cfs.frequency_score,
    cms.monetary_score
FROM CalculateRecencyScores crs
JOIN CalculateFrequencyScores cfs ON crs.user_id = cfs.user_id
JOIN CalculateMonetaryScores cms ON crs.user_id = cms.user_id
order by crs.user_id

WITH RecentOrder AS (
    SELECT cust_uid, DATEDIFF(CURRENT_DATE(), MAX(trx_dt)) AS recency_days
    FROM mt_trx_rcd_f
    GROUP BY cust_uid
),
TransactionFrequency AS (
    SELECT cust_uid, COUNT(*) AS frequency
    FROM mt_trx_rcd_f
    GROUP BY cust_uid
),
TotalSpending AS (
    SELECT cust_uid, SUM(trx_amt) AS monetary
    FROM mt_trx_rcd_f
    GROUP BY cust_uid
),
CalculateScores AS (
    SELECT 
        ro.cust_uid AS user_id,
        CASE 
            WHEN ro.recency_days > 30 THEN 1
            WHEN ro.recency_days BETWEEN 10 AND 30 THEN 2
            ELSE 3
        END AS recency_score,
        CASE 
            WHEN tf.frequency BETWEEN 1 AND 5 THEN 1
            WHEN tf.frequency BETWEEN 6 AND 15 THEN 2
            ELSE 3
        END AS frequency_score,
        CASE 
            WHEN ts.monetary < 2000 THEN 1
            WHEN ts.monetary BETWEEN 2000 AND 5000 THEN 2
            ELSE 3
        END AS monetary_score
    FROM RecentOrder ro
    JOIN TransactionFrequency tf ON ro.cust_uid = tf.cust_uid
    JOIN TotalSpending ts ON ro.cust_uid = ts.cust_uid
)
SELECT user_id, recency_score, frequency_score, monetary_score 
FROM CalculateScores order by user_id

WITH RecentOrder AS (
    SELECT cust_uid, DATEDIFF(CURRENT_DATE(), MAX(trx_dt)) AS recency_days
    FROM mt_trx_rcd_f
    GROUP BY cust_uid
),
ActiveDaysFrequency AS (
    SELECT cust_uid, COUNT(DISTINCT DATE(trx_dt)) AS frequency
    FROM mt_trx_rcd_f
    GROUP BY cust_uid
),
AverageSpending AS (
    SELECT cust_uid, AVG(trx_amt) AS monetary
    FROM mt_trx_rcd_f
    GROUP BY cust_uid
),
CalculateScores AS (
    SELECT 
        ro.cust_uid AS user_id,
        CASE 
            WHEN ro.recency_days > 30 THEN 1
            WHEN ro.recency_days BETWEEN 10 AND 30 THEN 2
            ELSE 3
        END AS recency_score,
        CASE 
            WHEN adf.frequency BETWEEN 1 AND 10 THEN 1
            WHEN adf.frequency BETWEEN 11 AND 20 THEN 2
            ELSE 3
        END AS frequency_score,
        CASE 
            WHEN avs.monetary < 150 THEN 1
            WHEN avs.monetary BETWEEN 150 AND 250 THEN 2
            ELSE 3
        END AS monetary_score
    FROM RecentOrder ro
    JOIN ActiveDaysFrequency adf ON ro.cust_uid = adf.cust_uid
    JOIN AverageSpending avs ON ro.cust_uid = avs.cust_uid
)
SELECT * FROM CalculateScores order by user_id

with UserTable as(
select cust_uid as user_id,
		max(date(trx_dt)) as date,
count(distinct date(trx_dt)) as frequency,
avg(trx_amt) as monetary
from mt_trx_rcd_f 
group by 1
order by 1) 
select user_id,
		 timestampdiff(day,date,curdate()) as recency,
 frequency,
		monetary
from UserTable

with UserTable as (
select cust_uid as user_id,
			max(date(trx_dt)) as date,
count(trx_dt) as frequency,
sum(trx_amt) as monetary
from mt_trx_rcd_f
group by 1
order by 1)
select user_id,
		 datediff(curdate(),date) as recency,
 frequency,
			monetary
from UserTable
order by user_id