Table: Orders
+---------------+---------+ | Column Name | Type | +---------------+---------+ | order_id | int | | order_date | date | | customer_id | int | | invoice | int | +---------------+---------+ order_id is the column with unique values for this table. This table contains information about the orders made by customer_id.
Write a solution to find the number of unique orders and the number of unique customers with invoices > $20 for each different month.
Return the result table sorted in any order.
The result format is in the following example.
Example 1:
Input: Orders table: +----------+------------+-------------+------------+ | order_id | order_date | customer_id | invoice | +----------+------------+-------------+------------+ | 1 | 2020-09-15 | 1 | 30 | | 2 | 2020-09-17 | 2 | 90 | | 3 | 2020-10-06 | 3 | 20 | | 4 | 2020-10-20 | 3 | 21 | | 5 | 2020-11-10 | 1 | 10 | | 6 | 2020-11-21 | 2 | 15 | | 7 | 2020-12-01 | 4 | 55 | | 8 | 2020-12-03 | 4 | 77 | | 9 | 2021-01-07 | 3 | 31 | | 10 | 2021-01-15 | 2 | 20 | +----------+------------+-------------+------------+ Output: +---------+-------------+----------------+ | month | order_count | customer_count | +---------+-------------+----------------+ | 2020-09 | 2 | 2 | | 2020-10 | 1 | 1 | | 2020-12 | 2 | 1 | | 2021-01 | 1 | 1 | +---------+-------------+----------------+ Explanation: In September 2020 we have two orders from 2 different customers with invoices > $20. In October 2020 we have two orders from 1 customer, and only one of the two orders has invoice > $20. In November 2020 we have two orders from 2 different customers but invoices < $20, so we don't include that month. In December 2020 we have two orders from 1 customer both with invoices > $20. In January 2021 we have two orders from 2 different customers, but only one of them with invoice > $20.
Problem Overview: You are given an orders table containing order_id, customer_id, and order_date. The task is to compute, for each month, the number of unique orders placed and the number of unique customers who placed them. The result should aggregate records by month and return distinct counts for both identifiers.
Approach 1: Conditional Filtering + Grouping Statistics (O(n) time, O(1) extra space in SQL)
Scan the table once and group rows by the month extracted from order_date. SQL aggregation functions handle the heavy lifting. Use DATE_FORMAT(order_date, '%Y-%m') (or an equivalent month extraction function) to normalize dates into monthly buckets. Inside each group, compute COUNT(DISTINCT order_id) and COUNT(DISTINCT customer_id). The key insight is that relational databases are optimized for aggregation and distinct counting, so the entire problem becomes a single grouped query without explicit iteration logic.
When a condition is required (for example filtering valid orders), apply it before aggregation using a WHERE clause. This reduces the dataset before grouping, which keeps the query efficient. The database engine performs a single pass over the filtered rows, making the overall time complexity O(n) where n is the number of records scanned.
The same idea translates directly to Pandas. Convert the date column to a monthly period or formatted string, then use groupby on that value. Apply nunique() to both order_id and customer_id to compute unique counts per group. Pandas internally builds hash sets for uniqueness checks, giving O(n) time with O(k) space for grouped keys.
This problem mainly tests your understanding of database aggregation and efficient use of SQL grouping functions. Distinct counting within grouped partitions is a common analytics pattern used in reporting dashboards and business metrics pipelines. Practicing problems like this strengthens familiarity with data aggregation patterns that appear frequently in SQL interviews.
Recommended for interviews: The grouped aggregation approach is exactly what interviewers expect. A naive solution that manually iterates and stores sets per month demonstrates understanding, but writing a concise SQL query using GROUP BY and COUNT(DISTINCT ...) shows practical database fluency and production-ready thinking.
We can first filter out orders with an amount greater than 20, and then group by month to count the number of orders and customers.
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Manual Aggregation with Sets | O(n) | O(m + u) | Conceptual understanding outside SQL environments |
| SQL GROUP BY + COUNT(DISTINCT) | O(n) | O(1) extra | Best approach for relational databases and interview SQL problems |
| Pandas groupby + nunique | O(n) | O(k) | Data analysis workflows or Python data pipelines |
LeetCode 1565 Interview SQL Question with Detailed Explanation | Practice SQL • Everyday Data Science • 5,623 views views
Practice Unique Orders and Customers Per Month with our built-in code editor and test cases.
Practice on FleetCodePractice this problem
Open in Editor