SQL for Monthly Aggregates

HardSQL · PostgreSQL

Problem

DigitalOcean's Financial Analytics team needs a monthly view of customer satisfaction for active paid accounts. Write a PostgreSQL query for January through April 2025.

Requirements

Return one row for every calendar month in the reporting period, including months with no valid responses.
Include only customers with an active paid subscription during each month and exclude internal accounts.
For each customer and month, retain only the most recent CSAT response.
Exclude NULL and out-of-range scores, where valid CSAT scores are integers from 1 through 5.
Return the monthly average score, the previous month's average, and the month-over-month change. Round score values to two decimal places.
Sort results chronologically.

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
customer_name	VARCHAR(120)	Customer account name
segment	VARCHAR(30)	Customer business segment
is_internal	BOOLEAN	Whether the account is an internal DigitalOcean account

subscriptions

Column	Type	Description
subscription_idPK	INT	Unique subscription identifier
customer_id	INT	Referenced customer
plan_name	VARCHAR(60)	DigitalOcean subscription plan
started_at	DATE	Subscription start date
ended_at	DATE	Subscription end date, if ended

csat_responses

Column	Type	Description
response_idPK	INT	Unique survey response identifier
customer_id	INT	Customer submitting the response
submitted_at	TIMESTAMP	Response submission timestamp
score	INT	Customer satisfaction score from 1 through 5
response_channel	VARCHAR(30)	Channel used to submit the response

Tablescustomerssubscriptionscsat_responses