SQL for Sales Aggregations | Dataford Interview Questions

Your question is SQL for Sales Aggregations. 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

Sanofi's commercial analytics team needs a regional view of completed sales for calendar year 2025. Write a PostgreSQL query that reports metrics for every sales region, including regions with no qualifying transactions.

Requirements

Return each region's name, total completed sales, and average completed transaction amount.
Include only transactions with status = 'Completed' dated from 2025-01-01 through 2025-12-31.
Preserve regions with no qualifying transactions and show their metrics as 0.00.
Sort by total sales descending, then region name ascending.

Schema

sales_regions

Column	Type	Description
region_idPK	INT	Unique identifier for a sales region
region_name	VARCHAR(100)	Commercial region name

sales_transactions

Column	Type	Description
transaction_idPK	INT	Unique identifier for a sales transaction
region_id	INT	Region associated with the transaction
product_name	VARCHAR(120)	Sanofi product or portfolio name
sale_amount	DECIMAL(12,2)	Transaction sales amount
transaction_date	DATE	Date on which the transaction occurred
status	VARCHAR(20)	Transaction processing status