SQL Revenue by Subscription Tier | Dataford Interview Questions

Given a schema with users, subscriptions, and payments, write a SQL query using joins and group by to find the total revenue generated per subscription tier last month.

Use calendar-month boundaries and include payment amounts recorded during the previous calendar month.

Output

Return one row per subscription tier with columns subscription_tier and total_revenue.
Include only tiers with matching users, subscriptions, and payments from last month.
Order results alphabetically by subscription_tier.

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
email	VARCHAR(255)	User email address

subscriptions

Column	Type	Description
subscription_idPK	INT	Unique subscription identifier
user_id	INT	User who owns the subscription
tier	VARCHAR(50)	Subscription tier

payments

Column	Type	Description
payment_idPK	INT	Unique payment identifier
subscription_id	INT	Subscription associated with the payment
amount	NUMERIC(10,2)	Payment amount
paid_at	DATE	Date the payment was recorded

Your question is SQL Revenue by Subscription Tier. Start with the requirements and the three tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem