PostgreSQL - Subqueries in PostgreSQL
You want to list each user with their two highest order amounts. Which query correctly uses LATERAL to achieve this?
You want to list each user with their two highest order amounts. Which query correctly uses LATERAL to achieve this?
hard📝 Application Q8 of 15
Step-by-Step Solution
Solution:
Step 1: Understand requirement for two highest orders per user
We want all users listed, even those without orders, with up to two highest amounts.Step 2: Analyze join types
LEFT JOIN LATERAL ensures users without orders appear with NULLs. CROSS JOIN or JOIN without ON excludes users without orders.Step 3: Check syntax and correctness
SELECT u.name, o.amount FROM users u LEFT JOIN LATERAL (SELECT amount FROM orders WHERE user_id = u.id ORDER BY amount DESC LIMIT 2) o ON true; uses LEFT JOIN LATERAL with ON true, correctly returning all users and their top two orders.Final Answer:
SELECT u.name, o.amount FROM users u LEFT JOIN LATERAL (SELECT amount FROM orders WHERE user_id = u.id ORDER BY amount DESC LIMIT 2) o ON true; -> Option AQuick Check:
LEFT JOIN LATERAL with LIMIT returns top N per user including users without orders [OK]
Quick Trick: Use LEFT JOIN LATERAL with LIMIT for top N per group including all rows [OK]
Common Mistakes:
- Using CROSS JOIN excludes users without orders
- Omitting ON clause with JOIN LATERAL
- Using comma join without LEFT JOIN for optional rows
Master "Subqueries in PostgreSQL" in PostgreSQL
Want More Practice?
15+ quiz questions · All difficulty levels · Free
Free Signup - Practice All QuestionsMore PostgreSQL Quizzes