The Number of Rich Customers - Problem

You are given a Store table containing bill information for customers. Each row represents one bill with its associated customer and amount.

Table: Store

Column NameType
bill_idint
customer_idint
amountint

bill_id is the primary key for this table. Each row contains the amount of one bill and the customer who made the purchase.

Write a SQL query to report the number of customers who had at least one bill with an amount strictly greater than 500.

Table Schema

Store
Column Name Type Description
bill_id PK int Primary key, unique identifier for each bill
customer_id int ID of the customer who made the purchase
amount int Amount of the bill in dollars
Primary Key: bill_id
Note: Each row represents one bill transaction. A customer can have multiple bills.

Input & Output

Example 1 — Mixed Bill Amounts
Input Table:
bill_id customer_id amount
1 1 800
2 2 300
3 3 700
4 1 600
Output:
rich_count
2
💡 Note:

Customer 1 has bills of $800 and $600 (both > 500). Customer 2 has a bill of $300 (≤ 500). Customer 3 has a bill of $700 (> 500). Only customers 1 and 3 qualify as 'rich customers', so the answer is 2.
Example 2 — No Rich Customers
Input Table:
bill_id customer_id amount
1 1 200
2 2 450
3 3 500
Output:
rich_count
0
💡 Note:

All bills are ≤ $500. Since we need amounts strictly greater than 500, no customers qualify as rich customers. The result is 0.
Example 3 — All Rich Customers
Input Table:
bill_id customer_id amount
1 1 1000
2 2 750
3 3 600
Output:
rich_count
3
💡 Note:

All customers have at least one bill > $500: Customer 1 ($1000), Customer 2 ($750), and Customer 3 ($600). The result is 3.

Constraints

  • 1 ≤ bill_id ≤ 1000
  • 1 ≤ customer_id ≤ 1000
  • 0 ≤ amount ≤ 10000

Visualization

Tap to expand
The Number of Rich Customers INPUT: Store Table bill_id customer_id amount 1 101 200 2 102 600 3 101 550 4 103 400 5 104 800 6 102 300 amount > 500 amount <= 500 Condition: amount > 500 ALGORITHM STEPS 1 Filter Records WHERE amount > 500 2 Get Unique Customers SELECT DISTINCT customer_id 3 Count Results COUNT(*) as rich_count 4 Return Result Output the count SELECT COUNT(DISTINCT customer_id) AS rich_count FROM Store WHERE amount > 500; FINAL RESULT Rich Customers (amount > 500): Customer 102 600 Customer 101 550 Customer 104 800 3 Unique Customers rich_count 3 OK - Result Complete Key Insight: Use COUNT(DISTINCT customer_id) with WHERE amount > 500 to count unique customers with high-value bills. DISTINCT ensures each customer is counted only once, even if they have multiple bills exceeding 500. Time Complexity: O(n) | Space Complexity: O(k) where k is unique rich customers TutorialsPoint - The Number of Rich Customers | Optimal Solution

Related Problems

Asked in
Amazon 12 Microsoft 8
25.0K Views
Medium Frequency
~8 min Avg. Time
890 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