Monthly Revenue by Segment | Dataford Interview Questions

Your question is Monthly Revenue by Segment. 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

Regions Financial wants a monthly view of revenue generated across its customer segments. Write a PostgreSQL query that summarizes posted transaction revenue for the first quarter of 2025.

Requirements

Join customers to customer_segments and card_transactions using their related keys.
Include only transactions with status = 'posted' from January through March 2025, using a half-open date range.
Group revenue by calendar month and customer segment, and sort chronologically by month and alphabetically by segment.
Ignore transactions that cannot be associated with a valid customer or segment.

Schema

customer_segments

Column	Type	Description
segment_idPK	INT	Unique customer segment identifier
segment_name	VARCHAR(50)	Business name of the customer segment

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
customer_name	VARCHAR(100)	Customer's display name
segment_id	INT	Reference to customer_segments

card_transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
customer_id	INT	Reference to customers
posted_at	DATE	Date the transaction was posted
revenue_amount	DECIMAL(12,2)	Revenue amount associated with the transaction
status	VARCHAR(20)	Processing status of the transaction