Given a table visits with columns user_id, page, and visit_date, write a query to count visits per user per day, but only for users with more than 5 visits that day.
hard📝 Application Q9 of 15
PostgreSQL - Aggregate Functions and GROUP BY
Given a table visits with columns user_id, page, and visit_date, write a query to count visits per user per day, but only for users with more than 5 visits that day.
ASELECT user_id, visit_date, COUNT(*) FROM visits WHERE COUNT(*) > 5 GROUP BY user_id, visit_date;
BSELECT user_id, visit_date, COUNT(*) FROM visits GROUP BY user_id HAVING COUNT(*) > 5;
CSELECT user_id, visit_date, COUNT(*) FROM visits GROUP BY user_id, visit_date HAVING COUNT(*) > 5;
DSELECT user_id, visit_date, COUNT(*) FROM visits GROUP BY visit_date, user_id HAVING COUNT(*) >= 5;
Step-by-Step Solution
Solution:
Step 1: Group by user_id and visit_date
To count visits per user per day, group by both columns.
Step 2: Filter groups with HAVING
Use HAVING COUNT(*) > 5 to keep only groups with more than 5 visits.
Final Answer:
SELECT user_id, visit_date, COUNT(*) FROM visits GROUP BY user_id, visit_date HAVING COUNT(*) > 5; -> Option C
Quick Check:
Use HAVING to filter grouped results [OK]
Quick Trick:Use HAVING to filter groups after GROUP BY [OK]
Common Mistakes:
Using WHERE with aggregate functions
Grouping by only one column
Using >= instead of > if question specifies more than 5
Master "Aggregate Functions and GROUP BY" in PostgreSQL
9 interactive learning modes - each teaches the same concept differently
