select
round(sum(case when '2020-11-20' between begin_date and end_date then pay_amount/(datediff(end_date,begin_date)) else 0 end),2) as total_daily_income
from bilibili_m1
with a as(
select
distinct
usr_id,
date_format(login_time,'%Y-%m-%d') as login_time
from user_login_log
where datediff(curdate(),login_time)<=90
)
select
b.login_time as first_login_date,
round(count(distinct if (datediff(c.login_time,b.login_time) between 1 and 3 ,b.usr_id,Null))/count(distinct b.usr_id)*100,2)as t_plus_3_retention_rate,
round(count(distinct if (datediff(c.login_time,b.login_time) between 1 and 7,b.usr_id,Null))/count(distinct b.usr_id)*100,2)as t_plus_7_retention_rate,
round(count(distinct if (datediff(c.login_time,b.login_time) between 1 and 14,b.b.usr_id,Null))/count(distinct b.usr_id)*100,2) as t_plus_14_retention_rate
from a b
left join a c
on b.usr_id = c.usr_id
and datediff(c.login_time,b.login_time)<=14
group by b.login_time
order by b.login_time
with a as(
select
distinct
usr_id,
date_format(login_time,'%Y-%m-%d') as login_time
from user_login_log
where datediff(curdate(),login_time)<=90
)
select
b.login_time as first_login_date,
round(count(distinct if (datediff(c.login_time,b.login_time) between 1 and 3 ,b.login_time,Null))/count(distinct b.usr_id)*100,2)as t_plus_3_retention_rate,
round(count(distinct if (datediff(c.login_time,b.login_time) between 1 and 7,b.login_time,Null))/count(distinct b.usr_id)*100,2)as t_plus_7_retention_rate,
round(count(distinct if (datediff(c.login_time,b.login_time) between 1 and 14,b.login_time,Null))/count(distinct b.usr_id)*100,2) as t_plus_14_retention_rate
from a b
left join a c
on b.usr_id = c.usr_id
and datediff(c.login_time,b.login_time)<=14
group by b.login_time
order by b.login_time
with a as(
select
distinct
usr_id,
date_format(login_time,'%Y-%m-%d') as login_time
from user_login_log
where datediff(curdate(),login_time)<=90
)
select
b.login_time as first_login_date,
round(count(distinct if (datediff(c.login_time,b.login_time)<=3,b.login_time,Null))/count(distinct b.usr_id)*100,2)as t_plus_3_retention_rate,
round(count(distinct if (datediff(c.login_time,b.login_time)<=7,b.login_time,Null))/count(distinct b.usr_id)*100,2)as t_plus_7_retention_rate,
round(count(distinct if (datediff(c.login_time,b.login_time)<=14,b.login_time,Null))/count(distinct b.usr_id)*100,2) as t_plus_14_retention_rate
from a b
left join a c
on b.usr_id = c.usr_id
and datediff(c.login_time,b.login_time)<=14
group by b.login_time
order by b.login_time
with a as(
select
distinct
usr_id,
date_format(login_time,'%Y-%m-%d') as login_time
from user_login_log
where datediff(curdate(),login_time)<=90
)
select
b.login_time as first_login_date,
round(count(distinct if (datediff(c.login_time,b.login_time)<=3,b.login_time,Null))/count(distinct b.usr_id)*100,2)as t_plus_3_retention_rate,
round(count(distinct if (datediff(c.login_time,b.login_time)<=7,b.login_time,Null))/count(distinct b.usr_id)*100,2)as t_plus_7_retention_rate,
round(count(distinct if (datediff(c.login_time,b.login_time)<=14,b.login_time,Null))/count(distinct b.usr_id)*100,2) as t_plus_14_retention_rate
from a b
left join a c
on b.usr_id = c.usr_id
and datediff(c.login_time,b.login_time)<=14
group by b.login_time
with a as(
select
distinct
usr_id,
date_format(login_time,'%Y-%m-%d') as login_time
from user_login_log
where datediff(curdate(),login_time)>=90
)
select
b.login_time as first_login_date,
round(count(distinct if (datediff(c.login_time,b.login_time)<=3,b.login_time,Null))/count(distinct b.usr_id)*100,2)as t_plus_3_retention_rate,
round(count(distinct if (datediff(c.login_time,b.login_time)<=7,b.login_time,Null))/count(distinct b.usr_id)*100,2)as t_plus_7_retention_rate,
round(count(distinct if (datediff(c.login_time,b.login_time)<=14,b.login_time,Null))/count(distinct b.usr_id)*100,2) as t_plus_14_retention_rate
from a b
left join a c
on b.usr_id = c.usr_id
and datediff(c.login_time,b.login_time)<=14
group by b.login_time
with a as(
select
usr_id,
login_time
from(
select
usr_id,
date_format(login_time,'%Y-%m-01') as login_time,
row_number()over(partition by usr_id order by login_time) as rn
from user_login_log
where login_time >= '2024-01-01' AND login_time < '2025-01-01') a
where rn =1
),
b as(
select
distinct
usr_id,
date_format(login_time,'%Y-%m-01') as login_time
from user_login_log
where login_time >= '2024-01-01' AND login_time < '2025-01-01'
)
select
a.login_time as current_month,
round(count(b.login_time)/count(*)*100,2)as t_plus_1_month_retention_rate
from a
left join b
on date_add(a.login_time,interval 1 month)=b.login_time and a.usr_id = b.usr_id
group by a.login_time
order by t_plus_1_month_retention_rate desc
with a as(
select
usr_id,
login_time
from(
select
usr_id,
date_format(login_time,'%Y-%m-01') as login_time,
row_number()over(partition by usr_id order by login_time) as rn
from user_login_log
where login_time >= '2024-01-01' AND login_time < '2025-01-01') a
where rn =1
),
b as(
select
distinct
usr_id,
date_format(login_time,'%Y-%m-01') as login_time
from user_login_log
)
select
a.login_time as current_month,
round(count(b.login_time)/count(*)*100,2)as t_plus_1_month_retention_rate
from a
left join b
on date_add(a.login_time,interval 1 month)=b.login_time and a.usr_id = b.usr_id
group by a.login_time
order by t_plus_1_month_retention_rate desc
with a as(
select
usr_id,
login_time
from(
select
usr_id,
date_format(login_time,'%Y-%m-01') as login_time,
row_number()over(partition by usr_id order by login_time) as rn
from user_login_log
where login_time >= '2024-01-01' AND login_time < '2025-01-01') a
where rn =1
),
b as(
select
distinct
usr_id,
date_format(login_time,'%Y-%m-01') as login_time
from user_login_log
)
select
a.login_time as current_month,
round(count(b.login_time)/count(*)*100,2)as t_plus_1_month_retention_rate
from a
left join b
on timestampdiff(month,a.login_time,b.login_time)=1 and a.usr_id = b.usr_id
group by a.login_time
order by t_plus_1_month_retention_rate desc
with a as(
select
usr_id,
login_time
from(
select
usr_id,
date_format(login_time,'%Y-%m-01') as login_time,
row_number()over(partition by usr_id order by login_time) as rn
from user_login_log) a
where rn =1 and login_time >= '2024-01-01' AND login_time < '2025-01-01'
),
b as(
select
distinct
usr_id,
date_format(login_time,'%Y-%m-01') as login_time
from user_login_log
)
select
a.login_time as current_month,
round(count(b.login_time)/count(*)*100,2)as t_plus_1_month_retention_rate
from a
left join b
on timestampdiff(month,a.login_time,b.login_time)=1 and a.usr_id = b.usr_id
group by a.login_time
order by t_plus_1_month_retention_rate desc
with a as(
select
usr_id,
login_time
from(
select
usr_id,
date_format(login_time,'%Y-%m-01') as login_time,
row_number()over(partition by usr_id order by login_time) as rn
from user_login_log) a
where rn =1
),
b as(
select
distinct
usr_id,
date_format(login_time,'%Y-%m-01') as login_time
from user_login_log)
select
a.login_time as current_month,
round(count(b.login_time)/count(*)*100,2)as t_plus_1_month_retention_rate
from a
left join b
on timestampdiff(month,a.login_time,b.login_time)=1 and a.usr_id = b.usr_id
group by a.login_time
order by t_plus_1_month_retention_rate desc
with a as(
select
usr_id,
login_time
from(
select
usr_id,
date_format(login_time,'%Y-%m-01') as login_time,
row_number()over(partition by usr_id order by login_time) as rn
from user_login_log) a
where rn =1
),
b as(
select
distinct
usr_id,
date_format(login_time,'%Y-%m-01') as login_time
from user_login_log)
select
a.login_time as current_month,
count(b.login_time)/count(*) as t_plus_1_month_retention_rate
from a
left join b
on timestampdiff(month,a.login_time,b.login_time)=1 and a.usr_id = b.usr_id
group by a.login_time
order by t_plus_1_month_retention_rate desc
with a as(
select
usr_id,
login_time
from(
select
usr_id,
date_format(login_time,'%Y-%m-01') as login_time,
row_number()over(partition by usr_id order by login_time) as rn
from user_login_log) a
where rn =1
),
b as(
select
distinct
usr_id,
date_format(login_time,'%Y-%m-01') as login_time
from user_login_log)
select
a.login_time as current_month,
count(b.login_time)/count(*) as t_plus_1_month_retention_rate
from a
left join b
on timestampdiff(month,a.login_time,b.login_time)=1 and a.usr_id = b.usr_id
group by a.login_time
with a as(
select
distinct
usr_id,
date_format(login_time,'%Y-%m-%d') as login_time
from
user_login_log
where login_time>=date_sub(curdate(), INTERVAL 30 DAY)),
b as(
select
usr_id,
login_time,
date_sub(login_time,interval row_number()over(partition by usr_id order by login_time) day) as diff
from a)
select
usr_id,
min(login_time) as start_date,
max(login_time) as end_date,
count(*) as consecutive_days
from b
group by usr_id,diff
having count(*)>=3
with a as(
select
distinct
usr_id,
date_format(login_time,'%Y-%m-%d') as login_time
from
user_login_log
where login_time>=date_sub(curdate(), INTERVAL 30 DAY)),
b as(
select
usr_id,
login_time,
datediff(login_time,row_number()over(partition by usr_id order by login_time)) as diff
from a)
select
usr_id,
min(login_time) as start_date,
max(login_time) as end_date,
count(*) as consecutive_days
from b
group by usr_id,diff
having count(*)>=3
order by consecutive_days desc
with a as(
select
distinct
usr_id,
date_format(login_time,'%Y-%m-%d') as login_time
from
user_login_log
where login_time>=date_sub(curdate(), INTERVAL 30 DAY)),
b as(
select
usr_id,
login_time,
datediff(login_time,row_number()over(partition by usr_id order by login_time)) as diff
from a)
select
usr_id,
min(login_time) as start_date,
max(login_time) as end_date,
count(*) as consecutive_days
from b
group by usr_id,diff
having count(*)>=3
order by consecutive_days
with a as(
select
distinct
usr_id,
date_format(login_time,'%Y-%m-%d') as login_time
from
user_login_log
where login_time>=date_sub(curdate(), INTERVAL 30 DAY)),
b as(
select
usr_id,
login_time,
datediff(login_time,row_number()over(partition by usr_id order by login_time)) as diff
from a)
select
usr_id,
min(login_time) as start_date,
max(login_time) as end_date,
count(*) as consecutive_days
from b
group by usr_id,diff
having count(*)>=3
with a as(
select
distinct
usr_id,
date_format(login_time,'%Y-%m-%d') as login_time
from
user_login_log),
b as(
select
usr_id,
login_time,
datediff(login_time,row_number()over(partition by usr_id order by login_time)) as diff
from a)
select
usr_id,
min(login_time) as start_date,
max(login_time) as end_date,
count(*) as consecutive_days
from b
group by usr_id,diff
having count(*)>=3
with a as(
select
usr_id,
date_format(login_time,'%Y-%m-%d') as login_time
from user_login_log
where datediff(current_date(),login_time)<=30
group by usr_id, date_format(login_time,'%Y-%m-%d')),
b as(
select
usr_id,
login_time,
date_sub(login_time,interval row_number()over(partition by usr_id order by login_time) day) as diff
from a)
select
usr_id,
min(login_time) as start_date,
max(login_time) as end_date,
count(*) as consecutive_days
from b
group by usr_id,diff
having consecutive_days >=3
order by usr_id,
start_date
with a as(
select
usr_id,
date_format(login_time,'%Y-%m-%d') as login_time
from user_login_log
where datediff(current_date(),login_time)<=29
group by usr_id,login_time),
b as(
select
usr_id,
login_time,
date_sub(login_time,interval row_number()over(partition by usr_id order by login_time) day) as diff
from a)
select
usr_id,
min(login_time) as start_date,
max(login_time) as end_date,
count(*) as consecutive_days
from b
group by usr_id,diff
having consecutive_days >=3
order by usr_id,
start_date
with a as(
select
usr_id,
date_format(login_time,'%Y-%m-%d') as login_time
from user_login_log
where datediff(current_date(),login_time)<=30
group by usr_id,login_time),
b as(
select
usr_id,
login_time,
date_sub(login_time,interval row_number()over(partition by usr_id order by login_time) day) as diff
from a)
select
usr_id,
min(login_time) as start_date,
max(login_time) as end_date,
count(*) as consecutive_days
from b
group by usr_id,diff
having consecutive_days >=3
order by usr_id desc,
start_date