SQL Joins and Aggregates | Dataford Interview Questions

Your question is SQL Joins and Aggregates. Start with the requirements and the three 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

AT&T Wireless needs a monthly usage report for active subscribers. Write a PostgreSQL query that reports each active subscriber's May 2025 mobile data usage, including subscribers with no usage events.

Requirements

Join subscribers to plans and retain only rows where status = 'active'.
Aggregate data_mb and usage-event count from usage_records for May 2025 only.
Use a LEFT JOIN and COALESCE so active subscribers without usage appear with zero totals.
Sort by total data usage descending, then subscriber ID ascending.

Schema

subscribers

Column	Type	Description
subscriber_idPK	INT	Unique subscriber identifier
subscriber_name	VARCHAR(100)	Subscriber's name
plan_id	INT	Subscribed wireless plan
status	VARCHAR(20)	Current account status

plans

Column	Type	Description
plan_idPK	INT	Unique plan identifier
plan_name	VARCHAR(100)	AT&T wireless plan name

usage_records

Column	Type	Description
usage_idPK	INT	Unique usage event identifier
subscriber_id	INT	Subscriber associated with the event
usage_date	DATE	Date of the usage event
data_mb	INT	Mobile data consumed in megabytes