Loading problem...
A commerce intelligence team wants a monthly view of meaningful order activity.
Table: orders
Task: For each calendar month, keep only orders where invoice > 20. Then compute:
Only return months that have at least one qualifying order.
Output requirements:
Supported submission environments:
orders:
| order_id | order_date | customer_id | invoice |
|----------|-------------|-------------|---------|
| 1 | 2024-01-03 | 10 | 18 |
| 2 | 2024-01-09 | 11 | 42 |
| 3 | 2024-01-20 | 10 | 35 |
| 4 | 2024-02-02 | 10 | 20 |
| 5 | 2024-02-16 | 12 | 21 |
| 6 | 2024-02-21 | 12 | 99 |
| 7 | 2024-03-07 | 13 | 15 |[
{"month":"2024-01","order_count":2,"customer_count":2},
{"month":"2024-02","order_count":2,"customer_count":1}
]Explanation
January keeps order 2 and 3. February keeps order 5 and 6. March has no invoice above 20, so it is omitted.
orders:
| order_id | order_date | customer_id | invoice |
|----------|-------------|-------------|---------|
| 101 | 2023-11-01 | 50 | 100 |
| 102 | 2023-11-05 | 50 | 120 |
| 103 | 2023-11-22 | 51 | 10 |
| 104 | 2023-12-01 | 50 | 22 |
| 105 | 2023-12-18 | 52 | 25 |
| 106 | 2024-01-04 | 52 | 20 |[
{"month":"2023-11","order_count":2,"customer_count":1},
{"month":"2023-12","order_count":2,"customer_count":2}
]Explanation
Customer 50 can contribute multiple qualifying orders in one month, but counts once for customer_count. January is excluded because invoice 20 does not pass the strict threshold.
orders:
| order_id | order_date | customer_id | invoice |
|----------|-------------|-------------|---------|
| 201 | 2025-05-02 | 900 | 5 |
| 202 | 2025-05-10 | 901 | 19 |
| 203 | 2025-06-01 | 900 | 20 |[]Explanation
No row has invoice greater than 20, so no month appears in the result.
Constraints