Monthly High-Value Order and Buyer Counts

EASY10 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

orders =

[{"order_id":1,"order_date":"2024-01-03","customer_id":10,"invoice":18},{"order_id":2,"order_date":"2024-01-09","customer_id":11,"invoice":42},{"order_id":3,"order_date":"2024-01-20","customer_id":10,"invoice":35},{"order_id":4,"order_date":"2024-02-02","customer_id":10,"invoice":20},{"order_id":5,"order_date":"2024-02-16","customer_id":12,"invoice":21},{"order_id":6,"order_date":"2024-02-21","customer_id":12,"invoice":99},{"order_id":7,"order_date":"2024-03-07","customer_id":13,"invoice":15}]

Monthly High-Value Order and Buyer Counts

Hints

Monthly High-Value Order and Buyer Counts

Hints