FIRST_VALUE and LAST_VALUE in PostgreSQL - Mini Project: Build & Apply

Using FIRST_VALUE and LAST_VALUE in PostgreSQL

📖 Scenario: You work for a retail company that tracks sales data. You want to find the first and last sale dates for each product to understand sales trends.

🎯 Goal: Create a PostgreSQL query that uses FIRST_VALUE and LAST_VALUE window functions to find the earliest and latest sale dates for each product.

📋 What You'll Learn

Create a table called sales with columns product_id (integer), sale_date (date), and amount (numeric).

Insert the exact sales data provided.

Define a window partition by product_id ordered by sale_date ascending.

Use FIRST_VALUE(sale_date) to get the first sale date per product.

Use LAST_VALUE(sale_date) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) to get the last sale date per product.

💡 Why This Matters

🌍 Real World

Finding first and last events or values per group is common in sales, finance, and event tracking systems.

💼 Career

Understanding window functions like FIRST_VALUE and LAST_VALUE is essential for data analysts and database developers to write advanced queries.

1

Create the sales table and insert data

Create a table called sales with columns product_id (integer), sale_date (date), and amount (numeric). Then insert these exact rows: (1, '2024-01-10', 100.00), (1, '2024-02-15', 150.00), (2, '2024-01-05', 200.00), (2, '2024-03-20', 250.00), (3, '2024-02-01', 300.00).

PostgreSQL

-- Create the sales table and insert the exact data
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO with multiple rows for the data.

2

Define the window partition and order

Write a SELECT statement from sales that selects product_id, sale_date, and amount. Define a window called product_window that partitions by product_id and orders by sale_date ascending.

PostgreSQL

CREATE TABLE sales (
    product_id INTEGER,
    sale_date DATE,
    amount NUMERIC
);

INSERT INTO sales (product_id, sale_date, amount) VALUES
(1, '2024-01-10', 100.00),
(1, '2024-02-15', 150.00),
(2, '2024-01-05', 200.00),
(2, '2024-03-20', 250.00),
(3, '2024-02-01', 300.00);

SELECT product_id, sale_date, amount
FROM sales
WINDOW product_window AS (PARTITION BY product_id ORDER BY sale_date ASC);
-- Your code here

Need a hint?

Use the WINDOW clause to define product_window with partition and order.

3

Use FIRST_VALUE to get the first sale date per product

Modify the SELECT statement to include FIRST_VALUE(sale_date) OVER product_window AS first_sale_date to get the earliest sale date for each product.

PostgreSQL

CREATE TABLE sales (
    product_id INTEGER,
    sale_date DATE,
    amount NUMERIC
);

INSERT INTO sales (product_id, sale_date, amount) VALUES
(1, '2024-01-10', 100.00),
(1, '2024-02-15', 150.00),
(2, '2024-01-05', 200.00),
(2, '2024-03-20', 250.00),
(3, '2024-02-01', 300.00);

SELECT product_id, sale_date, amount, FIRST_VALUE(sale_date) OVER product_window AS first_sale_date
FROM sales
WINDOW product_window AS (PARTITION BY product_id ORDER BY sale_date ASC);
-- Your code here

Need a hint?

Add FIRST_VALUE(sale_date) OVER product_window AS first_sale_date to the SELECT list.

4

Use LAST_VALUE to get the last sale date per product

Add LAST_VALUE(sale_date) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_sale_date to the SELECT statement to get the latest sale date per product.

PostgreSQL

CREATE TABLE sales (
    product_id INTEGER,
    sale_date DATE,
    amount NUMERIC
);

INSERT INTO sales (product_id, sale_date, amount) VALUES
(1, '2024-01-10', 100.00),
(1, '2024-02-15', 150.00),
(2, '2024-01-05', 200.00),
(2, '2024-03-20', 250.00),
(3, '2024-02-01', 300.00);

SELECT product_id, sale_date, amount, FIRST_VALUE(sale_date) OVER product_window AS first_sale_date, LAST_VALUE(sale_date) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_sale_date
FROM sales
WINDOW product_window AS (PARTITION BY product_id ORDER BY sale_date ASC);
-- Your code here

Need a hint?

Use LAST_VALUE with a frame clause to get the last sale date per product.