with a as
(select distinct usr_id,date(login_time) as ld
from user_login_log
where datediff(curdate(),date(login_time))<=30
) 
select a.ld as first_login_date, 
round(count(a2.usr_id)/count(a.usr_id)*100,2) as t_plus_1_retention_rate,
round(count(a3.usr_id)/count(a.usr_id)*100,2) as t_plus_3_retention_rate,
round(count(a4.usr_id)/count(a.usr_id)*100,2) as t_plus_7_retention_rate,
round(count(a5.usr_id)/count(a.usr_id)*100,2) as t_plus_14_retention_rate
from a
left join a a2 on a.usr_id=a2.usr_id and a.ld=date_sub(a2.ld, interval 1 day)
left join a a3 on a.usr_id=a3.usr_id and a.ld=date_sub(a3.ld, interval 3 day)
left join a a4 on a.usr_id=a4.usr_id and a.ld=date_sub(a4.ld, interval 7 day)
left join a a5 on a.usr_id=a5.usr_id and a.ld=date_sub(a5.ld, interval 14 day)
group by a.ld

with a as
(select distinct usr_id,date(login_time) as ld
from user_login_log
where datediff(curdate(),date(login_time))<=30
) 
select a.ld as first_login_date, 
count(a2.usr_id)/count(a.usr_id)*100 as t_plus_1_retention_rate,
count(a3.usr_id)/count(a.usr_id)*100 as t_plus_3_retention_rate,
count(a4.usr_id)/count(a.usr_id)*100 as t_plus_7_retention_rate,
count(a5.usr_id)/count(a.usr_id)*100 as t_plus_14_retention_rate
from a
left join a a2 on a.usr_id=a2.usr_id and a.ld=date_sub(a2.ld, interval 1 day)
left join a a3 on a.usr_id=a3.usr_id and a.ld=date_sub(a3.ld, interval 3 day)
left join a a4 on a.usr_id=a4.usr_id and a.ld=date_sub(a4.ld, interval 7 day)
left join a a5 on a.usr_id=a5.usr_id and a.ld=date_sub(a5.ld, interval 14 day)
group by a.ld

with a as
(select *, case when chinese >= 110 then 1 else 0 end as c1,
case when math >= 110 then 1 else 0 end as m1,
case when english >= 110 then 1 else 0 end as e1
from subject_score)
select * from subject_score
where student_id in
(select student_id from
(select student_id, c1+m1+e1 as score
from a) b
where score >=2)
order by student_id

with a as
(select distinct usr_id, str_to_date(date_format(login_time,'%Y-%m-%d'),'%Y-%m-%d') as dat
from user_login_log
where datediff(curdate(),str_to_date(date_format(login_time,'%Y-%m-%d'),'%Y-%m-%d'))<=30 ),
b as
(select usr_id, dat, rank()over(partition by usr_id order by dat) as rankk, 
date_sub(dat,interval rank()over(partition by usr_id order by dat) day) as dat2
from a)
select usr_id, start_date, end_date, consecutive_days
from
(select usr_id,dat2, min(dat) as start_date, date_add(min(dat),interval count(usr_id)-1 day) as end_date, count(usr_id) as consecutive_days
from b
group by usr_id,dat2
having consecutive_days>=3) c

with a as
(select distinct usr_id, str_to_date(date_format(login_time,'%Y-%m-%d'),'%Y-%m-%d') as dat
from user_login_log
where datediff(curdate(),str_to_date(date_format(login_time,'%Y-%m-%d'),'%Y-%m-%d'))<=30 ),
b as
(select usr_id, dat, rank()over(partition by usr_id order by dat) as rankk, 
date_sub(dat,interval rank()over(partition by usr_id order by dat) day) as dat2
from a)
select usr_id,dat2,min(dat) as start_date, date_add(min(dat),interval count(usr_id) day) as end_date, count(usr_id) as consecutive_days
from b
group by usr_id,dat2
having consecutive_days>=3

with a as
(select distinct usr_id, date_format(login_time,'%Y-%m') as month
from user_login_log
where year(login_time)=2024) ,
b as (select usr_id, str_to_date(concat(min(month),'-01'),'%Y-%m-%d') as current_month
from a
group by usr_id)
select current_month, 
round(count(distinct case when next_month is not null then b.usr_id end)/count(distinct b.usr_id)*100,2) as t_plus_1_month_retention_rate
from b left join 
(select usr_id, str_to_date(concat(month,'-01'),'%Y-%m-%d') as next_month
from a) c
on b.usr_id=c.usr_id
and date_add(b.current_month, interval 1 month)=c.next_month
group by current_month
order by current_month

select *
from 
(select date(order_time) order_date,
goods_id, sum(order_gmv) total_gmv,
rank() over(partition by date(order_time) order by sum(order_gmv)) ranking
from order_info
where order_time like '2024-10%'
group by order_date, goods_id) t
where ranking <= 3

WITH user_login_date AS (
SELECT DISTINCT usr_id, DATE(login_time) AS login_date
FROM user_login_log
WHERE 
 DATEDIFF(CURDATE(), DATE(login_time)) <= 30 
)
SELECT 
 ta.login_date AS first_login_date,
 CAST(COUNT(tb.usr_id) / COUNT(*)*100 AS decimal(10,2)) AS t_plus_1_retention_rate,
 CAST(COUNT(tc.usr_id) / COUNT(*)*100 AS decimal(10,2)) AS t_plus_3_retention_rate,
 CAST(COUNT(td.usr_id) / COUNT(*)*100 AS decimal(10,2)) AS t_plus_7_retention_rate,
 CAST(COUNT(te.usr_id) / COUNT(*)*100 AS decimal(10,2)) AS t_plus_14_retention_rate
FROM user_login_date AS ta
LEFT JOIN user_login_date AS tb
ON ta.usr_id = tb.usr_id AND DATEDIFF(tb.login_date, ta.login_date) = 1
LEFT JOIN user_login_date AS tc
ON ta.usr_id = tc.usr_id AND DATEDIFF(tc.login_date, ta.login_date) = 3
LEFT JOIN user_login_date AS td
ON ta.usr_id = td.usr_id AND DATEDIFF(td.login_date, ta.login_date) = 7
LEFT JOIN user_login_date AS te
ON ta.usr_id = te.usr_id AND DATEDIFF(te.login_date, ta.login_date) = 14
GROUP BY ta.login_date
ORDER BY ta.login_date

select date_format(purchase_time,'%Y%u') as week_number,
case when product_type='iPhone' then'iPhone'
 else 'Not iPhone' end as category,
count(distinct user_id) as user_count
from apple_pchs_rcd
group by 1,2
order by 1,2

select mch_nm, month, avg_spending, 
round((avg_spending-lag(avg_spending,1)over(partition by mch_nm order by month))/lag(avg_spending,1)over(partition by mch_nm order by month)*100,2) as growth_rate from
(select month,mch_nm,sum(trx_amt)/count(distinct trx_seq) as avg_spending from
(select mch_nm, trx_seq, cust_uid, date_format(trx_dt,'%Y-%m') as month, trx_amt 
from mt_trx_rcd1) a
group by month, mch_nm) b

with a as
(select trx_seq, cust_uid, left(trx_dt,4) as year, mch_nm
from mt_trx_rcd1
where left(trx_dt,4)='2021') ,
b as
(select mch_nm,cust_uid,count(distinct trx_seq) as cnt
from a
group by mch_nm,cust_uid
having cnt>=2)
select c.mch_nm, '2021' as year, total_users, repeat_users,
round((repeat_users/total_users)*100,2) as repurchase_rate
from
(select mch_nm,count(distinct cust_uid) as total_users from a
group by mch_nm) c
left join
(select mch_nm,count(distinct cust_uid) as repeat_users from b
group by mch_nm) d
on c.mch_nm=d.mch_nm

select order_dt,count(distinct order_id),
count(distinct case when status='completed' and usr_stat=0 and driver_stat=0 then order_id end),
count(distinct case when status='completed' and usr_stat=0 and driver_stat=0 then order_id end)/count(distinct order_id)
from
(select a.*, b.banned as usr_stat,c.banned as driver_stat 
from hll_t1 a
left join hll_t2 b
on a.usr_id=b.usr_id
left join hll_t2 c
on a.driver_id =c.usr_id) d
group by order_dt

with a as
(select purchase_time, user_id, product_type as p1, 
 lead(product_type,1)over(partition by user_id order by purchase_time) as p2
from apple_pchs_rcd),
b as (select *, row_number()over(partition by user_id order by purchase_time asc) as rankk
from a)
select user_id, case when p1='Mac' and p2='iPhone' then 1 else 0 end as tag 
from b 
where rankk=1

with a as (
select b.cust_uid,b.mch_id,b.fav_tm,c.pchs_tm
from xhs_fav_rcd b left join xhs_pchs_rcd c
on b.cust_uid=c.cust_uid
and b.mch_id=c.mch_id
and b.fav_tm<c.pchs_tm)
select mch_id, count(distinct cust_uid) as fav_user_count, 
count(distinct case when pchs_tm is not null then cust_uid end) as conv_user_count, 
cast(round((count(distinct case when pchs_tm is not null then cust_uid end)/ count(distinct cust_uid))*100,2) as float) as conversion_rate
from a
group by mch_id
order by conversion_rate desc

with a as (
select b.cust_uid,b.mch_id,b.fav_tm,c.pchs_tm
from xhs_fav_rcd b left join xhs_pchs_rcd c
on b.cust_uid=c.cust_uid
and b.mch_id=c.mch_id
and b.fav_tm<c.pchs_tm)
select mch_id, count(distinct cust_uid) as fav_user_count, 
count(distinct case when pchs_tm is not null then cust_uid end) as conv_user_count, 
round((count(distinct case when pchs_tm is not null then cust_uid end)/ count(distinct cust_uid))*100,2) as conversion_rate
from a
group by mch_id
order by conversion_rate desc

with a as (
select b.cust_uid,b.mch_id,b.fav_tm,c.pchs_tm
from xhs_fav_rcd b left join xhs_pchs_rcd c
on b.cust_uid=c.cust_uid
and b.mch_id=c.mch_id
and b.fav_tm<c.pchs_tm)
select mch_id, count(distinct cust_uid) as fav_user_count, 
count(distinct case when pchs_tm is not null then cust_uid end) as conv_user_count, 
round((count(distinct case when pchs_tm is not null then cust_uid end)/ count(distinct cust_uid))*100,2) as conversion_rate
from a
group by mch_id

select substr(purchase_time,1,7) as month, count(order_id) as total_orders ,count(distinct user_id) as unique_users, round(count(order_id)/count(distinct user_id),2) as avg_orders_per_user
from apple_pchs_rcd
group by substr(purchase_time,1,7)

select substr(purchase_time,1,7) as month, count(distinct user_id),count(order_id),count(order_id)/count(distinct user_id)
from apple_pchs_rcd
group by substr(purchase_time,1,7)