Your question is Validate Monthly Revenue Data Quality. Start with the requirements and the four 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.
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 |