with flag as (
select usr_id,live_id,1 as flag,enter_time as time1
from ks_live_t1
union all
select usr_id,live_id,-1 as flag,leave_time as time1
from ks_live_t1
),
base as(
select live_id,time1,sum(flag) over(partition by live_id order by time1) as cum
from flag
order by live_id,time1),
peak as(
select live_id,max(cum) as peak_cum
from base 
group by 1)
select b.live_id,k.live_nm,p.peak_cum as max_online_users,min(b.time1) as first_peak_time,
max(b.time1) as last_peak_time
from base b
inner join peak p on b.live_id=p.live_id and b.cum=p.peak_cum
inner join ks_live_t2 k on b.live_id=k.live_id
group by 1,2,3
order by 3 desc

WITH friendship AS (
    SELECT
        r1.snd_usr_id AS usr_id,
        r1.rcv_usr_id AS friend_id
    FROM
        tx_red_pkt_rcd r1
    UNION
    SELECT
        r2.rcv_usr_id AS usr_id,
        r2.snd_usr_id AS friend_id
    FROM
        tx_red_pkt_rcd r2
),
user_friend_count AS (
    SELECT
        u.usr_id,
        COUNT(DISTINCT f.friend_id) AS friend_count
    FROM
        tx_usr_bas_info u
    LEFT JOIN
        friendship f ON u.usr_id = f.usr_id
    GROUP BY
        u.usr_id
),
total_users AS (
    SELECT
        COUNT(DISTINCT usr_id) AS total_user_count
    FROM
        tx_usr_bas_info
)
SELECT
    ufc.usr_id,
    ufc.friend_count,
    tu.total_user_count,
    round(ufc.friend_count / tu.total_user_count,4) AS popularity
FROM
    user_friend_count ufc,
    total_users tu
ORDER BY
    popularity DESC;

with base as(
select snd_usr_id as u1, rcv_usr_id as u2
from tx_red_pkt_rcd 
union 
select rcv_usr_id as u1, snd_usr_id as u2
from tx_red_pkt_rcd 
)
select u1 as usr_id, count(u2) as friend_count,
(select count(distinct usr_id) from tx_usr_bas_info) as total_user_count, 
round(count(u2)/(select count(distinct usr_id) from tx_usr_bas_info),4)as popularity
from base
group by u1 
order by 4 desc

with base as(
select snd_usr_id as u1, rcv_usr_id as u2
from tx_red_pkt_rcd 
union 
select rcv_usr_id as u1, snd_usr_id as u2
from tx_red_pkt_rcd 
)
select u1 as usr_id, count(u2) as friend_count,
(select count(distinct usr_id) from tx_usr_bas_info) as total_user_count, 
count(u2)/(select count(distinct usr_id) from tx_usr_bas_info)as popularity
from base
group by u1 
order by 4 desc

with base as(
select snd_usr_id as u1, rcv_usr_id as u2
from tx_red_pkt_rcd where date(snd_datetime) = '2021-02-13' and date(rcv_datetime)!='1900-01-01'
union 
select rcv_usr_id as u1, snd_usr_id as u2
from tx_red_pkt_rcd where date(snd_datetime) = '2021-02-13' and date(rcv_datetime)!='1900-01-01'
)
select u1 as usr_id, count(u2) as friend_count,
(select count(distinct usr_id) from tx_usr_bas_info) as total_user_count, 
count(u2)/(select count(distinct usr_id) from tx_usr_bas_info)as popularity
from base
group by u1 
order by 4 desc

with dt as (
select live_id, enter_time dt ,1 as flag from ks_live_t1
union all 
select live_id,leave_time dt,-1 as flag from ks_live_t1
order by 1),
 base as (
select dt.live_id,dt2.live_nm,sum(flag) over(partition by live_id order by dt) as cum 
from dt 
join ks_live_t2 dt2 on dt.live_id = dt2.live_id)
select live_id,live_nm,max(cum)
from base
group by 1,2
order by 3 desc

with avg_h as (
SELECT year(dt) as yr, avg(tmp_h) as avg_h
from weather_rcd_china
where city='shenzhen'
and dt between '2010-01-01' and '2022-12-31'
group by 1
order by 1),
flag as (
select yr,avg_h,lag(avg_h) over(order by yr) as l_avg_h,
 coalesce(abs(avg_h-lag(avg_h) over(order by yr)),0) as var,
 case when coalesce(abs(avg_h-lag(avg_h) over(order by yr)),0)>=1 then 1 else 0 end as flag
from avg_h
order by yr) 
select yr as year, round(avg_h,2) as avg_tmp_h, case when flag =1 then 'Yes' ELSE 'No' end as significant_change
from flag 
order by 1

select user_id,count(distinct ip_address) as cnt from user_submit_record group by user_id having count(distinct ip_address)>=2 order by 2 desc,1 asc

select user_id,count(ip_address) as cnt from user_submit_record group by user_id having count(ip_address)>=2 order by 2 desc,1 asc

select user_id,count(ip_address) as cnt from user_submit_record group by user_id having count(ip_address)>=2 order by user_id

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;