How would you modify this query to count only unique customers who made purchases over $500?

hard📝 Application Q9 of 15

SQL - Advanced Query Patterns

SELECT store, COUNT(CASE WHEN purchase_amount > 500 THEN customer_id END) AS high_value_customers FROM sales GROUP BY store;

AUse COUNT(CASE WHEN purchase_amount > 500 THEN 1 END) AS high_value_customers

BUse SUM(CASE WHEN purchase_amount > 500 THEN 1 ELSE 0 END) AS high_value_customers

CUse COUNT(DISTINCT customer_id) AS high_value_customers

DUse COUNT(DISTINCT CASE WHEN purchase_amount > 500 THEN customer_id END) AS high_value_customers

Step-by-Step Solution

Solution:

Step 1: Understand requirement for unique customers
Counting unique customers requires COUNT DISTINCT on customer_id.
Step 2: Apply conditional aggregation with DISTINCT
Use COUNT(DISTINCT CASE WHEN condition THEN customer_id END) to count unique customers meeting condition.
Final Answer:
Use COUNT(DISTINCT CASE WHEN purchase_amount > 500 THEN customer_id END) AS high_value_customers -> Option D
Quick Check:
COUNT DISTINCT with CASE filters unique conditional values [OK]

Quick Trick: Combine COUNT DISTINCT with CASE for unique conditional counts [OK]

Common Mistakes:

Master "Advanced Query Patterns" in SQL

9 interactive learning modes - each teaches the same concept differently

Want More Practice?

15+ quiz questions · All difficulty levels · Free

More SQL Quizzes