You have a table Visits with columns UserID and VisitDate. Some UserID values are NULL. You want to count visits per user, treating NULL users as 'Guest'. Which query achieves this?
hard📝 Application Q9 of 15
SQL - GROUP BY and HAVING
You have a table Visits with columns UserID and VisitDate. Some UserID values are NULL. You want to count visits per user, treating NULL users as 'Guest'. Which query achieves this?
ASELECT UserID, COUNT(*) FROM Visits GROUP BY UserID NULLS LAST;
BSELECT UserID, COUNT(*) FROM Visits GROUP BY UserID HAVING UserID IS NOT NULL;
CSELECT UserID, COUNT(*) FROM Visits WHERE UserID IS NOT NULL GROUP BY UserID;
DSELECT COALESCE(UserID, 'Guest') AS UserGroup, COUNT(*) FROM Visits GROUP BY COALESCE(UserID, 'Guest');
Step-by-Step Solution
Solution:
Step 1: Use COALESCE to replace NULL UserID with 'Guest'
COALESCE(UserID, 'Guest') converts NULLs to 'Guest'.
Step 2: Group by the COALESCE expression and count visits
This groups all NULL UserIDs under 'Guest' and counts visits correctly.
Final Answer:
SELECT COALESCE(UserID, 'Guest') AS UserGroup, COUNT(*) FROM Visits GROUP BY COALESCE(UserID, 'Guest'); -> Option D
Quick Check:
COALESCE groups NULLs as 'Guest' for counting [OK]
Quick Trick:Replace NULL keys with COALESCE before grouping [OK]
Common Mistakes:
MISTAKES
Filtering out NULL UserIDs
Using HAVING or WHERE incorrectly
Expecting GROUP BY NULLS LAST to group NULLs
Master "GROUP BY and HAVING" in SQL
9 interactive learning modes - each teaches the same concept differently
