Active Subscription Revenue by Customer | Dataford Interview Questions

PetBox wants a report of currently active subscriptions and the revenue tied to each subscribed product. Write a SQL query to return one row per active subscription.

Requirements

Return the customer name, product name, next shipment date, and product price as revenue
Include only subscriptions where status = 'active'
Join subscriptions to customers and products using their keys
Order the final result by customer name, then product name

Sample Output

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique identifier for each customer
name	VARCHAR(255)	Customer's full name
email	VARCHAR(255)	Customer email address
created_at	TIMESTAMP	Timestamp when the customer account was created

products

Column	Type	Description
product_idPK	INT	Unique identifier for each product
name	VARCHAR(255)	Product name
category	VARCHAR(255)	Product category
price	DECIMAL(10,2)	Product price

subscriptions

Column	Type	Description
subscription_idPK	INT	Unique identifier for each subscription
customer_id	INT	Reference to the customer
product_id	INT	Reference to the product
start_date	DATE	Date the subscription started
next_shipment_date	DATE	Date of the next scheduled shipment
status	VARCHAR(255)	Current subscription status

Your question is Active Subscription Revenue by Customer. 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