LATERAL join for correlated subqueries in PostgreSQL - Mini Project: Build & Apply

Using LATERAL Join for Correlated Subqueries in PostgreSQL

📖 Scenario: You are managing a bookstore database. You want to find the most recent purchase date for each customer along with their name and ID.

🎯 Goal: Build a PostgreSQL query using a LATERAL join to get each customer's ID, name, and their most recent purchase date from the customers and purchases tables.

📋 What You'll Learn

Create a customers table with columns customer_id (integer) and customer_name (text).

Create a purchases table with columns purchase_id (integer), customer_id (integer), and purchase_date (date).

Write a query that uses a LATERAL join to find the latest purchase date for each customer.

The query should return customer_id, customer_name, and latest_purchase (the most recent purchase date).

💡 Why This Matters

🌍 Real World

Retail and e-commerce databases often need to find the latest transaction or activity per user. Using LATERAL joins helps efficiently get related data per row.

💼 Career

Database developers and analysts use LATERAL joins to write advanced queries that involve correlated subqueries, improving query performance and clarity.

1

Create the customers and purchases tables

Write SQL statements to create a table called customers with columns customer_id (integer) and customer_name (text), and a table called purchases with columns purchase_id (integer), customer_id (integer), and purchase_date (date).

PostgreSQL

-- Create customers and purchases tables
-- Your code here

Need a hint?

Use CREATE TABLE statements with the specified columns and types.

2

Insert sample data into customers and purchases

Insert these exact rows into customers: (1, 'Alice'), (2, 'Bob'), (3, 'Charlie'). Insert these exact rows into purchases: (101, 1, '2024-01-10'), (102, 1, '2024-02-15'), (103, 2, '2024-01-20'), (104, 3, '2024-03-05').

PostgreSQL

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    customer_name TEXT
);

CREATE TABLE purchases (
    purchase_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    purchase_date DATE
);

-- Insert sample data into customers and purchases
-- Your code here

Need a hint?

Use INSERT INTO with multiple rows for both tables.

3

Write a query using LATERAL join to find each customer's latest purchase date

Write a SELECT query that uses a LATERAL join with alias latest on purchases to get the most recent purchase_date for each customer. Select customers.customer_id, customers.customer_name, and latest.purchase_date AS latest_purchase.

PostgreSQL

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    customer_name TEXT
);

CREATE TABLE purchases (
    purchase_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    purchase_date DATE
);

INSERT INTO customers (customer_id, customer_name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');

INSERT INTO purchases (purchase_id, customer_id, purchase_date) VALUES
(101, 1, '2024-01-10'),
(102, 1, '2024-02-15'),
(103, 2, '2024-01-20'),
(104, 3, '2024-03-05');

-- Write a SELECT query using LATERAL join to get latest purchase date per customer
-- Your code here

Need a hint?

Use JOIN LATERAL with a subquery that orders purchases by date descending and limits to 1.

4

Complete the query with ordering and aliasing

Add an ORDER BY customers.customer_id clause at the end of the query to sort results by customer ID.

PostgreSQL

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    customer_name TEXT
);

CREATE TABLE purchases (
    purchase_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    purchase_date DATE
);

INSERT INTO customers (customer_id, customer_name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');

INSERT INTO purchases (purchase_id, customer_id, purchase_date) VALUES
(101, 1, '2024-01-10'),
(102, 1, '2024-02-15'),
(103, 2, '2024-01-20'),
(104, 3, '2024-03-05');

SELECT customers.customer_id, customers.customer_name, latest.purchase_date AS latest_purchase
FROM customers
JOIN LATERAL (
    SELECT purchase_date
    FROM purchases
    WHERE purchases.customer_id = customers.customer_id
    ORDER BY purchase_date DESC
    LIMIT 1
) AS latest ON true
-- Add ORDER BY clause here

Need a hint?

Use ORDER BY customers.customer_id at the end of the query to sort results.