Table: Store
+-------------+------+ | Column Name | Type | +-------------+------+ | bill_id | int | | customer_id | int | | amount | int | +-------------+------+ bill_id is the primary key (column with unique values) for this table. Each row contains information about the amount of one bill and the customer associated with it.
Write a solution to report the number of customers who had at least one bill with an amount strictly greater than 500.
The result format is in the following example.
Example 1:
Input: Store table: +---------+-------------+--------+ | bill_id | customer_id | amount | +---------+-------------+--------+ | 6 | 1 | 549 | | 8 | 1 | 834 | | 4 | 2 | 394 | | 11 | 3 | 657 | | 13 | 3 | 257 | +---------+-------------+--------+ Output: +------------+ | rich_count | +------------+ | 2 | +------------+ Explanation: Customer 1 has two bills with amounts strictly greater than 500. Customer 2 does not have any bills with an amount strictly greater than 500. Customer 3 has one bill with an amount strictly greater than 500.
Problem Overview: The table Store records purchases with columns customer_id and amount. A customer is considered rich if they made a purchase strictly greater than 500. The task is to return how many unique customers satisfy this condition.
Approach 1: Filter + DISTINCT Count (O(n) time, O(1) extra space)
The direct solution filters rows where amount > 500 and counts unique customers. Use COUNT(DISTINCT customer_id) so each qualifying customer is counted once even if they have multiple purchases above 500. The database scans the table, applies the filter, and performs a distinct aggregation. This approach is concise and matches how SQL engines are optimized to handle filtering and aggregation operations. Works well for problems involving uniqueness in database queries.
Approach 2: GROUP BY + HAVING (O(n) time, O(k) space)
Another option groups rows by customer_id, then filters groups using a HAVING clause. The query evaluates whether any purchase in each group exceeds 500 using MAX(amount) > 500. After grouping, count the remaining rows. This approach explicitly models the logic per customer and is useful when the definition of "rich" depends on aggregated metrics like maximum, sum, or average. It demonstrates deeper understanding of SQL grouping operations in SQL.
Approach 3: Subquery with DISTINCT (O(n) time, O(k) space)
A subquery first selects distinct customer_id values where amount > 500. The outer query simply counts the rows returned. Internally this behaves similarly to the DISTINCT aggregation approach but separates filtering and counting into two logical steps. This structure is sometimes preferred for readability when building layered queries or composing logic with other filters and joins in larger aggregation pipelines.
Recommended for interviews: The COUNT(DISTINCT customer_id) solution is the expected answer. It shows you understand filtering and uniqueness directly in SQL. The GROUP BY version is still valid and useful when interviewers want to see familiarity with aggregation patterns.
MySQL
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Filter + COUNT(DISTINCT) | O(n) | O(1) | Best general solution. Simple query and optimized by SQL engines. |
| GROUP BY + HAVING | O(n) | O(k) | When defining conditions per customer using aggregated values. |
| Subquery with DISTINCT | O(n) | O(k) | Useful when composing queries or separating filtering and counting logic. |
LeetCode 2082 Interview SQL Question with Detailed Explanation | Practice SQL • Everyday Data Science • 4,847 views views
Practice The Number of Rich Customers with our built-in code editor and test cases.
Practice on FleetCode