LATERAL join for correlated subqueries in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select each department and its employee count using LATERAL join.

PostgreSQL

SELECT d.department_name, emp_count.count FROM departments d LEFT JOIN LATERAL (SELECT COUNT(*) AS count FROM employees e WHERE e.department_id = d.id) AS emp_count ON [1];

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

Ad.id = e.department_id

Bemp_count.count > 0

Ctrue

Dd.department_name = e.name

Attempts:

3 left

💡 Hint

Common Mistakes

Using a join condition that references tables not in scope in the ON clause.

Leaving the ON clause empty or invalid.

The LATERAL join requires a condition after ON. Using true means the join always applies, allowing the subquery to correlate with the outer query.

2fill in blank

medium

Complete the code to select each employee and their latest project using LATERAL join.

PostgreSQL

SELECT e.name, p.project_name FROM employees e LEFT JOIN LATERAL (SELECT project_name FROM projects p WHERE p.employee_id = e.id ORDER BY p.start_date DESC LIMIT 1) AS p ON [1];

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

Ap.employee_id = e.id

Btrue

Ce.id = p.id

Dp.project_name IS NOT NULL

Attempts:

3 left

💡 Hint

Common Mistakes

Trying to join on columns that do not exist in the subquery alias.

Using a join condition that filters out rows unintentionally.

The ON clause for LATERAL join can be true to always join the subquery result for each employee.

3fill in blank

hard

Fix the error in the LATERAL join by completing the ON clause correctly.

PostgreSQL

SELECT d.name, emp.name FROM departments d JOIN LATERAL (SELECT name FROM employees emp WHERE emp.department_id = d.id LIMIT 1) emp ON [1];

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

Aemp.id = d.id

Bemp.department_id = d.id

Cemp.name = d.name

Dtrue

Attempts:

3 left

💡 Hint

Common Mistakes

Using join conditions that reference columns not available in the lateral subquery alias.

Leaving the ON clause empty.

The ON clause in a LATERAL join can be true to allow the lateral subquery to run correlated with the outer query.

4fill in blank

hard

Fill both blanks to create a LATERAL join that selects each customer and their most recent order date.

PostgreSQL

SELECT c.customer_name, recent_orders.order_date FROM customers c LEFT JOIN LATERAL (SELECT order_date FROM orders o WHERE o.customer_id = c.id ORDER BY order_date DESC LIMIT 1) AS recent_orders ON [1] [2];

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

Atrue

Bo.customer_id = c.id

CAND

DOR

Attempts:

3 left

💡 Hint

Common Mistakes

Using invalid logical operators like OR without proper conditions.

Trying to join on columns not available in the lateral subquery alias.

The ON clause can be true for LATERAL joins. If combining conditions, use AND or OR. Here, true AND is syntactically valid but usually only true is needed.

5fill in blank

hard

Fill all three blanks to write a query that selects each product and its top 3 reviews using LATERAL join.

PostgreSQL

SELECT p.product_name, r.review_text FROM products p JOIN LATERAL (SELECT review_text FROM reviews r WHERE r.product_id = p.id ORDER BY r.rating DESC LIMIT [1]) r ON [2] [3];

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

A3

Btrue

CAND

Dr.product_id = p.id

Attempts:

3 left

💡 Hint

Common Mistakes

Using LIMIT values other than 3.

Using join conditions that cause errors or filter out rows incorrectly.

The LIMIT is 3 to get top 3 reviews. The ON clause can be true. If combining conditions, use AND. Usually, just true is enough for LATERAL joins.