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

Using FIRST_VALUE and LAST_VALUE in SQL

📖 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 SQL query that uses FIRST_VALUE and LAST_VALUE window functions to find the first and last sale dates for each product.

📋 What You'll Learn

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

Insert the exact sales data provided.

Write a SQL query that selects product_id, sale_date, amount, the first sale date per product using FIRST_VALUE(sale_date), and the last sale date per product using LAST_VALUE(sale_date).

Use window functions partitioned by product_id and ordered by sale_date.

Ensure the LAST_VALUE function uses the correct frame clause to get the last sale date per product.

💡 Why This Matters

🌍 Real World

Retail companies analyze sales data to understand when products first and last sold, helping with inventory and marketing decisions.

💼 Career

Knowing how to use window functions like FIRST_VALUE and LAST_VALUE is important for data analysts and database developers to write advanced SQL queries.

1

Create the sales table and insert data

Create a table called sales with columns product_id (integer), sale_date (date), and amount (integer). Then insert these exact rows: (1, '2024-01-01', 100), (1, '2024-01-05', 150), (2, '2024-01-03', 200), (2, '2024-01-10', 250).

SQL

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

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Set up the window partition and order

Write a SQL query selecting product_id, sale_date, and amount from sales. Add a window function partitioned by product_id and ordered by sale_date. Assign this window to an alias called win.

SQL

CREATE TABLE sales (
    product_id INT,
    sale_date DATE,
    amount INT
);

INSERT INTO sales (product_id, sale_date, amount) VALUES
(1, '2024-01-01', 100),
(1, '2024-01-05', 150),
(2, '2024-01-03', 200),
(2, '2024-01-10', 250);

-- Write a SELECT query with a window named win partitioned by product_id and ordered by sale_date
-- Your code here

Need a hint?

Use the WINDOW clause to define a window named win partitioned by product_id and ordered by sale_date.

3

Add FIRST_VALUE and LAST_VALUE columns

Extend the SELECT query to include FIRST_VALUE(sale_date) OVER win as first_sale_date and LAST_VALUE(sale_date) OVER win as last_sale_date. Use the window named win.

SQL

CREATE TABLE sales (
    product_id INT,
    sale_date DATE,
    amount INT
);

INSERT INTO sales (product_id, sale_date, amount) VALUES
(1, '2024-01-01', 100),
(1, '2024-01-05', 150),
(2, '2024-01-03', 200),
(2, '2024-01-10', 250);

SELECT product_id, sale_date, amount,
-- Add FIRST_VALUE and LAST_VALUE columns here
-- Your code here
FROM sales
WINDOW win AS (PARTITION BY product_id ORDER BY sale_date);

Need a hint?

Use FIRST_VALUE(sale_date) OVER win and LAST_VALUE(sale_date) OVER win in the SELECT list.

4

Fix LAST_VALUE frame to get correct last sale date

Modify the LAST_VALUE(sale_date) function to include ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING frame clause inside the OVER(win ...) to ensure it returns the last sale date per product correctly.

SQL

CREATE TABLE sales (
    product_id INT,
    sale_date DATE,
    amount INT
);

INSERT INTO sales (product_id, sale_date, amount) VALUES
(1, '2024-01-01', 100),
(1, '2024-01-05', 150),
(2, '2024-01-03', 200),
(2, '2024-01-10', 250);

SELECT product_id, sale_date, amount,
       FIRST_VALUE(sale_date) OVER win AS first_sale_date,
       -- Fix LAST_VALUE frame clause below
       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 win AS (PARTITION BY product_id ORDER BY sale_date);

Need a hint?

Use the frame clause ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING inside the OVER for LAST_VALUE.