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

select mch_nm,
sum(trx_amt) as restaurant_revenue,
sum(sum(trx_amt)) over () as category_revenue,
round(100*sum(trx_amt)/sum(sum(trx_amt)) over (),2) as market_share
from mt_trx_rcd1 
where trx_dt between '2021-03-01' and '2021-03-31' and mch_typ2='面包甜点'
group by mch_nm

with t1 as (
select mch_nm,min(trx_dt) as trx_dt,cust_uid
from mt_trx_rcd1
group by mch_nm,cust_uid
)
select m.mch_nm,date_format(m.trx_dt,'%Y-%m') as month,
count(distinct m.cust_uid) as total_users,
count(distinct t1.cust_uid) as new_users,
round(100*count(distinct t1.cust_uid)/count(distinct m.cust_uid),2) as new_user_ratio
from mt_trx_rcd1 m left join t1 using (mch_nm,trx_dt,cust_uid)
group by m.mch_nm,month
having count(distinct t1.cust_uid)>0