Welcome to the SQL screen.
The question is on your right: Active Subscription Revenue by Customer. Read through the requirements and the three tables first.
Run and submit your code as often as you need. You also have five interviewer messages this session - want to talk through your approach, or are you ready to start coding?
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 |