The Number of Rich Customers

The Number of Rich Customers

What is this problem about?

Financial data analysis often involves segmenting customers based on their spending or balance. "The Number of Rich Customers" is a database problem where you are given a table of store accounts, with each record containing a customer ID and the amount in that account. Your task is to count how many unique customers have at least one account with a balance strictly greater than a given threshold 'rich_threshold'.

Why is this asked in interviews?

This the Number of Rich Customers interview question is a basic SQL assessment asked by companies like athenahealth. It tests your ability to use the COUNT(DISTINCT ...) function and the WHERE clause. While simple, it evaluates whether you understand the difference between counting total records and counting unique entities, which is a fundamental concept in data reporting.

Algorithmic pattern used

This problem follows the Database interview pattern.

1. Use the SELECT COUNT(DISTINCT customer_id) statement to target unique customers.
2. Use the WHERE amount > rich_threshold clause to filter for "rich" accounts.
3. The result is a single number representing the count of unique customers meeting the criteria.

Example explanation

Threshold: 500. Table:

• Customer 1: Amount 600
• Customer 1: Amount 100
• Customer 2: Amount 300
• Customer 3: Amount 700
• Customer 3: Amount 800

1. Filter for > 500:
   • (Customer 1, 600)
   • (Customer 3, 700)
   • (Customer 3, 800)
2. Unique Customer IDs from filtered list: {1, 3}.
3. Count is 2. Bob (Customer 2) is not counted, and Alice (Customer 1) is only counted once despite having other accounts.

Common mistakes candidates make

The most common mistake in "The Number of Rich Customers coding problem" is forgetting the DISTINCT keyword. Without it, a customer with multiple rich accounts would be counted multiple times, leading to an incorrect result. Another mistake is using amount >= rich_threshold if the problem specifies "strictly greater than."

Interview preparation tip

Always pay close attention to whether a problem asks for a count of total records or a count of unique individuals. This distinction is vital in SQL. Also, be sure you are comfortable with comparison operators and basic aggregation.