SQL - Subqueries
You want to find the average order amount per user but only for users who have placed more than 3 orders. Which query correctly uses a subquery in the FROM clause to achieve this?
You want to find the average order amount per user but only for users who have placed more than 3 orders. Which query correctly uses a subquery in the FROM clause to achieve this?
hard📝 Application Q15 of 15
Step-by-Step Solution
Solution:
Step 1: Understand the requirement
We need average order amount per user but only for users with more than 3 orders.Step 2: Analyze each option
SELECT sub.user_id, sub.avg_amount FROM (SELECT user_id, AVG(amount) AS avg_amount FROM orders GROUP BY user_id HAVING COUNT(*) > 3) AS sub; correctly uses a subquery to group orders by user_id, filters users with more than 3 orders using HAVING, then calculates average amount.Final Answer:
SELECT sub.user_id, sub.avg_amount FROM (SELECT user_id, AVG(amount) AS avg_amount FROM orders GROUP BY user_id HAVING COUNT(*) > 3) AS sub; -> Option BQuick Check:
Subquery filters users by order count, outer selects average [OK]
Quick Trick: Use HAVING in subquery to filter groups before outer select [OK]
Common Mistakes:
MISTAKES- Using WHERE with aggregation functions
- Placing HAVING outside GROUP BY context
- Not using subquery to filter groups first
Master "Subqueries" in SQL
Want More Practice?
15+ quiz questions · All difficulty levels · Free
Free Signup - Practice All QuestionsMore SQL Quizzes