PostgreSQL - Subqueries in PostgreSQL
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, COUNT(*) AS order_count FROM orders GROUP BY user_id) AS sub WHERE sub.order_count > 3; uses a subquery to calculate average and count per user, then filters users with order_count > 3 outside. Others misuse COUNT in WHERE or HAVING incorrectly.Final Answer:
SELECT sub.user_id, sub.avg_amount FROM (SELECT user_id, AVG(amount) AS avg_amount, COUNT(*) AS order_count FROM orders GROUP BY user_id) AS sub WHERE sub.order_count > 3; -> Option AQuick Check:
Subquery calculates aggregates; outer filters by count [OK]
Quick Trick: Filter aggregates in outer query after subquery [OK]
Common Mistakes:
- Using COUNT(*) in WHERE clause directly
- Misplacing HAVING clause conditions
- Not aliasing subquery columns properly
Master "Subqueries in PostgreSQL" in PostgreSQL
Want More Practice?
15+ quiz questions · All difficulty levels · Free
Free Signup - Practice All QuestionsMore PostgreSQL Quizzes