/*
Netflix SQL Interview Question

Identify VIP Users for Netflix
    
To better cater to its most dedicated users, Netflix would like to identify its "VIP users" - those who are most active in terms of the number of hours of content they watch.
Write a SQL query that will retrieve the top 2 users with the most watched hours in the last month. 
*/
    
-- Schema Setup
CREATE TABLE users (
    user_id INTEGER PRIMARY KEY,
    sign_up_date DATE,
    subscription_type TEXT
);

CREATE TABLE watching_activity (
    activity_id INTEGER PRIMARY KEY,
    user_id INTEGER,
    date_time TIMESTAMP,
    show_id INTEGER,
    hours_watched FLOAT,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

INSERT INTO users (user_id, sign_up_date, subscription_type) VALUES
(1, '2022-01-10', 'Premium'),
(2, '2023-02-15', 'Standard'),
(3, '2021-07-20', 'Premium'),
(4, '2024-01-05', 'Basic'),
(5, '2022-09-30', 'Standard'),
(6, '2023-11-12', 'Premium'),
(7, '2021-12-01', 'Basic'),
(8, '2023-03-18', 'Standard'),
(9, '2022-05-25', 'Premium'),
(10, '2023-06-10', 'Basic');

INSERT INTO watching_activity (activity_id, user_id, date_time, show_id, hours_watched) VALUES
(1, 1, '2025-02-10 14:00:00', 101, 5.5),
(2, 2, '2025-02-12 16:30:00', 102, 3.2),
(3, 3, '2025-02-15 20:15:00', 103, 7.0),
(4, 4, '2025-02-18 12:45:00', 104, 2.5),
(5, 5, '2025-02-20 18:20:00', 105, 6.0),
(6, 6, '2025-02-22 10:00:00', 106, 8.3),
(7, 7, '2025-02-24 22:10:00', 107, 4.1),
(8, 8, '2025-02-26 19:30:00', 108, 9.0),
(9, 9, '2025-02-28 15:00:00', 109, 6.8),
(10, 10, '2025-02-28 21:10:00', 110, 3.9),
(11, 1, '2025-02-05 10:15:00', 111, 4.7),
(12, 2, '2025-02-06 12:30:00', 112, 5.2),
(13, 3, '2025-02-07 14:45:00', 113, 2.9),
(14, 4, '2025-02-08 17:00:00', 114, 6.4),
(15, 5, '2025-02-09 19:15:00', 115, 7.1);


-- Solution
WITH ranked_watch_hours AS (
    SELECT
        user_id,
        ROUND(SUM(hours_watched),1) AS total_watch_hours,
        DENSE_RANK() OVER (ORDER BY ROUND(SUM(hours_watched),1) DESC) AS rnk
    FROM watching_activity
    WHERE MONTH(date_time) = MONTH(CURDATE()) - 1
    GROUP BY user_id
)

SELECT
    u.user_id,
    u.sign_up_date,
    u.subscription_type,
    r.total_watch_hours
FROM ranked_watch_hours r
JOIN users u ON u.user_id = r.user_id
WHERE r.rnk <= 2;