/*
Amazon SQL Interview Question

Given the users' sessions logs on a particular day, calculate how many hours each user was active that day.
Note: The session starts when state = 1 and ends when state = 0.
*/

-- Schema Setup
CREATE TABLE customer_state_log (cust_id VARCHAR(10),state INT,timestamp TIME);

INSERT INTO customer_state_log (cust_id, state, timestamp) VALUES('c001', 1, '07:00:00'),('c001', 0, '09:30:00'),('c001', 1, '12:00:00'),('c001', 0, '14:30:00'),('c002', 1, '08:00:00'),('c002', 0, '09:30:00'),('c002', 1, '11:00:00'),('c002', 0, '12:30:00'),('c002', 1, '15:00:00'),('c002', 0, '16:30:00'),('c003', 1, '09:00:00'),('c003', 0, '10:30:00'),('c004', 1, '10:00:00'),('c004', 0, '10:30:00'),('c004', 1, '14:00:00'),('c004', 0, '15:30:00'),('c005', 1, '10:00:00'),('c005', 0, '14:30:00'),('c005', 1, '15:30:00'),('c005', 0, '18:30:00');

-- Solution
WITH start_end_timestamps AS
(
SELECT
    cust_id,
    CASE WHEN state = 1 THEN timestamp END AS start_time,
    CASE WHEN state = 0 THEN timestamp END AS end_time,
    ROW_NUMBER() OVER (PARTITION BY cust_id ORDER BY timestamp) AS rn
FROM customer_state_log
)
    
SELECT
    st1.cust_id,
    ROUND(SUM(TIMESTAMPDIFF(MINUTE,st1.start_time,st2.end_time)) / 60,1) AS active_hours
FROM start_end_timestamps st1
JOIN start_end_timestamps st2
ON st2.cust_id = st1.cust_id AND st2.rn = st1.rn + 1
WHERE st1.start_time IS NOT NULL
GROUP BY st1.cust_id;