SELECT 
    author_id,
    COUNT(*) AS ai_video_count
FROM ks_video_inf
WHERE if_AI_talking = 1
GROUP BY author_id
ORDER BY author_id;

WITH VideoCompletion AS (
    SELECT 
        v.video_id,
        v.screen_type,
        v.if_AI_talking,
        v.if_hint,
        COUNT(DISTINCT CASE WHEN TIMESTAMPDIFF(SECOND, u.start_time, u.end_time) >= v.duration THEN u.uid END) AS completed_views,
        COUNT(DISTINCT u.uid) AS total_views
    FROM 
        ks_video_inf v
    JOIN 
        ks_video_wat_log u ON v.video_id = u.video_id
    WHERE 
        u.start_time >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
    GROUP BY 
        v.video_id, v.screen_type, v.if_AI_talking, v.if_hint
),
PivotTable AS (
    SELECT 
        screen_type,
        SUM(CASE WHEN if_AI_talking = 1 AND if_hint = 1 THEN completed_views ELSE 0 END) / NULLIF(SUM(CASE WHEN if_AI_talking = 1 AND if_hint = 1 THEN total_views ELSE 0 END), 0) * 100 AS AI_with_hint,
        SUM(CASE WHEN if_AI_talking = 1 AND if_hint = 0 THEN completed_views ELSE 0 END) / NULLIF(SUM(CASE WHEN if_AI_talking = 1 AND if_hint = 0 THEN total_views ELSE 0 END), 0) * 100 AS AI_no_hint,
        SUM(CASE WHEN if_AI_talking = 0 AND if_hint = 1 THEN completed_views ELSE 0 END) / NULLIF(SUM(CASE WHEN if_AI_talking = 0 AND if_hint = 1 THEN total_views ELSE 0 END), 0) * 100 AS no_AI_with_hint,
        SUM(CASE WHEN if_AI_talking = 0 AND if_hint = 0 THEN completed_views ELSE 0 END) / NULLIF(SUM(CASE WHEN if_AI_talking = 0 AND if_hint = 0 THEN total_views ELSE 0 END), 0) * 100 AS no_AI_no_hint
    FROM 
        VideoCompletion
    GROUP BY 
        screen_type
)
SELECT 
    screen_type,
    ROUND(AI_with_hint, 2) AS AI_with_hint,
    ROUND(AI_no_hint, 2) AS AI_no_hint,
    ROUND(no_AI_with_hint, 2) AS no_AI_with_hint,
    ROUND(no_AI_no_hint, 2) AS no_AI_no_hint
FROM 
    PivotTable;

WITH a AS(SELECT
i.screen_type,
i.if_hint,
i.if_AI_talking,
CAST(COUNT(DISTINCT CASE WHEN timestampdiff(SECOND, w.start_time, w.end_time) > i.duration THEN w.uid END) / COUNT(DISTINCT w.uid) * 100 AS DECIMAL(10,2)) AS rate
FROM ks_video_inf i
JOIN ks_video_wat_log w ON i.video_id = w.video_id
WHERE w.start_time >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
GROUP BY
i.screen_type,
i.if_hint,
i.if_AI_talking),
f_a AS(SELECT screen_type,
 MAX(CASE WHEN if_hint = 1 AND if_AI_talking = 1 THEN rate END) AS AI_with_hint,
 MAX(CASE WHEN if_hint = 0 AND if_AI_talking = 1 THEN rate END) AS AI_no_hint,
 MAX(CASE WHEN if_hint = 1 AND if_AI_talking = 0 THEN rate END) AS no_AI_with_hint,
 MAX(CASE WHEN if_hint = 0 AND if_AI_talking = 0 THEN rate END) AS no_AI_no_hint
FROM a
GROUP BY screen_type)
SELECT screen_type, AI_with_hint,AI_no_hint, no_AI_with_hint, no_AI_no_hint
FROM f_a

WITH a AS(SELECT
i.screen_type,
i.if_hint,
i.if_AI_talking,
CAST(COUNT(DISTINCT CASE WHEN timestampdiff(SECOND, w.start_time, w.end_time) > i.duration THEN w.uid END) / COUNT(DISTINCT w.uid) * 100 AS DECIMAL(10,2)) AS rate
FROM ks_video_inf i
JOIN ks_video_wat_log w ON i.video_id = w.video_id
GROUP BY
i.screen_type,
i.if_hint,
i.if_AI_talking),
f_a AS(SELECT screen_type,
 MAX(CASE WHEN if_hint = 1 AND if_AI_talking = 1 THEN rate END) AS AI_with_hint,
 MAX(CASE WHEN if_hint = 0 AND if_AI_talking = 1 THEN rate END) AS AI_no_hint,
 MAX(CASE WHEN if_hint = 1 AND if_AI_talking = 0 THEN rate END) AS no_AI_with_hint,
 MAX(CASE WHEN if_hint = 0 AND if_AI_talking = 0 THEN rate END) AS no_AI_no_hint
FROM a
GROUP BY screen_type)
SELECT screen_type, AI_with_hint,AI_no_hint, no_AI_with_hint, no_AI_no_hint
FROM f_a

WITH a AS(SELECT
i.screen_type,
i.if_hint,
i.if_AI_talking,
CAST(COUNT(DISTINCT CASE WHEN timestampdiff(SECOND, w.start_time, w.end_time) > i.duration THEN w.uid END) / COUNT(DISTINCT w.uid) AS DECIMAL(10,2)) AS rate
FROM ks_video_inf i
JOIN ks_video_wat_log w ON i.video_id = w.video_id
GROUP BY
i.screen_type,
i.if_hint,
i.if_AI_talking),
f_a AS(SELECT screen_type,
 MAX(CASE WHEN if_hint = 1 AND if_AI_talking = 1 THEN rate END) AS AI_with_hint,
 MAX(CASE WHEN if_hint = 0 AND if_AI_talking = 1 THEN rate END) AS AI_no_hint,
 MAX(CASE WHEN if_hint = 1 AND if_AI_talking = 0 THEN rate END) AS no_AI_with_hint,
 MAX(CASE WHEN if_hint = 0 AND if_AI_talking = 0 THEN rate END) AS no_AI_no_hint
FROM a
GROUP BY screen_type)
SELECT screen_type, AI_with_hint,AI_no_hint, no_AI_with_hint, no_AI_no_hint
FROM f_a

WITH a AS(SELECT
i.screen_type,
i.if_hint,
i.if_AI_talking,
COUNT(DISTINCT CASE WHEN timestampdiff(SECOND, w.start_time, w.end_time) > i.duration THEN w.uid END) / COUNT(DISTINCT w.uid) AS rate
FROM ks_video_inf i
JOIN ks_video_wat_log w ON i.video_id = w.video_id
GROUP BY
i.screen_type,
i.if_hint,
i.if_AI_talking),
f_a AS(SELECT screen_type,
 MAX(CASE WHEN if_hint = 1 AND if_AI_talking = 1 THEN rate END) AS AI_with_hint,
 MAX(CASE WHEN if_hint = 0 AND if_AI_talking = 1 THEN rate END) AS AI_no_hint,
 MAX(CASE WHEN if_hint = 1 AND if_AI_talking = 0 THEN rate END) AS no_AI_with_hint,
 MAX(CASE WHEN if_hint = 0 AND if_AI_talking = 0 THEN rate END) AS no_AI_no_hint
FROM a
GROUP BY screen_type)
SELECT screen_type, AI_with_hint,AI_no_hint, no_AI_with_hint, no_AI_no_hint
FROM f_a

WITH daily_time AS(SELECT uid,
DATE(start_time),
SUM(CASE WHEN DATE(start_time) = DATE(end_time) THEN TIMESTAMPDIFF(SECOND, start_time, end_time)
WHEN DATE(start_time) < DATE(end_time) THEN TIMESTAMPDIFF(SECOND, TIME(start_time), '23:59:59') + TIMESTAMPDIFF(SECOND, '00:00:00', TIME(end_time))END) AS d_time
FROM ks_video_wat_log
GROUP BY uid,
DATE(start_time))
SELECT uid,CAST(AVG(d_time) AS DECIMAL(10, 0)) AS daily_avg_watch_time
FROM daily_time
GROUP BY uid
ORDER BY daily_avg_watch_time DESC
LIMIT 5

WITH daily_time AS(SELECT uid,
DATE(start_time),
SUM(CASE WHEN DATE(start_time) = DATE(end_time) THEN TIMESTAMPDIFF(SECOND, start_time, end_time)
WHEN DATE(start_time) < DATE(end_time) THEN TIMESTAMPDIFF(SECOND, TIME(start_time), '23:59:59') + TIMESTAMPDIFF(SECOND, '00:00:00', TIME(end_time))END) AS d_time
FROM ks_video_wat_log
GROUP BY uid,
DATE(start_time))
SELECT uid,CAST(AVG(d_time) AS DECIMAL(10, 0)) AS daily_avg_watch_time
FROM daily_time
GROUP BY uid
ORDER BY daily_avg_watch_time DESC

WITH daily_time AS(SELECT uid,
DATE(start_time),
SUM(CASE WHEN DATE(start_time) = DATE(end_time) THEN TIMESTAMPDIFF(SECOND, start_time, end_time)
WHEN DATE(start_time) < DATE(end_time) THEN TIMESTAMPDIFF(SECOND, TIME(start_time), '23:59:59') + TIMESTAMPDIFF(SECOND, '00:00:00', TIME(end_time))END) AS d_time
FROM ks_video_wat_log
GROUP BY uid,
DATE(start_time))
SELECT uid,CAST(AVG(d_time) AS DECIMAL(10,2)) AS daily_avg_watch_time
FROM daily_time
GROUP BY uid
ORDER BY daily_avg_watch_time DESC

WITH filtered AS(SELECT
 i.author_id,
SUM(w.if_like + w.if_retweet + w.if_fav) AS ax,
COUNT(w.comment_id) AS bx,
COUNT(DISTINCT i.video_id) AS nx
FROM ks_video_inf i
JOIN ks_video_wat_log w ON i.video_id = w.video_id
GROUP BYi.author_id)
SELECT author_id,
CAST((ax + bx) / nx AS DECIMAL(10,2)) AS avg_interaction_index
FROM filtered
ORDER BY avg_interaction_index DESC

SELECT 
    v.video_id,
    v.title,
    COALESCE(AVG(TIMESTAMPDIFF(SECOND, w.start_time, w.end_time) / v.duration), 0) AS avg_completion_rate
FROM ks_video_inf v
inner JOIN ks_video_wat_log w ON v.video_id = w.video_id
GROUP BY v.video_id, v.title
ORDER BY avg_completion_rate DESC;

SELECT video_id,
author_id,
title
FROM ks_video_inf
WHERE release_time >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
AND screen_type = 'p'
ORDER BY release_time DESC

SELECT video_id,
author_id,
title
FROM ks_video_inf
WHERE release_time >= DATE_SUB(NOW(), INTERVAL 7 DAY)
AND screen_type = 'p'
ORDER BY release_time DESC

SELECT i.video_id, i.title,
SUM(w.if_like) AS like_count
FROM ks_video_inf i
JOIN ks_video_wat_log w ON i.video_id = w.video_id
GROUP BY i.video_id, i.title
ORDER BY like_count DESC

SELECT i.video_id, i.title,
COUNT(w.if_like) AS like_count
FROM ks_video_inf i
JOIN ks_video_wat_log w ON i.video_id = w.video_id
GROUP BY i.video_id, i.title
ORDER BY like_count DESC

SELECT uid,
AVG(TIMESTAMPDIFF(SECOND, start_time, end_time)) AS avg_watch_duration
FROM ks_video_wat_log
GROUP BY uid
ORDER BY avg_watch_duration desc

WITH selected_uid AS(SELECT w.uid
FROM ks_video_wat_log w
JOIN ks_video_inf i ON w.video_id = i.video_id
WHERE i.release_time >= DATE_SUB(now(), INTERVAL 1 MONTH)
AND i.duration >= 180
AND (timestampdiff(second, w.start_time, w.end_time) - i.duration) >= 0
GROUP BY w.uid
HAVING COUNT(DISTINCT w.video_id) >= 2)
SELECT COUNT(DISTINCT uid) AS total_users
FROM selected_uid

SELECT DISTINCT CASE WHEN timestampdiff(second, w.start_time, w.end_time) - i.duration >= 0 AND i.duration >= 180 THEN w.uid END
FROM ks_video_wat_log w
JOIN ks_video_inf i ON w.video_id = i.video_id
WHERE i.release_time >= DATE_SUB(now(), INTERVAL 1 MONTH)

SELECT i.video_id,
i.title,
ROUND(COUNT(DISTINCT CASE WHEN TIMESTAMPDIFF(SECOND, w.start_time, w.end_time) - i.duration >= 0 THEN uid END)/COUNT(distinct uid) * 100, 4) AS completion_rate
FROM ks_video_inf i
JOIN ks_video_wat_log w ON i.video_id = w.video_id
WHERE i.release_time >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
GROUP BY i.video_id, i.title
ORDER BY completion_rate desc
LIMIT 5

SELECT i.video_id,
i.title,
CAST(SUM(CASE WHEN(TIMESTAMPDIFF(SECOND, w.start_time, w.end_time) - i.duration) >= 0 THEN 1 ELSE 0 END)/ COUNT(*) *100 AS DECIMAL(10,2)) AS completion_rate
FROM ks_video_wat_log w
JOIN ks_video_inf i ON w.video_id = i.video_id
WHERE i.release_time >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
GROUP BY i.video_id, i.title
ORDER BY completion_rate desc
LIMIT 5