WITH week_stats AS (
SELECT p.id, p.sid, s.count,
( SELECT name FROM website_RU1.series_ WHERE id = p.sid ) series_name,
( SELECT name FROM website_RU1.program_ WHERE id = p.id ) program_name,
( SELECT episode_number FROM website_RU1.program_ WHERE id = p.id ) episode_number,
( SELECT first_broadcast FROM website_RU1.program_ WHERE id = p.id ) first_broadcast,
( SELECT CONCAT('https://www.tv7.fi/exodus-interfaces/image?id=', image_id) FROM website_RU1.program_ WHERE id = p.id ) image_path,
( SELECT duration FROM website_RU1.program_ WHERE id = p.id ) duration
FROM
website_WOWZA.date_stats s
JOIN website_WOWZA.program p ON s.pid = p.id
WHERE ( s.date <= '2025-09-15'
AND s.date >= '2025-09-08' )
AND s.cid = 'RU1'
),
selected_series AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY sid ORDER BY first_broadcast DESC) AS rn
FROM week_stats
WHERE sid IN (563,305)
),
another_programs AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY sid ORDER BY count DESC) AS rn
FROM week_stats
WHERE sid NOT IN (563,305)
),
another_programs_top AS (
SELECT *
FROM another_programs
WHERE rn = 1
ORDER BY count DESC
LIMIT 14
)
SELECT * FROM selected_series WHERE rn = 1
UNION ALL
SELECT * FROM another_programs_top
