select singer_name
		,count(distinct s.song_id) as song_cnt
,count(l.song_id) as total_listen
,round(count(l.song_id)/count(distinct s.song_id),2) as avg_listen
from singer_info si 
inner join song_info s on si.singer_id=s.origin_singer_id
inner join listen_rcd l on s.song_id=l.song_id
group by singer_name 
order by 4 desc

select singer_name
		,count(distinct l.song_id) as song_cnt
,count(l.song_id) as total_listen
,round(count(l.song_id)/count(distinct l.song_id),2) as avg_listen
from singer_info si 
inner join song_info s on si.singer_id=s.origin_singer_id
inner join listen_rcd l on s.song_id=l.song_id
group by singer_name 
order by 4 desc

with basedata as
(
select cust_uid as user_id
		,datediff(curdate(),max(trx_dt)) as recency
,count(distinct trx_dt) as frequency
,sum(trx_amt)/count(*) 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) as monetary_score
from basedata
order by 1

with basedata as
(
select cust_uid as user_id
		,datediff(curdate(),max(trx_dt)) as recency
,count(distinct trx_dt) as frequency
,sum(trx_amt)/count(*) 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) as monetary_score
from basedata
order by 1

with basedata as
(
select cust_uid as user_id
		,datediff(curdate(),max(trx_dt)) as recency
,count(*) as frequency
,sum(trx_amt) as monetary
from mt_trx_rcd_f
group by 1
order by 1)
select user_id
		,case when recency <10 then 3 when recency <=30 then 2 else 1 end as recency_score
,case when frequency between 1 and 5 then 1 when frequency between 6 and 15 then 2 when frequency >15 then 3 else 'unknown' end as frequency_score
,case when monetary<2000 then 1 when monetary <=5000 then 2 else 3 end as monetary_score
from basedata
order by 1

with basedata as
(
select cust_uid as user_id
		,datediff(curdate(),max(trx_dt)) as recency
,count(*) as frequency
,sum(trx_amt) as monetary
from mt_trx_rcd_f
group by 1
order by 1)
select user_id
		,case when recency<10 then 3 when recency<=30 then 2 else 1 end as recency_score
,case when frequency>15 then 3 when frequency between 6 and 15 then 2 when frequency between 1 and 5 then 1 else 'unknown' end as frequency_score
,case when monetary <2000 then 1 when monetary <=5000 then 2 else 3 end as monetary_score
from basedata
order by 1

with basedata as
(
select cust_uid as user_id,
		datediff(curdate(),max(trx_dt)) as recency,
count(*) as frequency,
sum(trx_amt) as monetary
from mt_trx_rcd_f
group by 1
order by 1)
select user_id,
		case when recency >30 then 1 when recency between 10 and 30 then 2 else 3 end as recency_score,
case when frequency between 1 and 5 then 1 when frequency between 6 and 15 then 2 
when frequency >15 then 3 else '其他' end as frequency_score,
case when monetary <2000 then 1 when monetary between 2000 and 5000 then 2 else 3 end as monetary_score
from basedata
order by 1

with basedata as 
(
select cust_uid as user_id,
datediff(curdate(),max(trx_dt)) as recency,
count(distinct trx_dt) as frequency,
sum(trx_amt)/count(*) as monetary
from mt_trx_rcd_f
group by 1
order by 1)
select user_id,
case when recency <10 then 3
		when recency between 10 and 30 then 2
else 1 end as recency_score,
case when frequency between 1 and 10 then 1
 when frequency between 11 and 20 then 2
 when frequency >20 then 3 end as frequency_score,
case when monetary <150 then 1
			when monetary between 150 and 250 then 2
when monetary >250 then 3 end as monetary_score
from basedata
order by 1

select cust_uid as user_id,
datediff(curdate(),max(trx_dt)) as recency,
count(distinct trx_dt) as frequency,
round(sum(trx_amt)/count(*),4) as monetary
from mt_trx_rcd_f
group by 1
order by 1

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 1

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

with basedata as
(
select cust_uid,count(cust_uid) as transaction_count,
count(distinct trx_dt) as active_days_count	
from mt_trx_rcd_f
group by 1
order by 3 desc )
,
rankdata as
(
select 
cust_uid,
transaction_count,
rank() over(order by transaction_count desc) as transaction_rank,
active_days_count,
rank() over(order by active_days_count desc) as active_days_rank
from basedata)
select 
cust_uid,
transaction_count,
transaction_rank,
active_days_count,
active_days_rank,
cast(transaction_rank as signed) -cast(active_days_rank as signed) as rank_difference
from rankdata 
order by 6 desc,1 asc

with basedata as(
select cust_uid,substr(trx_dt,1,7) as month,
mch_nm,
row_number()over(partition by mch_nm,cust_uid order by trx_dt) as rn
from mt_trx_rcd1
order by mch_nm,month
)
select mch_nm,month,
count(distinct cust_uid) as total_users,
count(distinct case when rn=1 then cust_uid else null end) as new_users,
round(count(distinct case when rn=1 then cust_uid else null end)/count(distinct cust_uid)*100,2) as new_user_ratio
from basedata
group by 1,2
having count(distinct case when rn=1 then cust_uid else null end)>0

with basedata as 
(
select mch_nm,substr(trx_dt,1,7) as month,
cust_uid,
row_number() over(partition by mch_nm,cust_uid order by trx_dt) as rn
from mt_trx_rcd1
order by 1,2
)
select mch_nm,month,
 count(distinct cust_uid) as total_users,
 count(distinct case when rn=1 then cust_uid else null end) as new_users,
 round(coalesce(count(distinct case when rn=1 then cust_uid else null end),0)/count(distinct cust_uid)*100,2) as new_user_ratio
from basedata
group by 1,2
having count(distinct case when rn=1 then cust_uid else null end)>0

with basedata as(
select cust_uid,substr(trx_dt,1,7) as month,mch_nm from mt_trx_rcd1)
,
newbasedata as(
select cust_uid,month,mch_nm,
first_value(month) over(partition by mch_nm,cust_uid order by month) as fmth,
case when month=first_value(month) over(partition by mch_nm,cust_uid order by month) then 'new' else '其他'end as type
from basedata)
,
newdata as(
select * from newbasedata
where type='new')
,
ttlcnt as (
select mch_nm,month,count(distinct cust_uid) as total_users
from basedata 
group by 1,2
order by 1,2
)
,
newcnt as(
select mch_nm,month,count(distinct cust_uid) as new_users
from newdata
group by 1,2
order by 1,2
)
select t.*,
n.new_users,
round(coalesce(n.new_users,0)/t.total_users*100,2) as new_user_ratio
from ttlcnt t 
left join newcnt n on t.mch_nm=n.mch_nm and t.month=n.month 
where n.new_users is not null

with basedata as(
select cust_uid,year(trx_dt) as year,mch_typ2
from mt_trx_rcd1
)
,
rpdata as(
select cust_uid,year,mch_typ2,count(*) as cnt
from basedata
group by 1,2,3
having count(*)>=2
)
,
ttlcnt as (
select mch_typ2,year,count(distinct cust_uid) as total_users
from basedata 
group by 1,2
order by 1,2
)
,
rpcnt as(
select mch_typ2,year,count(distinct cust_uid) as repeat_users
from rpdata
group by 1,2
order by 1,2
)
select t.*,r.repeat_users,
 round(coalesce(repeat_users,0)/total_users*100,2) as repurchase_rate
from ttlcnt t 
left join rpcnt r on t.mch_typ2=r.mch_typ2

with basedata as(
select cust_uid,year(trx_dt) as yr,mch_nm from mt_trx_rcd1 order by mch_nm)
,
rpdata as(
select mch_nm,yr,cust_uid,count(*) as cnt
from basedata
group by 1,2,3
having cnt>=2)
,
ttlcnt as(
select mch_nm,yr as year,count(distinct cust_uid) as total_users
from basedata
group by 1,2
order by 1,2)
,
rpcnt as(
select mch_nm,yr as year,count(distinct cust_uid) as repeat_users
from rpdata
group by 1,2
order by 1,2
)
select t.*,
r.repeat_users,
round(coalesce(repeat_users,0)/total_users*100,2) as repurchase_rate
from ttlcnt as t 
left join rpcnt as r on t.mch_nm=r.mch_nm

with mthbase as (
 select substr(trx_dt,1,7) as month,
 trx_amt as amt,
 mch_nm,
 trx_seq
 from mt_trx_rcd1
 ),
avgamt as(
select mch_nm,month,sum(amt)/count(trx_seq) as avg_spending
from mthbase 
group by 1,2
order by mch_nm,month
)
select *,
round((avg_spending/lag(avg_spending) over(partition by mch_nm order by month)-1)*100,2) as growth_rate
from avgamt

with basedata as(
select start_loc,end_loc,l1.loc_ctg as start_ctg,l2.loc_ctg as end_ctg,
count(*) as trip_count 
from didi_sht_rcd d 
inner join loc_nm_ctg l1 on d.start_loc=l1.loc_nm
inner join loc_nm_ctg l2 on d.end_loc=l2.loc_nm
where l1.loc_ctg='酒店'
or l2.loc_ctg='酒店'
group by 1,2,3,4)
select start_loc,end_loc,start_ctg,end_ctg,trip_count
from(
select *,
row_number() over(partition by start_ctg,end_ctg order by trip_count desc) as rk
from basedata) t where rk=1 
order by trip_count desc

with basedata as (
select start_loc,end_loc,concat(start_loc,'-',end_loc,l2.loc_ctg) as line,l2.loc_ctg
from didi_sht_rcd d
inner join loc_nm_ctg l1 on d.start_loc=l1.loc_nm 
inner join loc_nm_ctg l2 on d.end_loc=l2.loc_nm
where l1.loc_ctg = '酒店'
)
select start_loc,end_loc,loc_ctg,cnt as trip_count
from (
select *,
 row_number()over(partition by loc_ctg order by cnt desc) as rk
from(
select *
from (
Select start_loc,end_loc,loc_ctg,
count(line) over(partition by loc_ctg,line) as cnt
from basedata) t ) t2
group by 1,2,3,4) t3 
where rk=1
order by 4 desc