with cte as (
select usr_id,live_id
from ks_live_t1
where '2021-09-12 23:48:38' between enter_time and leave_time
)
select cte.live_id, t2.live_nm, count(*) as online_users
from cte left join ks_live_t2 as t2 on cte.live_id = t2.live_id
group by 1,2
order by 3 desc
with cte as (
select uid, date(start_time) as watch_date,
sum(timestampdiff(second,start_time,end_time)) as duration
from ks_video_wat_log
group by 1,2
)
select uid, round(avg(duration),0) as daily_avg_watch_time
from cte
group by 1
order by 2 desc
limit 5
with cte as (
select uid, date(start_time) as watch_date,
sum(timestampdiff(second,start_time,end_time)) as duration
from ks_video_wat_log
group by 1,2
)
select uid, round(avg(duration),0) as daily_avg_watch_time
from cte
group by 1
order by 2 desc
with cte as (
select usr_id, date_format(trx_time,'%Y-%m') as trx_month, sum(trx_amt) as amt
from cmb_usr_trx_rcd
group by 1,2
),
cte2 as (
select usr_id,trx_month,
case when amt between 0 and 100 then '0-100'
when amt between 101 and 1000 then '100-1k'
when amt between 1001 and 10000 then '1k-1w'
else '>1w'
end as amt_range
from cte
),
cte3 as (
select trx_month, amt_range , count(*) as cnt
from cte2
group by 1,2
),
cte4 as (
select trx_month, count(distinct usr_id) as total_cnt
from cte
group by 1
)
select cte3.trx_month, cte3.amt_range,
round(cnt*100/total_cnt,2) as percentage
from cte3 join cte4 on cte3.trx_month=cte4.trx_month
order by 1,2
with cte as (
select usr_id,live_id,enter_time as time1 , 1 as flag
from ks_live_t1
union all
select usr_id, live_id,leave_time as time1, -1 as flag
from ks_live_t1
),
cte2 as (
select live_id,
sum(flag)over(partition by live_id order by time1) as uv
from cte
)
select cte2.live_id,t2.live_nm, max(uv) as max_online_users
from cte2 left join ks_live_t2 as t2 on cte2.live_id=t2.live_id
group by 1,2
order by 3 desc
WITH monthly_unique_logins AS (
SELECT
usr_id,
DATE_FORMAT(login_time, '%Y-%m-01') AS login_month
FROM
user_login_log
WHERE
login_time >= '2024-01-01' AND login_time < '2025-01-01'
GROUP BY
usr_id,
DATE_FORMAT(login_time, '%Y-%m-01')
),
new_users AS (
SELECT
usr_id,
MIN(login_month) AS first_login_month
FROM
monthly_unique_logins
GROUP BY
usr_id
),
next_month_logins AS (
SELECT
nu.usr_id,
nu.first_login_month AS current_month,
mul.login_month AS next_month
FROM
new_users nu
LEFT JOIN
monthly_unique_logins mul
ON
nu.usr_id = mul.usr_id AND
mul.login_month = DATE_ADD(nu.first_login_month, INTERVAL 1 MONTH)
)
SELECT
current_month,
ROUND(COUNT(DISTINCT CASE WHEN next_month IS NOT NULL THEN usr_id END) * 100.0 / COUNT(DISTINCT usr_id), 2) AS t_plus_1_month_retention_rate
FROM
next_month_logins
WHERE
current_month >= '2024-01-01' AND current_month < '2025-01-01'
GROUP BY
current_month
ORDER BY
current_month;
with cte as (
select usr_id , date_format(login_time,'%Y-%m-01') as login_month
from user_login_log
where date_format(login_time,'%Y-%m-01') between '2024-01-01' and '2025-01-01'
group by 1,2
),
cte2 as (
select usr_id,min(login_month) as first_login_month
from cte
group by 1
),
cte3 as (
select cte2.usr_id, cte2.first_login_month as current_month ,
cte.login_month as next_month
from cte2 left join cte on cte2.usr_id=cte.usr_id
and cte.login_month=date_add(cte2.first_login_month,interval 1 month)
)
select current_month ,
round(100*count(distinct case when next_month is not null then usr_id end)/count(distinct usr_id),2) as t_plus_1_month_retention_rate
from cte3
where current_month between '2024-01-01' and '2024-12-01'
group by 1
order by 1
with cte as (
select usr_id , date_format(login_time,'%Y-%m-01') as login_month
from user_login_log
where date_format(login_time,'%Y-%m-01') between '2024-01-01' and '2025-01-01'
group by 1,2
),
cte2 as (
select usr_id,min(login_month) as first_login_month
from cte
group by 1
),
cte3 as (
select cte2.usr_id, cte2.first_login_month as current_month ,
cte.login_month as next_month
from cte2 left join cte on cte2.usr_id=cte.usr_id
and cte.login_month=date_add(cte2.first_login_month,interval 1 month)
)
select current_month ,
round(100*count(distinct case when next_month is not null then usr_id end)/count(distinct usr_id),2) as t_plus_1_month_retention_rate
from cte3
where current_month between '2024-01-01' and '2025-01-01'
group by 1
order by 1
with cte as (
select usr_id , date_format(login_time,'%Y-%m-01') as login_month
from user_login_log
where date_format(login_time,'%Y-%m-01') between '2024-01-01' and '2025-01-01'
group by 1,2
),
cte2 as (
select usr_id,min(login_month) as first_login_month
from cte
group by 1
),
cte3 as (
select cte2.usr_id, cte2.first_login_month as current_month ,
cte.login_month as next_month
from cte2 left join cte on cte2.usr_id=cte.usr_id
and cte.login_month=date_add(cte2.first_login_month,interval 1 month)
)
select current_month ,
round(100*count(case when next_month is not null then usr_id end)/count(distinct usr_id),2) as t_plus_1_month_retention_rate
from cte3
where current_month between '2024-01-01' and '2024-12-01'
group by 1
order by 1
with cte as (
select usr_id , date_format(login_time,'%Y-%m-01') as login_month
from user_login_log
where date_format(login_time,'%Y-%m-01') between '2024-01-01' and '2024-12-01'
group by 1,2
),
cte2 as (
select usr_id,min(login_month) as first_login_month
from cte
group by 1
),
cte3 as (
select cte2.usr_id, cte2.first_login_month as current_month ,
cte.login_month as next_month
from cte2 left join cte on cte2.usr_id=cte.usr_id
and cte.login_month=date_add(cte2.first_login_month,interval 1 month)
)
select current_month ,
round(100*count(case when next_month is not null then usr_id end)/count(distinct usr_id),2) as t_plus_1_month_retention_rate
from cte3
where current_month between '2024-01-01' and '2024-11-01'
group by 1
order by 1
with cte as (
select usr_id, min(date_format(login_time,'%Y-%m-01')) as first_login_month
from user_login_log
where date_format(login_time,'%Y-%m-01')='2024-01-01'
group by 1
),
cte2 as (
select distinct usr_id, date_format(login_time,'%Y-%m-01') as login_month
from user_login_log
where usr_id in (select usr_id from cte)
)
select t.login_month as current_month ,
round(100*count(distinct t1.usr_id)/count(distinct t.usr_id),2) as t_plus_1_month_retention_rate
from cte2 as t left join cte2 as t1
on t.usr_id=t1.usr_id and t1.login_month = date_add(t.login_month,interval 1 month)
where t.login_month between '2024-01-01' and '2024-12-01'
group by 1
order by 1
with cte as (
select usr_id, min(date_format(login_time,'%Y-%m-01')) as first_login_month
from user_login_log
where date_format(login_time,'%Y-%m-01')='2024-01-01'
group by 1
),
cte2 as (
select distinct usr_id, date_format(login_time,'%Y-%m-01') as login_month
from user_login_log
where usr_id in (select usr_id from cte)
)
select t.login_month as current_month ,
round(100*count(distinct t1.usr_id)/count(distinct t.usr_id),2) as t_plus_1_month_retention_rate
from cte2 as t left join cte2 as t1
on t.usr_id=t1.usr_id and t1.login_month = date_add(t.login_month,interval 1 month)
where t.login_month between '2024-01-01' and '2024-11-01'
group by 1
order by 1
with cte as (
select usr_id,date_format(login_time,'%Y-%m-01') as login_month
from user_login_log
where date_format(login_time,'%Y-%m-01') between '2024-01-01' and '2024-12-01'
group by 1,2
)
select t.login_month as current_month ,
round(100*count(distinct t1.usr_id)/count(distinct t.usr_id),2) as t_plus_1_month_retention_rate
from cte as t left join cte as t1
on t.usr_id=t1.usr_id and t1.login_month=date_add(t.login_month,interval 1 month)
where t.login_month between '2024-01-01' and '2024-11-01'
group by 1
order by 1
with cte as (
select usr_id,date_format(login_time,'%Y-%m-01') as login_month
from user_login_log
where date_format(login_time,'%Y-%m-01') between '2024-01-01' and '2025-01-01'
group by 1,2
)
select t.login_month as current_month ,
round(100*count(distinct t1.usr_id)/count(distinct t.usr_id),2) as t_plus_1_month_retention_rate
from cte as t left join cte as t1
on t.usr_id=t1.usr_id and t1.login_month=date_add(t.login_month,interval 1 month)
where t.login_month between '2024-01-01' and '2024-12-01'
group by 1
order by 1
with cte as (
select usr_id,date_format(login_time,'%Y-%m-01') as login_month
from user_login_log
where date_format(login_time,'%Y-%m-01') between '2024-01-01' and '2024-12-01'
group by 1,2
)
select t.login_month as current_month ,
round(100*count(distinct t1.usr_id)/count(distinct t.usr_id),2) as t_plus_1_month_retention_rate
from cte as t left join cte as t1
on t.usr_id=t1.usr_id and t1.login_month=date_add(t.login_month,interval 1 month)
where t.login_month between '2024-01-01' and '2024-12-01'
group by 1
order by 1
with cte as (
select usr_id,date_format(login_time,'%Y-%m-01') as login_month
from user_login_log
where date_format(login_time,'%Y-%m-01') between '2024-01-01' and '2024-11-01'
group by 1,2
)
select t.login_month as current_month ,
round(100*count(distinct t1.usr_id)/count(distinct t.usr_id),2) as t_plus_1_month_retention_rate
from cte as t left join cte as t1
on t.usr_id=t1.usr_id and t1.login_month=date_add(t.login_month,interval 1 month)
where t.login_month between '2024-01=01' and '2024-11-01'
group by 1
order by 1
with cte as (
select usr_id,date_format(login_time,'%Y-%m-01') as login_month
from user_login_log
where date_format(login_time,'%Y-%m-01') between '2024-01-01' and '2024-12-01'
group by 1,2
)
select t.login_month as current_month ,
round(100*count(distinct t1.usr_id)/count(distinct t.usr_id),2) as t_plus_1_month_retention_rate
from cte as t left join cte as t1
on t.usr_id=t1.usr_id and t1.login_month=date_add(t.login_month,interval 1 month)
where t.login_month between '2024-01=01' and '2024-12-01'
group by 1
order by 1
with cte as (
select usr_id,date_format(login_time,'%Y-%m-01') as login_month
from user_login_log
where date_format(login_time,'%Y-%m-01') between '2024-01-01' and '2024-12-01'
group by 1,2
)
select t.login_month as current_month ,
round(100*count(distinct t1.usr_id)/count(distinct t.usr_id),2) as t_plus_1_month_retention_rate
from cte as t left join cte as t1
on t.usr_id=t1.usr_id and t1.login_month=date_add(t.login_month,interval 1 month)
group by 1
order by 1
with cte as (
select usr_id,date_format(login_time,'%Y-%m-01') as login_month
from user_login_log
where date_format(login_time,'%Y-%m-01') between '2024-01-01' and '2024-12-01'
group by 1,2
)
select t.login_month as current_month ,
round(100*count(distinct t1.usr_id)/count(distinct t.usr_id),2) as t_plus_1_month_retention_rate
from cte as t left join cte as t1
on t.usr_id=t1.usr_id and t1.login_month=date_add(t.login_month,interval 1 month)
group by 1
with cte as (
select usr_id,date_format(login_time,'%Y-%m-01') as login_month
from user_login_log
where date_format(login_time,'%Y-%m-01') between '2024-01-01' and '2024-11-01'
group by 1,2
)
select t.login_month as current_month ,
round(100*count(distinct t1.usr_id)/count(distinct t.usr_id),2) as t_plus_1_month_retention_rate
from cte as t left join cte as t1
on t.usr_id=t1.usr_id and t1.login_month=date_add(t.login_month,interval 1 month)
group by 1