Monthly High-Value Order and Buyer Counts

Easy

A commerce intelligence team wants a monthly view of meaningful order activity. Table: orders order_id: unique identifier of the order order_date: date when the order was placed customer_id: identifier of the customer invoice: billed amount for that order Task: For each calendar month, keep only orders where invoice > 20. Then compute: order_count: number of qualifying orders in that month customer_count: number of distinct customers among qualifying orders in that month Only return months that have at least one qualifying order. Output requirements: Return columns in this exact order: month order_count customer_count month must use YYYY-MM format Sort by month ascending Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

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      |

Output

[
  {"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.

Example 2

Input

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      |

Output

[
  {"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.

Example 3

Input

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      |

Output

[]

Explanation

No row has invoice greater than 20, so no month appears in the result.

Constraints

1 <= number of rows in orders <= 300000
order_id is unique in orders
1 <= order_id <= 1000000000
1 <= customer_id <= 1000000000
0 <= invoice <= 1000000
order_date is a valid date in YYYY-MM-DD format
Use strict threshold: include only rows where invoice > 20
Return only months with at least one qualifying order
Return exactly three columns: month, order_count, customer_count
month must be formatted as YYYY-MM
Sort by month ascending
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with required columns

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 |

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 |

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 |