Monthly Shipments and Returns by Region

Problem

Table: `Shipments`

```text
+------------+---------+----------+--------+-------------+
| Column | Type | ... |
+------------+---------+----------+--------+-------------+
| ship_id | int | primary key |
| region | varchar | two-letter region code |
| status | varchar | 'fulfilled' or 'cancelled' |
| value_cents| int | order value |
| ship_date | date | date the order shipped |
+------------+---------+----------+--------+-------------+
```

Table: `Returns`

```text
+-------------+---------+
| Column | Type |
+-------------+---------+
| ship_id | int |
| return_date | date |
+-------------+---------+
Each row records that the order ship_id was returned on return_date. ship_id
references Shipments.ship_id.
```

For each month-and-region combination, report the number of **fulfilled** shipments and the total value of those fulfilled shipments, alongside the number of **returns** and the total value of those returned shipments. A fulfilled shipment is counted in the month it shipped; a return is counted in the month it was returned (which may differ from the ship month).

Return `month` (formatted `YYYY-MM`), `region`, `fulfilled_count`, `fulfilled_value`, `return_count`, `return_value`. A month-region appears if it has at least one fulfilled shipment or at least one return.

Tables

Example rows — the live problem includes the full dataset.

Expected output

Your answer should return 3 rows with the columns month, region, fulfilled_count, fulfilled_value, return_count, return_value.

Starter code (SQL)

SELECT *
FROM Shipments;

Related SQL questions

• 3-Day Tweet Rolling AverageMedium
• 7-Day vs 28-Day Rolling Avg DivergenceMedium
• A/B Test Sample Ratio Mismatch (SRM) CheckMedium
• Accounts Streaming From Two Devices at OnceMedium
• Ad Campaign Funnel AnalysisMedium
• Agent Resolution RateMedium