with t1 as (
select cust_uid,mch_id,count(*) as pch_cnt 
from xhs_pchs_rcd
group by cust_uid,mch_id
),
t2 as (
select cust_uid,count(*) as pch_cnt,
round(100*count(case when pch_cnt>1 then 1 end)/count(*),2) as fugoulv
from t1 join gd_inf g on t1.mch_id=g.gd_id 
group by cust_uid
),
t3 as (
select cust_uid,pchs_tm,lag(pchs_tm,1) over (partition by cust_uid order by pchs_tm) as last_tm
from t2 join xhs_pchs_rcd using (cust_uid)
where fugoulv>=80
)
select cust_uid,round(avg(timestampdiff(second,last_tm,pchs_tm)/3600),2) as avg_tm 
from t3 
where last_tm is not null
group by cust_uid
order by cust_uid

with t1 as (
select user_id,
count(*) as listen_cnt,
round(100*sum(if_finished)/count(*),2) as completion_rate
from qqmusic_user_info q join listen_rcd l using (user_id)
group by user_id 
order by listen_cnt desc
),
t2 as (
select user_id,listen_cnt,completion_rate,
case when listen_cnt>=80 then 1 
when listen_cnt>=60 then 2
when listen_cnt>=50 then 3 
when listen_cnt>=40 then 4 
else 5 
end as listen_jibie,
ntile(5) over (order by completion_rate desc) as completion_rate_jibie 
from t1
)
select *
from t2

with t1 as (
select usr_id,date_format(trx_time,'%Y-%m') as rx_month,
sum(trx_amt) as trx_amt
from cmb_usr_trx_rcd 
group by usr_id,rx_month
order by rx_month asc
)
select rx_month,
case when trx_amt<=100 then '0-100'
when trx_amt>100 and trx_amt<=1000 then '100-1k'
when trx_amt>1000 and trx_amt<=10000 then '1k-1w'
else '>1w'
end as amt_range,
round(100*count(*)/sum(count(*)) over (partition by rx_month),2) as percentage
from t1 
group by rx_month,amt_range 
order by rx_month asc,amt_range asc

select screen_type,
round(100*sum(if_AI_talking=1 and if_hint=1 and timestampdiff(second,start_time,end_time)>=duration)/sum(if_AI_talking=1 and if_hint=1),2) as AI_with_hint,
round(100*sum(if_AI_talking=1 and if_hint=0 and timestampdiff(second,start_time,end_time)>=duration)/sum(if_AI_talking=1 and if_hint=0),2) as AI_no_hint,
round(100*sum(if_AI_talking=0 and if_hint=1 and timestampdiff(second,start_time,end_time)>=duration)/sum(if_AI_talking=0 and if_hint=1),2) as no_AI_with_hint,
round(100*sum(if_AI_talking=0 and if_hint=0 and timestampdiff(second,start_time,end_time)>=duration)/sum(if_AI_talking=0 and if_hint=0),2) as no_AI_no_hint
from ks_video_inf k1 join ks_video_wat_log k2 using (video_id)
group by screen_type

with t1 as (
select uid
from ks_video_inf k1 join ks_video_wat_log k2 using (video_id)
where duration>180 and start_time>=date_sub(curdate(),interval 1 month)
group by uid 
having count(distinct case when timestampdiff(second,start_time,end_time)>=duration then video_id end)>=2
)
select count(*) as total_users 
from t1

select video_id,title,
round(sum(timestampdiff(second,start_time,end_time))/3600,2) as total_play_duration_hours
from ks_video_inf k1 join ks_video_wat_log k2 using (video_id)
where start_time>=date_sub(curdate(),interval 1 month)
group by video_id,title
order by total_play_duration_hours desc 
limit 5

select video_id,title,
count(*) as view_count,
sum(if_like) as like_count,
sum(if_retweet) as retweet_count,
sum(if_fav) as fav_count,
sum(if_like)+sum(if_fav)+sum(if_retweet) as total_interactions
from ks_video_inf k1 join ks_video_wat_log k2 using (video_id)
where start_time>=date_sub(curdate(),interval 1 month)
group by video_id,title 
order by total_interactions desc 
limit 3

select case when hour(enter_time)>=10 then hour(enter_time)
else concat('0',hour(enter_time)) 
end as hour_entered,
count(distinct usr_id) as enter_count
from ks_live_t1 
group by hour_entered

with t1 as (
select cust_uid,mch_nm
from mt_trx_rcd1
where cust_uid='MT10000'
group by cust_uid,mch_nm
),
t2 as (
select cust_uid,mch_nm
from mt_trx_rcd1
where cust_uid<>'MT10000'
group by cust_uid,mch_nm
)
select t1.cust_uid,t2.cust_uid as cust_uid_1
from t1 join t2 using (mch_nm)
group by t1.cust_uid,t2.cust_uid
having count(*)=(select count(distinct mch_nm) from mt_trx_rcd1 where cust_uid='MT10000')
order by cust_uid_1 asc

with t1 as (
select cust_uid,mch_nm
from mt_trx_rcd1
where cust_uid='MT10000'
group by cust_uid,mch_nm
),
t2 as (
select cust_uid,mch_nm
from mt_trx_rcd1
where cust_uid<>'MT10000'
group by cust_uid,mch_nm
)
select t1.cust_uid,t2.cust_uid as cust_uid_1
from t1 join t2 using (mch_nm)
group by t1.cust_uid,t2.cust_uid
having count(*)=14
order by cust_uid_1 asc

select 'MT10000' as cust_uid,cust_uid as cust_uid1
from mt_trx_rcd1 
where cust_uid<>'MT10000'
group by cust_uid1
having sum(mch_nm='庄家界(千灯店)')>=1 and sum(mch_nm='黄记烘培宫廷桃酥王')>=1 
order by cust_uid1 asc

with t1 as (
select cust_uid
from mt_trx_rcd1
where mch_nm='兰州李晓明拉面馆' and cust_uid<>'MT10000'
group by cust_uid
)
select 'MT10000' as cust_uid,cust_uid as cust_uid1,'兰州李晓明拉面馆' as mch_nm
from t1 
order by cust_uid1 asc

with t1 as (
select cust_uid,
datediff(curdate(),max(trx_dt)) as recency,
count(distinct trx_dt) as frequency,
avg(trx_amt) as monetary
from mt_trx_rcd_f 
group by cust_uid
),
t2 as (
select cust_uid,
ntile(3) over (order by recency desc) as recency_score,
ntile(3) over (order by frequency asc) as frequency_score,
ntile(3) over (order by monetary asc) as monetary_score
from t1
) 
select mch_typ2,count(*) as visit_count 
from t2 join mt_trx_rcd_f m using (cust_uid)
where recency_score+frequency_score+monetary_score>=7
group by mch_typ2
order by visit_count desc

with t1 as (
select cust_uid,
datediff(curdate(),max(trx_dt)) as recency,
count(distinct trx_dt) as frequency,
avg(trx_amt) as monetary
from mt_trx_rcd_f 
group by cust_uid
),
t2 as (
select cust_uid,
ntile(3) over (order by recency desc) as recency_score,
ntile(3) over (order by frequency asc) as frequency_score,
ntile(3) over (order by monetary asc) as monetary_score
from t1
)
select cust_uid,recency_score,frequency_score,monetary_score 
from t2 
where recency_score=1 and frequency_score<=2
order by cust_uid asc

select cust_uid,
case when datediff(curdate(),max(trx_dt))>30 then 1 
when datediff(curdate(),max(trx_dt))<10 then 3
else 2 
end as recency_score,
case when count(*)<=5 then 1 
when count(*)>15 then 3
else 2 
end as frequency_score,
case when sum(trx_amt)<2000 then 1
when sum(trx_amt)>5000 then 3 
else 2 
end as monetary_score
from mt_trx_rcd_f 
group by cust_uid
order by cust_uid asc

select cust_uid,
case when datediff(curdate(),max(trx_dt))>30 then 1
when datediff(curdate(),max(trx_dt))<10 then 3 
else 2 
end as recency,
case when count(distinct trx_dt)<=10 then 1
when count(distinct trx_dt)>20 then 3 
else 2 
end as frequency,
case when avg(trx_amt)<150 then 1 
when avg(trx_amt)>250 then 3 
else 2 
end as monetary
from mt_trx_rcd_f 
group by cust_uid 
order by cust_uid asc

select cust_uid,
datediff(curdate(),max(trx_dt)) as recency,
count(*) as frequency,
sum(trx_amt) as monetary
from mt_trx_rcd_f 
group by cust_uid
order by cust_uid asc

select cust_uid,
count(*) as transaction_count,
rank() over (order by count(*) desc) as transaction_rank,
count(distinct trx_dt) as active_days_count,
rank() over (order by count(distinct trx_dt)) as active_days_rank,
abs(cast(rank() over (order by count(*) desc) as signed)-cast(rank() over (order by count(distinct trx_dt) desc) as signed)) as rank_difference
from mt_trx_rcd_f 
group by cust_uid
order by rank_difference desc

select cust_uid,
count(*) as transaction_count,
rank() over (order by count(*) desc) as transaction_rank,
count(distinct trx_dt) as active_days_count,
rank() over (order by count(distinct trx_dt)) as active_days_rank,
abs(cast(rank() over (order by count(*) desc) as signed)-cast(rank() over (order by count(distinct trx_dt) desc) as signed)) as rank_difference
from mt_trx_rcd_f 
group by cust_uid
order by rank_difference desc,cust_uid asc

select cust_uid,
count(*) as transaction_count,
rank() over (order by count(*) desc) as transaction_rank,
count(distinct trx_dt) as active_days_count,
rank() over (order by count(distinct trx_dt)) as active_days_rank,
abs(cast(rank() over (order by count(*) desc) as signed)-cast(rank() over (order by count(distinct trx_dt)) as signed)) as rank_difference
from mt_trx_rcd_f 
group by cust_uid
order by rank_difference desc,cust_uid asc