LATERAL subqueries in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select each department's name and the name of its top-paid employee using a LATERAL subquery.

PostgreSQL

SELECT d.name, e.name FROM departments d, LATERAL (SELECT name FROM employees WHERE department_id = d.id ORDER BY salary DESC LIMIT [1]) e;

Drag options to blanks, or click blank then click option'

AALL

B1

C0

D10

Attempts:

3 left

💡 Hint

Common Mistakes

Using LIMIT 0 returns no rows.

Using ALL is invalid syntax here.

Using LIMIT 10 returns too many employees.

The LATERAL subquery selects the top-paid employee by ordering salaries descending and limiting to 1.

2fill in blank

medium

Complete the code to join each order with its latest shipment date using a LATERAL subquery.

PostgreSQL

SELECT o.id, s.shipment_date FROM orders o, LATERAL (SELECT shipment_date FROM shipments WHERE order_id = o.id ORDER BY shipment_date DESC LIMIT [1]) s;

Drag options to blanks, or click blank then click option'

A1

B10

C0

D5

Attempts:

3 left

💡 Hint

Common Mistakes

Using LIMIT 0 returns no shipment dates.

Using LIMIT 5 or 10 returns multiple shipment dates, not just the latest.

The LATERAL subquery fetches the latest shipment date per order by ordering descending and limiting to 1.

3fill in blank

hard

Fix the error in the LATERAL subquery that tries to select employee names by limiting it to one row.

PostgreSQL

SELECT d.name, e.name FROM departments d, LATERAL (SELECT name FROM employees WHERE department_id = d.id ORDER BY salary DESC LIMIT [1]) e;

Drag options to blanks, or click blank then click option'

A1

B0

CALL

D10

Attempts:

3 left

💡 Hint

Common Mistakes

Omitting LIMIT causes multiple rows error.

Using LIMIT 0 returns no rows.

Using ALL is invalid syntax.

The subquery must have LIMIT 1 to return exactly one employee per department and the alias 'e' is correctly used.

4fill in blank

hard

Fill both blanks to select each customer and their most recent order date using a LATERAL subquery.

PostgreSQL

SELECT c.name, o.[1] FROM customers c, LATERAL (SELECT [2] FROM orders WHERE customer_id = c.id ORDER BY order_date DESC LIMIT 1) o;

Drag options to blanks, or click blank then click option'

Aorder_date

Border_id

Dcustomer_id

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting order_id outside but order_date inside causes mismatch.

Selecting customer_id inside the subquery is incorrect.

The outer query selects the order_date from the lateral subquery which also selects order_date ordered descending to get the most recent.

5fill in blank

hard

Fill all three blanks to select each product and its top 3 reviews' ratings using a LATERAL subquery.

PostgreSQL

SELECT p.name, r.rating FROM products p, LATERAL (SELECT rating FROM reviews WHERE product_id = p.id ORDER BY rating DESC LIMIT [1]) r WHERE r.rating > [2] AND r.rating <= [3];

Drag options to blanks, or click blank then click option'

A3

B4

C5

D2

Attempts:

3 left

💡 Hint

Common Mistakes

Using LIMIT 5 returns too many reviews.

Incorrect rating filters exclude desired reviews.

The subquery limits to top 3 ratings. The WHERE clause filters ratings greater than 2 and less or equal to 5.

Practice

1. What is the main purpose of using LATERAL in a PostgreSQL query?

easy

A. To allow a subquery to reference columns from tables listed before it in the FROM clause

B. To create a temporary table that lasts only for the duration of the query

C. To optimize the query by parallelizing subqueries automatically

D. To enforce foreign key constraints between tables

Solution

1. Step 1: Understand the role of LATERAL

   LATERAL allows a subquery to access columns from tables that appear earlier in the FROM clause.

2. Step 2: Compare with other options

   The other options describe different features unrelated to LATERAL's purpose.

3. Final Answer:

   To allow a subquery to reference columns from tables listed before it in the FROM clause -> Option A

4. Quick Check:

   LATERAL = subquery uses previous table columns [OK]

Hint: LATERAL lets subqueries see earlier tables in FROM [OK]

Common Mistakes:

• Thinking LATERAL creates temporary tables
• Confusing LATERAL with query parallelism
• Assuming LATERAL enforces constraints

2. Which of the following is the correct syntax to use a LATERAL subquery in PostgreSQL?

easy

A. SELECT * FROM table1, (SELECT * FROM table2 WHERE table2.id = table1.id) sub;

B. SELECT * FROM table1 JOIN LATERAL table2 ON table2.id = table1.id;

C. SELECT * FROM table1 CROSS JOIN LATERAL (SELECT * FROM table2 WHERE table2.id = table1.id) sub;

D. SELECT * FROM table1 LATERAL JOIN table2 ON table2.id = table1.id;

Solution

1. Step 1: Identify correct LATERAL syntax

   The correct syntax uses CROSS JOIN LATERAL with a subquery in parentheses and an alias.

2. Step 2: Check each option

   SELECT * FROM table1, (SELECT * FROM table2 WHERE table2.id = table1.id) sub; misses LATERAL keyword causing reference error; SELECT * FROM table1 JOIN LATERAL table2 ON table2.id = table1.id; uses JOIN LATERAL incorrectly without parentheses; SELECT * FROM table1 LATERAL JOIN table2 ON table2.id = table1.id; has invalid syntax.

3. Final Answer:

   SELECT * FROM table1 CROSS JOIN LATERAL (SELECT * FROM table2 WHERE table2.id = table1.id) sub; -> Option C

4. Quick Check:

   Correct LATERAL syntax = SELECT * FROM table1 CROSS JOIN LATERAL (SELECT * FROM table2 WHERE table2.id = table1.id) sub; [OK]

Hint: Use CROSS JOIN LATERAL with subquery in parentheses [OK]

Common Mistakes:

• Omitting LATERAL keyword
• Not aliasing the subquery
• Using LATERAL without parentheses

3. Given tables users(id, name) and orders(user_id, amount), what does this query return?

SELECT u.name, o.total FROM users u CROSS JOIN LATERAL (SELECT SUM(amount) AS total FROM orders WHERE user_id = u.id) o;

medium

A. Each user with the total amount of their orders

B. All users joined with all orders without filtering

C. Only users who have orders, with their order totals

D. Syntax error due to missing GROUP BY

Solution

1. Step 1: Analyze the LATERAL subquery

   The subquery sums orders per user using user_id = u.id, so it calculates total per user.

2. Step 2: Understand CROSS JOIN LATERAL effect

   It runs the subquery for each user, returning their name and total order amount, including users with zero orders (total will be NULL).

3. Final Answer:

   Each user with the total amount of their orders -> Option A

4. Quick Check:

   LATERAL sums orders per user = Each user with the total amount of their orders [OK]

Hint: LATERAL runs subquery per row, here summing orders per user [OK]

Common Mistakes:

• Thinking it returns all orders without grouping
• Assuming only users with orders appear
• Confusing CROSS JOIN LATERAL with INNER JOIN

4. Identify the error in this query using LATERAL:

SELECT t1.id, t2.val FROM table1 t1 JOIN LATERAL (SELECT val FROM table2 WHERE table2.ref = t1.id) t2;

medium

A. Missing alias for the LATERAL subquery

B. JOIN LATERAL requires CROSS keyword

C. Subquery must return only one column

D. No error; query is correct

Solution

1. Step 1: Check alias usage

   The subquery is aliased as t2, so no issue there.

2. Step 2: Verify JOIN LATERAL syntax

   JOIN LATERAL without CROSS is valid syntax in PostgreSQL; CROSS JOIN LATERAL is one form, but JOIN LATERAL alone is allowed as INNER JOIN LATERAL.

3. Step 3: Confirm subquery column count

   Subquery returns one column 'val', which is acceptable.

4. Final Answer:

   No error; query is correct -> Option D

5. Quick Check:

   JOIN LATERAL syntax without CROSS is valid = No error [OK]

Hint: JOIN LATERAL without CROSS is valid syntax [OK]

Common Mistakes:

• Using JOIN LATERAL without CROSS
• Forgetting subquery alias
• Expecting subquery to return multiple columns

5. You want to list each customer with their top 2 most expensive orders using LATERAL. Which query correctly achieves this?

SELECT c.name, o.order_id, o.amount FROM customers c CROSS JOIN LATERAL (SELECT order_id, amount FROM orders WHERE customer_id = c.id ORDER BY amount DESC LIMIT 2) o;

hard

A. The query misses GROUP BY on customers

B. This query correctly lists each customer with their top 2 orders

C. LATERAL cannot be used with LIMIT inside subqueries

D. The query will return only customers with at least 2 orders

Solution

1. Step 1: Understand LATERAL with LIMIT

   The subquery selects top 2 orders per customer by ordering and limiting results.

2. Step 2: Check if query returns all customers

   CROSS JOIN LATERAL runs subquery per customer; if no orders, no rows for that customer appear.

3. Step 3: Confirm correctness

   The query correctly lists each customer with up to 2 highest orders; no syntax or logic errors.

4. Final Answer:

   This query correctly lists each customer with their top 2 orders -> Option B

5. Quick Check:

   LATERAL with LIMIT works for top N per row [OK]

Hint: Use LATERAL with ORDER BY and LIMIT for top N per row [OK]

Common Mistakes:

• Thinking GROUP BY is needed here
• Believing LATERAL disallows LIMIT
• Assuming all customers appear even without orders