You have a table visits with columns user_id and visit_date. Some user_id values are NULL. Write a query to count how many unique users visited on '2024-01-01', excluding NULL user_ids.
hard📝 Application Q8 of 15
SQL - Aggregate Functions
You have a table visits with columns user_id and visit_date. Some user_id values are NULL. Write a query to count how many unique users visited on '2024-01-01', excluding NULL user_ids.
ASELECT COUNT(user_id) FROM visits WHERE visit_date = '2024-01-01';
BSELECT COUNT(DISTINCT user_id) FROM visits WHERE visit_date = '2024-01-01' AND user_id IS NOT NULL;
CSELECT COUNT(*) FROM visits WHERE visit_date = '2024-01-01' AND user_id IS NOT NULL;
DSELECT COUNT(DISTINCT *) FROM visits WHERE visit_date = '2024-01-01';
Step-by-Step Solution
Solution:
Step 1: Filter visits on the specific date excluding NULL user_id
Use WHERE visit_date = '2024-01-01' AND user_id IS NOT NULL to exclude NULLs.
Step 2: Count distinct user_id values
Use COUNT(DISTINCT user_id) to count unique users.
Final Answer:
SELECT COUNT(DISTINCT user_id) FROM visits WHERE visit_date = '2024-01-01' AND user_id IS NOT NULL; -> Option B
Quick Check:
Filter NULLs before COUNT(DISTINCT) [OK]
Quick Trick:Filter NULLs before COUNT(DISTINCT) for accurate unique count [OK]
Common Mistakes:
MISTAKES
Not filtering NULL user_id before counting distinct
Using COUNT(user_id) instead of COUNT(DISTINCT user_id)
Counting all rows with COUNT(*)
Master "Aggregate Functions" in SQL
9 interactive learning modes - each teaches the same concept differently
