MySQL Query to Find First and Last Sale Date for Each Customer

Find the First and Last Sale Date for Each Customer

Write a MySQL query to find the first and last sale date for each customer using a window function.

Solution:

-- Begin the SELECT statement to specify the columns to retrieve
SELECT 
    -- Retrieve the CustomerID column to uniquely identify each customer
    CustomerID, 
    -- Retrieve the SaleDate column to display the date of each sale
    SaleDate,
    -- Calculate the first sale date for each customer:
    -- Use the FIRST_VALUE function to get the earliest SaleDate within each customer's partition,
    -- Partition by CustomerID and order by SaleDate to ensure the first value is the earliest date.
    FIRST_VALUE(SaleDate) OVER (PARTITION BY CustomerID ORDER BY SaleDate) AS FirstSaleDate,
    -- Calculate the last sale date for each customer:
    -- Use the LAST_VALUE function over a window frame that covers all rows in the partition,
    -- Partition by CustomerID, order by SaleDate, and set the frame from the first to the last row.
    LAST_VALUE(SaleDate) OVER (PARTITION BY CustomerID ORDER BY SaleDate ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastSaleDate
-- Specify the Sales table as the source of the data
FROM Sales;

Explanation:

• Purpose of the Query:
• The goal is to find the first and last sale date for each customer.
• This demonstrates the use of the FIRST_VALUE() and LAST_VALUE() window functions.
• Key Components:
• FIRST_VALUE(SaleDate) OVER (PARTITION BY CustomerID ORDER BY SaleDate): Retrieves the first sale date for each customer.
• LAST_VALUE(SaleDate) OVER (PARTITION BY CustomerID ORDER BY SaleDate ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING): Retrieves the last sale date for each customer.
• Why use Window Functions?:
• Window functions allow you to access the first and last values in a partition, making it easier to analyze the range of data for each group.
• Real-World Application:
• For example, in a retail business, you might want to analyze the purchasing behavior of customers by identifying their first and last purchase dates.

For more Practice: Solve these Related Problems:

• Write a MySQL query to find customers who made their first purchase within the last year.
• Write a MySQL query to identify customers whose last purchase was over 6 months ago.
• Write a MySQL query to calculate the number of days between a customer's first and last purchase.
• Write a MySQL query to find customers who made their first and last purchase in the same month.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous MySQL Exercise: Calculate the Difference between Each Employee's Salary and the Department Average.
Next MySQL Exercise: Calculate the Percentage of Total Sales for each Product.