How can you combine LATERAL subqueries with aggregation to find each user's average order amount and their highest single order?

hard📝 Application Q9 of 15

PostgreSQL - Subqueries in PostgreSQL

ASELECT u.id, avg(o.amount), max(o.amount) FROM users u JOIN LATERAL (SELECT amount FROM orders WHERE user_id = u.id) o ON true GROUP BY u.id;

BSELECT u.id, AVG(amount), MAX(amount) FROM users u, LATERAL orders o WHERE o.user_id = u.id GROUP BY u.id;

CSELECT u.id, o.avg_amount, o.max_amount FROM users u LEFT JOIN LATERAL (SELECT AVG(amount) AS avg_amount, MAX(amount) AS max_amount FROM orders WHERE user_id = u.id) o ON true;

DSELECT u.id, AVG(o.amount), MAX(o.amount) FROM users u CROSS JOIN LATERAL orders o WHERE o.user_id = u.id GROUP BY u.id;

Step-by-Step Solution

Solution:

Step 1: Understand aggregation per user
We want average and max order amounts per user, including users with no orders.
Step 2: Analyze options
SELECT u.id, o.avg_amount, o.max_amount FROM users u LEFT JOIN LATERAL (SELECT AVG(amount) AS avg_amount, MAX(amount) AS max_amount FROM orders WHERE user_id = u.id) o ON true; uses LEFT JOIN LATERAL with a subquery that aggregates orders per user, returning NULLs for users without orders. Others misuse joins or aggregation syntax.
Final Answer:
SELECT u.id, o.avg_amount, o.max_amount FROM users u LEFT JOIN LATERAL (SELECT AVG(amount) AS avg_amount, MAX(amount) AS max_amount FROM orders WHERE user_id = u.id) o ON true; -> Option C
Quick Check:
LEFT JOIN LATERAL with aggregation subquery per user returns correct stats [OK]

Quick Trick: Use LEFT JOIN LATERAL with aggregation subquery for per-row stats [OK]

Common Mistakes:

Using JOIN instead of LEFT JOIN excludes users without orders
Trying to aggregate without GROUP BY
Misusing LATERAL with direct table instead of subquery

Master "Subqueries in PostgreSQL" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

Want More Practice?

15+ quiz questions · All difficulty levels · Free

Free Signup - Practice All Questions

How can you combine LATERAL subqueries with aggregation to find each user's average order amount and their highest single order?

Step 1: Understand aggregation per user

Step 2: Analyze options

Final Answer:

Quick Check:

Want More Practice?