SQL for Streaming Analytics | Dataford Interview Questions

Your question is SQL for Streaming Analytics. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

Roku analytics needs a monthly view of streaming time for valid Roku profiles during Q2 2020. Write a PostgreSQL query that aggregates session duration for April, May, and June 2020.

Requirements

Join streaming_sessions to roku_profiles so sessions are included only when they belong to a known Roku profile.
Filter sessions from April 1, 2020, inclusive, through July 1, 2020, exclusive.
Group by calendar month, calculate total streaming minutes, treat NULL durations as zero, and round the total to the nearest whole number.
Return one row per month in chronological order, with the columns month and total_streaming_minutes.

Schema

roku_profiles

Column	Type	Description
profile_idPK	INT	Unique Roku profile identifier
profile_name	VARCHAR(100)	Profile display name
device_model	VARCHAR(50)	Roku device model

streaming_sessions

Column	Type	Description
session_idPK	INT	Unique streaming session identifier
profile_id	INT	References roku_profiles.profile_id
started_at	TIMESTAMP	Session start timestamp
duration_minutes	NUMERIC(10,2)	Session duration in minutes