select 
	t1.login_date,
concat(round(count(distinct t2.usr_id)*100/count(distinct t1.usr_id),2),"%") as T1_retention_rate
from 
(select distinct usr_id ,date(login_time) as login_date
 from user_login_log 
 where date(login_time)>=date_sub(curdate(),interval 30 day)) t1
 left join 
 (select distinct usr_id,date(login_time) as login_date
 from user_login_log
 ) t2 
 on t1.usr_id=t2.usr_id and t2.login_date=date_add(t1.login_date,interval 1 day)
group by t1.login_date
 order by t1.login_date asc

SELECT 
t1.login_date,
CONCAT(
ROUND(COUNT(DISTINCT t2.usr_id) * 100.0 / COUNT(DISTINCT t1.usr_id), 2), 
'%'
) AS T1_retention_rate
FROM 
(
SELECT DISTINCT usr_id, DATE(login_time) AS login_date 
FROM user_login_log
WHERE login_time >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
) t1
LEFT JOIN 
(
SELECT DISTINCT usr_id, DATE(login_time) AS login_date 
FROM user_login_log
) t2 
ON t1.usr_id = t2.usr_id 
 AND t2.login_date = DATE_ADD(t1.login_date, INTERVAL 1 DAY)
GROUP BY 
t1.login_date
ORDER BY 
t1.login_date ASC;

SELECT 
t1.login_date AS T_date,
ROUND(COUNT(DISTINCT t2.usr_id) * 100.0 / COUNT(DISTINCT t1.usr_id), 2) AS next_day_retention_rate
FROM 
(
SELECT DISTINCT usr_id, DATE(login_time) AS login_date 
FROM user_login_log
WHERE login_time >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
) t1
LEFT JOIN 
(
SELECT DISTINCT usr_id, DATE(login_time) AS login_date 
FROM user_login_log
) t2 
ON t1.usr_id = t2.usr_id 
 AND t2.login_date = DATE_ADD(t1.login_date, INTERVAL 1 DAY) 
GROUP BY 
t1.login_date
ORDER BY 
T_date DESC;

with qihuoguo as(
select 
	usr_id
from user_login_log
WHERE login_time >= '2024-07-01' AND login_time < '2024-08-01'
group by usr_id 
having COUNT(*) >= 10
),
 qihouhuo as(
 SELECT 
usr_id
FROM
 user_login_log
WHERE login_time >= '2024-08-01'
GROUP BY usr_id, DATE_FORMAT(login_time, '%Y-%m'
)
HAVING COUNT(*) >= 10
 )
SELECT 
COUNT(DISTINCT usr_id) AS
 inactive_user_count
FROM 
qihuoguo
WHERE 
usr_id 
NOT IN (SELECT usr_id FROM
 qihouhuo)

select count(distinct usr_id )from
(
select 
	usr_id
from user_login_log
group by usr_id 
having count(case when date_format(login_time,'%Y-%m')="2024-07" then 1 else 0 end)>=10 and max(login_time)<'2024-08-01' ) as f

select
	count(distinct usr_id) as inactive_user_count
from user_login_log
where login_time<='2024-10-01' andusr_id not in 
(selectdistinct usr_id
 from user_login_log 
 where login_time >'2024-10-01')

with first as(
select
	distinct usr_id
from user_login_log
where login_time<='2024-10-01')
select
 count(*) as inactive_user_count
from first

with first as(
select
	distinct usr_id
from user_login_log
where login_time<'2024-10-02')
select
 count(*) as inactive_user_count
from first

with first as(
select
	distinct usr_id
from user_login_log
where login_time<'2024-10-02 00:00')
select
 count(*) as inactive_user_count
from first

with first as(
select
	distinct usr_id
from user_login_log
where login_time>'2024-10-02 00:00')
select
 count(*) as inactive_user_count
from first

with first as(
select
	usr_id
from user_login_log
where login_time>'2024-10-02 00:00')
select
 count(*) as inactive_user_count
from first

with first as(
select
	usr_id
from user_login_log
where login_time>'2024-10-01 23:59')
select
 count(*) as inactive_user_count
from first

with shijianhua as(
select t1.live_id,t1.enter_time as events,1 as flag from ks_live_t1 t1
UNION ALL
select t1.live_id,t1.leave_time as events,-1 as flag from ks_live_t1 t1
),
leijia as(
select
	live_id,
sum(s.flag) over(partition by s.live_id order by s.events asc ,s.flag desc ) as online_users
from shijianhua s)
select 
	leijia.live_id,
ks_live_t2.live_nm,
max(online_users) as max_online_users
from leijia join ks_live_t2 on leijia.live_id=ks_live_t2.live_id
group by leijia.live_id,
ks_live_t2.live_nm
order by max_online_users desc

select	
 	t1.live_id,
t2.live_nm,
count(distinct usr_id) as online_users
from ks_live_t1 t1 join ks_live_t2 t2 on t1.live_id=t2.live_id
where t1.enter_time <= '2021-09-12 23:38:38' and '2021-09-12 23:38:38' <=t1.leave_time
group byt1.live_id,
t2.live_nm
order by online_users desc

select	
 	t1.live_id,
t2.live_nm,
count(distinct usr_id)
from ks_live_t1 t1 join ks_live_t2 t2 on t1.live_id=t2.live_id
where t1.enter_time <= '2021-09-12 23:38:38' and '2021-09-12 23:38:38' <=t1.leave_time
group byt1.live_id,
t2.live_nm

with first as (
select
 	 u.uid,
 date(start_time) as date,
 sum(timestampdiff(second,u.start_time,u.end_time)) as total
 from ks_video_wat_log u join ks_video_inf v on u.video_id =v.video_id
 group by u.uid,date)
 select
 	uid,
round(sum(total)/count(*),0) as daily_avg_watch_time
from first
group by uid 
ORDER BY daily_avg_watch_time DESC limit 5

WITH VideoInteractions AS (
    SELECT 
        v.author_id,
        SUM(
            w.if_like +
            CASE WHEN w.comment_id IS NOT NULL THEN 1 ELSE 0 END +
            w.if_retweet +
            w.if_fav
        ) AS total_interactions,
        COUNT(DISTINCT v.video_id) AS video_count
    FROM ks_video_inf v
    JOIN ks_video_wat_log w ON v.video_id = w.video_id
    GROUP BY v.author_id
)
SELECT 
    author_id,
    round(total_interactions * 1.0 / video_count,2) AS avg_interaction_index
FROM VideoInteractions
ORDER BY avg_interaction_index DESC;

select 
	v.video_id,
v.title,
avg(timestampdiff(second,u.start_time,u.end_time)/v.duration) as avg_completion_rate
from ks_video_wat_log u join ks_video_inf v on u.video_id=v.video_id
group by v.video_id,v.title
order by avg_completion_rate desc

select 
v.video_id,v.author_id,v.title
from ks_video_inf v 
where v. screen_type='p' and v.release_time>date_sub(curdate(),interval 7 day)
order by v.release_time desc

select
 v.video_id,
 v.title,
 sum(case when u.if_like =1 then 1 else 0 end ) as like_count
from ks_video_wat_log u join ks_video_inf v on u.video_id=v.video_id
group by v.video_id,v.title
order by like_count desc