Unique Orders and Customers Per Month - Problem

Given a table Orders containing order information, write a SQL solution to find the number of unique orders and the number of unique customers with invoices greater than $20 for each different month.

The table structure is:

  • order_id: Unique identifier for each order
  • order_date: Date when the order was placed
  • customer_id: ID of the customer who placed the order
  • invoice: Invoice amount for the order

Return the result table showing monthly statistics, sorted in any order.

Table Schema

Orders
Column Name Type Description
order_id PK int Unique identifier for each order
order_date date Date when the order was placed
customer_id int ID of the customer who placed the order
invoice int Invoice amount in dollars
Primary Key: order_id
Note: Each row represents one order with unique order_id

Input & Output

Example 1 — Multiple Orders in Different Months
Input Table:
order_id order_date customer_id invoice
1 2021-01-01 1 30
2 2021-01-02 2 40
3 2021-01-03 3 70
4 2021-02-07 1 30
5 2021-02-09 4 80
Output:
month order_count customer_count
2021-01-01 3 3
2021-02-01 2 2
💡 Note:

In January 2021, there are 3 unique orders (1, 2, 3) from 3 unique customers (1, 2, 3) with invoices > 20. In February 2021, there are 2 unique orders (4, 5) from 2 unique customers (1, 4) with invoices > 20.
Example 2 — Orders Below Threshold Filtered Out
Input Table:
order_id order_date customer_id invoice
1 2021-01-01 1 30
2 2021-01-02 2 15
3 2021-01-03 3 25
Output:
month order_count customer_count
2021-01-01 2 2
💡 Note:

Only orders with invoice > 20 are counted. Order 2 with invoice $15 is filtered out, so only orders 1 and 3 from customers 1 and 3 are included in the January count.
Example 3 — Same Customer Multiple Orders Same Month
Input Table:
order_id order_date customer_id invoice
1 2021-01-01 1 30
2 2021-01-15 1 25
3 2021-01-20 2 40
Output:
month order_count customer_count
2021-01-01 3 2
💡 Note:

Customer 1 has 2 different orders in January, so order_count is 3 (all unique orders) but customer_count is 2 (customers 1 and 2, with customer 1 counted only once despite multiple orders).

Constraints

  • 1 ≤ order_id ≤ 10^6
  • order_date is a valid date
  • 1 ≤ customer_id ≤ 10^6
  • 1 ≤ invoice ≤ 10^6

Visualization

Tap to expand
Monthly Orders and Customers AnalysisInput: Orders Tableorder_iddatecustomerinvoice101-01130201-02215302-07125Filter: invoice > 20GROUP BYmonthOutput: Monthly Summarymonthorderscustomers2021-01112021-0211COUNT DISTINCT per month
Understanding the Visualization
1
Filter
Keep orders with invoice > 20
2
Group by Month
Extract month from order_date
3
Count Distinct
Unique orders and customers per month
Key Takeaway
🎯 Key Insight: Use DATE_TRUNC for month grouping and COUNT DISTINCT for unique value counting in aggregation queries

Related Problems

Asked in
Amazon 23 Microsoft 18 Google 15
23.4K Views
Medium Frequency
~12 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen