SQL for Weekly Regional Performance

MediumSQL · PostgreSQL

Problem

Amazon Kuiper Commercial Services needs a weekly operational view by reporting region. Write a PostgreSQL query for the weeks beginning January 6, January 13, and January 20, 2025.

Requirements

Include every reporting region for every requested week, including weeks with no completed operations.
Count total handled volume using volume_units from completed operations joined to an active service channel.
Calculate average handling time in hours from opened_at to completed_at, rounded to two decimals.
Use LAG to calculate week-over-week volume change as a percentage. Return NULL when the prior week has zero or no volume.
Exclude non-reporting regions, incomplete operations, inactive channels, operations outside the three-week range, and operations with no matching region.
Sort by region name and week start.

Schema

regions

Column	Type	Description
region_idPK	INTEGER	Unique operational region identifier
region_name	VARCHAR(80)	Kuiper Commercial Services region name
is_reporting_region	BOOLEAN	Whether the region is included in reporting

service_channels

Column	Type	Description
channel_idPK	INTEGER	Unique service channel identifier
channel_name	VARCHAR(80)	Operational intake channel name
is_active	BOOLEAN	Whether the channel is active

operational_cases

Column	Type	Description
operation_idPK	BIGINT	Unique operation identifier
region_id	INTEGER	Region assigned to the operation
channel_id	INTEGER	Service channel used by the operation
status	VARCHAR(20)	Current operation status
volume_units	INTEGER	Number of units handled
opened_at	TIMESTAMP	Operation start timestamp
completed_at	TIMESTAMP	Operation completion timestamp

Tablesregionsservice_channelsoperational_cases