LATERAL subqueries in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of a LATERAL join with filtering

Given the tables authors and books, what is the output of the following query?

SELECT a.name, b.title FROM authors a CROSS JOIN LATERAL (SELECT title FROM books WHERE author_id = a.id ORDER BY published_year DESC LIMIT 1) b ORDER BY a.id;

Assume authors has 3 rows with ids 1, 2, 3 and books has multiple books per author.

PostgreSQL

SELECT a.name, b.title FROM authors a CROSS JOIN LATERAL (SELECT title FROM books WHERE author_id = a.id ORDER BY published_year DESC LIMIT 1) b ORDER BY a.id;

A [{"name": "Author1", "title": "Latest Book A1"}, {"name": "Author2", "title": "Latest Book A2"}, {"name": "Author3", "title": "Latest Book A3"}]

B [{"name": "Author1", "title": "Oldest Book A1"}, {"name": "Author2", "title": "Oldest Book A2"}, {"name": "Author3", "title": "Oldest Book A3"}]

C [{"name": "Author1", "title": null}, {"name": "Author2", "title": null}, {"name": "Author3", "title": null}]

D [{"name": "Author1", "title": "Latest Book A3"}, {"name": "Author2", "title": "Latest Book A2"}, {"name": "Author3", "title": "Latest Book A1"}]

💡 Hint

LATERAL allows the subquery to use columns from the current row of the main query.

The LATERAL subquery selects the latest book per author by ordering books by published_year descending and limiting to 1. The CROSS JOIN LATERAL applies this for each author row, producing the latest book title per author.

📝 Syntax

intermediate

Identify the syntax error in LATERAL usage

Which option contains a syntax error when using LATERAL in PostgreSQL?

PostgreSQL

SELECT a.name, b.title FROM authors a JOIN LATERAL (SELECT title FROM books WHERE author_id = a.id) b ON true;

A SELECT a.name, b.title FROM authors a JOIN LATERAL (SELECT title FROM books WHERE author_id = a.id) b ON true;

B SELECT a.name, b.title FROM authors a CROSS JOIN LATERAL (SELECT title FROM books WHERE author_id = a.id) b;

C SELECT a.name, b.title FROM authors a JOIN LATERAL SELECT title FROM books WHERE author_id = a.id b ON true;

D SELECT a.name, b.title FROM authors a LEFT JOIN LATERAL (SELECT title FROM books WHERE author_id = a.id) b ON true;

💡 Hint

Check the parentheses around the subquery after LATERAL.

Option C is missing parentheses around the subquery after LATERAL, causing a syntax error. LATERAL must be followed by a subquery enclosed in parentheses.

❓ optimization

advanced

Optimizing a query with LATERAL for performance

You have a query that uses LATERAL to find the top 3 recent orders per customer:

SELECT c.customer_id, o.order_id FROM customers c JOIN LATERAL (SELECT order_id FROM orders WHERE customer_id = c.customer_id ORDER BY order_date DESC LIMIT 3) o ON true;

Which change will most likely improve performance on large datasets?

A Add an index on orders(customer_id, order_date DESC)

B Rewrite the query using a correlated subquery in SELECT clause

C Remove the LIMIT clause to allow full scan

D Use CROSS JOIN instead of JOIN LATERAL

💡 Hint

Indexes help speed up filtering and ordering.

Adding a composite index on orders(customer_id, order_date DESC) helps the database quickly find the top 3 recent orders per customer, improving query performance.

🔧 Debug

advanced

Debugging unexpected NULL results in LATERAL join

A query uses LEFT JOIN LATERAL to get the latest comment per post:

SELECT p.post_id, c.comment_text FROM posts p LEFT JOIN LATERAL (SELECT comment_text FROM comments WHERE post_id = p.post_id ORDER BY created_at DESC LIMIT 1) c ON true;

Some posts have NULL for comment_text even though comments exist. What is the most likely cause?

A The subquery does not filter correctly because post_id is ambiguous

B The ON true condition causes the join to fail for some rows

C The LEFT JOIN LATERAL returns NULL when no matching comments exist

D The comments table has no matching post_id values due to data mismatch

💡 Hint

Check data consistency between posts and comments tables.

If some posts have NULL comment_text despite comments existing, it likely means the comments have post_id values that do not match the posts table due to data inconsistency or mismatch.

🧠 Conceptual

expert

Understanding LATERAL subquery execution order

Consider the query:

SELECT t1.id, t2.val FROM t1 JOIN LATERAL (SELECT val FROM t2 WHERE t2.ref_id = t1.id ORDER BY val DESC LIMIT 1) t2 ON true;

Which statement best describes how the LATERAL subquery executes?

A The subquery runs after the entire join is completed

B The subquery runs once per t1 row, using that row's id to filter t2

C The subquery runs once and uses all t1 rows to filter t2

D The subquery runs independently of t1 and returns all t2 rows

💡 Hint

LATERAL allows the subquery to reference columns from the outer query row.

LATERAL subqueries execute once per row of the outer query (t1), using that row's columns (t1.id) to filter the inner query (t2). This allows correlated filtering per row.