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
Unique Orders and Customers Per Month INPUT: Orders Table order_id cust_id invoice date 1 101 $25 Jan 2 102 $15 Jan 3 101 $30 Jan 4 103 $45 Feb 5 101 $22 Feb 6 104 $18 Feb Filter Condition: invoice > $20 Data Summary - 6 total orders - 4 unique customers - 2 months (Jan, Feb) ALGORITHM STEPS 1 Filter Records WHERE invoice > 20 Keep only qualifying rows 2 Extract Month DATE_FORMAT(date, '%Y-%m') AS month 3 Group By Month GROUP BY month Aggregate per period 4 Count Distinct COUNT(DISTINCT order_id) COUNT(DISTINCT cust_id) SELECT month, COUNT(DISTINCT order_id), COUNT(DISTINCT cust_id) FROM Orders WHERE... FINAL RESULT month orders custs Jan 2 1 Feb 2 2 January Analysis Orders > $20: #1($25), #3($30) Unique Orders: 2 Unique Customer: 1 (cust 101) February Analysis Orders > $20: #4($45), #5($22) Unique Orders: 2 Unique Customers: 2 (103,101) OK - Done Key Insight: Use COUNT(DISTINCT column) to count unique values per group. The WHERE clause filters BEFORE grouping, so only orders with invoice > $20 are considered. GROUP BY month partitions data for monthly aggregation. DATE_FORMAT or EXTRACT functions help convert dates to year-month format for proper grouping. TutorialsPoint - Unique Orders and Customers Per Month | Optimal Solution

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