Your question is Active Subscription Revenue by Customer. 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.
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.
revenuestatus = 'active'subscriptions to customers and products using their keys| 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 |
| Column | Type | Description |
|---|---|---|
| customer_idPK | INT | Unique identifier for each customer |
| name | VARCHAR(255) | Customer's full name |
| VARCHAR(255) | Customer email address | |
| created_at | TIMESTAMP | Timestamp when the customer account was created |
| 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 |
| 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 |