Consider a table visits with columns user_id, visit_date, and duration. Write a query to find the average visit duration per user for visits in 2023, ordered by average duration ascending.
hard📝 Application Q9 of 15
SQL - GROUP BY and HAVING
Consider a table visits with columns user_id, visit_date, and duration. Write a query to find the average visit duration per user for visits in 2023, ordered by average duration ascending.
ASELECT user_id, AVG(duration) AS avg_duration FROM visits WHERE visit_date BETWEEN '2023-01-01' AND '2023-12-31' GROUP BY user_id ORDER BY avg_duration ASC;
BSELECT user_id, AVG(duration) AS avg_duration FROM visits GROUP BY user_id WHERE visit_date BETWEEN '2023-01-01' AND '2023-12-31' ORDER BY avg_duration ASC;
CSELECT user_id, AVG(duration) AS avg_duration FROM visits GROUP BY user_id ORDER BY avg_duration ASC WHERE visit_date BETWEEN '2023-01-01' AND '2023-12-31';
DSELECT user_id, AVG(duration) AS avg_duration FROM visits ORDER BY avg_duration ASC WHERE visit_date BETWEEN '2023-01-01' AND '2023-12-31' GROUP BY user_id;
Step-by-Step Solution
Solution:
Step 1: Filter rows before grouping
WHERE clause filters visits in 2023 before grouping by user_id.
Step 2: Group and order results
GROUP BY user_id groups visits; ORDER BY avg_duration ASC sorts ascending.
Final Answer:
SELECT user_id, AVG(duration) AS avg_duration FROM visits WHERE visit_date BETWEEN '2023-01-01' AND '2023-12-31' GROUP BY user_id ORDER BY avg_duration ASC; -> Option A
Quick Check:
WHERE filters rows, GROUP BY groups, ORDER BY sorts [OK]
Quick Trick:WHERE filters rows before GROUP BY; ORDER BY sorts grouped results [OK]
Common Mistakes:
MISTAKES
Placing WHERE after GROUP BY
Using ORDER BY before WHERE
Misordering clauses
Master "GROUP BY and HAVING" in SQL
9 interactive learning modes - each teaches the same concept differently
