Given a table visits with columns user_id, page, date, and duration, write a query to find the average duration per user per page per date. Which query is correct?
hard📝 Application Q9 of 15
SQL - GROUP BY and HAVING
Given a table visits with columns user_id, page, date, and duration, write a query to find the average duration per user per page per date. Which query is correct?
ASELECT user_id, page, date, AVG(duration) FROM visits GROUP BY user_id, page, date;
BSELECT user_id, page, date, AVG(duration) FROM visits GROUP BY user_id, page;
CSELECT user_id, page, date, AVG(duration) FROM visits GROUP BY date;
DSELECT user_id, page, date, AVG(duration) FROM visits;
Step-by-Step Solution
Solution:
Step 1: Determine grouping columns
We want average duration per user, page, and date, so group by all three columns.
Step 2: Evaluate query options
SELECT user_id, page, date, AVG(duration) FROM visits GROUP BY user_id, page, date; groups by user_id, page, and date, matching SELECT columns. Options B and C group by fewer columns, causing incorrect results. SELECT user_id, page, date, AVG(duration) FROM visits; lacks GROUP BY, so no grouping.
Final Answer:
SELECT user_id, page, date, AVG(duration) FROM visits GROUP BY user_id, page, date; -> Option A
Quick Check:
Group by all non-aggregated columns for correct averages [OK]
Quick Trick:Group by all columns except aggregates for correct results [OK]
Common Mistakes:
MISTAKES
Grouping by fewer columns than selected
Missing GROUP BY clause
Confusing AVG with SUM
Master "GROUP BY and HAVING" in SQL
9 interactive learning modes - each teaches the same concept differently
