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 |
Q1 2024 shipped orders from active clubsOutputrevenue_month | shipped_orders | issue_orders | cleaned_revenue
2024-01 | 2 | 1 | 35.00
2024-02 | 2 | 1 | 26.00
2024-03 | 1 | 1 | 0.00