The Number of Rich Customers - Problem

You're working as a data analyst for a luxury retail store and need to identify your VIP customers who make high-value purchases. Your task is to analyze the store's billing database and count how many unique customers have made at least one purchase exceeding $500.

Given a Store table with the following structure:

Column NameType
bill_idint
customer_idint
amountint

Where:

  • bill_id is the primary key (unique identifier for each bill)
  • customer_id identifies which customer made the purchase
  • amount represents the bill amount in dollars

Goal: Write a SQL query to count the number of distinct customers who have at least one bill with an amount strictly greater than 500.

Note: A customer might have multiple bills, but we only count them once if they qualify as a "rich customer".

Input & Output

example_1.sql — Basic Case
$ Input: Store table: | bill_id | customer_id | amount | |---------|-------------|--------| | 1 | 1 | 200 | | 2 | 2 | 800 | | 3 | 3 | 450 | | 4 | 4 | 600 | | 5 | 2 | 300 | | 6 | 1 | 750 |
› Output: | rich_count | |------------| | 3 |
💡 Note: Customers 1, 2, and 4 have at least one bill > $500. Customer 1 has bill of $750, customer 2 has bill of $800, and customer 4 has bill of $600. Customer 3 only has $450 bill, so they don't qualify.
example_2.sql — All Poor Customers
$ Input: Store table: | bill_id | customer_id | amount | |---------|-------------|--------| | 1 | 1 | 200 | | 2 | 2 | 300 | | 3 | 3 | 450 | | 4 | 1 | 500 |
› Output: | rich_count | |------------| | 0 |
💡 Note: No customer has any bill with amount strictly greater than 500. Customer 1's highest bill is exactly $500, which doesn't qualify (must be > 500, not >= 500).
example_3.sql — Single Rich Customer
$ Input: Store table: | bill_id | customer_id | amount | |---------|-------------|--------| | 1 | 1 | 501 | | 2 | 1 | 200 | | 3 | 1 | 100 |
› Output: | rich_count | |------------| | 1 |
💡 Note: Only customer 1 exists and they have one bill of $501 which exceeds $500, so they qualify as a rich customer. Multiple bills from the same customer still count as one rich customer.

Constraints

  • 1 ≤ bill_id ≤ 105
  • 1 ≤ customer_id ≤ 104
  • 1 ≤ amount ≤ 106
  • bill_id is unique (primary key)
  • All values are positive integers

Visualization

Tap to expand
🏪 VIP Customer Detection Process📄 Store Bills$200$800$450$600$300$750🔍 Filter > $500$800$600$750Customer 2Customer 4Customer 1👥 Unique Customers124VIP Customers📊 Count3💡 Key Insight: COUNT(DISTINCT) MagicSQL's COUNT(DISTINCT customer_id) automatically:✓ Filters records with WHERE amount > 500✓ Uses hash table to track unique customers✓ Counts distinct values in O(n) time✓ Returns final count efficientlyPerfect for finding unique high-value customers! 🎯
Understanding the Visualization
1
Scan All Bills
Go through each bill in the Store table
2
Filter High Amounts
Keep only bills with amount > $500
3
Track Unique Customers
Use hash table to remember distinct customer IDs
4
Count VIP Customers
Return the count of unique customers who qualify
Key Takeaway
🎯 Key Insight: SQL's COUNT(DISTINCT) with WHERE clause is the perfect tool for counting unique entities that meet a condition - exactly what we need for identifying VIP customers!

Related Problems

Asked in
Amazon 45 Microsoft 35 Google 28 Meta 22
28.4K Views
High Frequency
~8 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