Task
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
Tables
customers
| column | type | description |
|---|
| customer_id | INT | Unique customer identifier |
| name | VARCHAR(255) | Customer name |
| email | VARCHAR(255) | Customer email |
| created_at | TIMESTAMP | Account creation timestamp |
products
| column | type | description |
|---|
| product_id | INT | Unique product identifier |
| name | VARCHAR(255) | Product name |
| category | VARCHAR(255) | Product category |
| price | DECIMAL(10,2) | Product price |
subscriptions
| column | type | description |
|---|
| subscription_id | INT | Unique subscription identifier |
| customer_id | INT | Customer reference |
| product_id | INT | Product reference |
| start_date | DATE | Subscription start date |
| next_shipment_date | DATE | Next scheduled shipment |
| status | VARCHAR(255) | Subscription status |
Sample Output
| customer_name | product_name | next_shipment_date | revenue |
|---|
| Alice | Dog Food | 2024-01-15 | 29.99 |
| Bob | Cat Litter | 2024-01-20 | 19.99 |
| Diana | Cat Treats | 2024-01-30 | 9.99 |
