Drill 4 of 5 in Streaming questions
0

Interview Question Bank · Timestamp intervals · EXTRACT(EPOCH) · half-open date ranges

4. Viewing hours by device, midnight sessions included

EasyStreamingQuestion #265

Concept: Timestamp intervals · EXTRACT(EPOCH) · half-open date rangesshow

Subtracting two timestamps gives an interval, and EXTRACT(EPOCH FROM interval) turns it into seconds, so dividing by 3600 gives hours with the minutes kept. To bucket by day, cast the timestamp that defines the day to ::date. Filter timestamps with a half-open range (>= first day AND < the day after the last) because BETWEEN '...' AND 'last day' stops at midnight of the last day.

Example (different data):

SELECT clock_in::date AS shift_date,
  ROUND(SUM(EXTRACT(EPOCH FROM clock_out - clock_in)) / 3600, 2) AS hours
FROM shifts
WHERE clock_in >= '2025-05-01' AND clock_in < '2025-05-08'
GROUP BY 1;

Your task

Pinewood Play, a video streaming service, reports viewing time by device every day. A session counts toward the day it started, even when it runs past midnight.

For sessions that started on 2025-03-01 or 2025-03-02, return view_date (the date the session started), device_type, sessions and hours_watched (total ended_at - started_at in hours, rounded to 2 decimals). Sort by view_date, then device_type.

Starting Postgres in your browser…

Checking your access…