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.