Welcome to the SQL screen.
The question is on your right: Validate Monthly Revenue Data Quality. Read through the requirements and the four 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?
Write a PostgreSQL query that summarizes monthly shipped revenue quality for Literati book clubs in Q1 2024. The source data is messy: some order lines are duplicated, some prices or quantities are null, some orders are canceled, and some clubs are not active.
Your output should show the shipment month, the number of distinct shipped orders, how many of those orders had data issues, and cleaned revenue after deduplicating order lines and treating null amounts as 0.
| Column | Type | Description |
|---|---|---|
| club_id | ||
| club_name | ||
| status |
| Column | Type | Description |
|---|---|---|
| order_id | ||
| club_id | ||
| order_date | ||
| order_status |
| Column | Type | Description |
|---|---|---|
| order_item_id | ||
| order_id | ||
| book_id | ||
| quantity | ||
| unit_price | ||
| line_updated_at |
| Column | Type | Description |
|---|---|---|
| shipment_id | ||
| order_id | ||
| shipped_at | ||
| shipment_status |