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: Count Customers with Bills > $500Input: Storebill_idcustomer_idamount11800223003370041600WHEREamount > 500Filtered Resultscustomer_idamount180037001600COUNTDISTINCTOutputrich_count2Rich customers (amount > 500):• Customer 1: $800, $600 ✓• Customer 2: $300 ✗• Customer 3: $700 ✓Result: 2 unique customers
Understanding the Visualization
1
Input
Store table with bills and amounts
2
Filter
WHERE amount > 500
3
Count
COUNT(DISTINCT customer_id)
Key Takeaway
🎯 Key Insight: Use COUNT(DISTINCT) when counting unique entities that may appear multiple times in filtered results

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