SQL Exercise – 6

SQL Exercise:

Suppose you have a Sales table that tracks product sales data, including the sale amount, product category, and sale date. You want to retrieve the last sale amount for each product category, but only for sales that occurred within the current year, and display this information alongside each sale in that category.

Table Creation Script:

CREATE TABLE Sales (
SaleID INT,
ProductCategory VARCHAR(50),
SaleAmount DECIMAL(10, 2),
SaleDate DATE
);

Insert Data into Sales Table:

INSERT INTO Sales (SaleID, ProductCategory, SaleAmount, SaleDate) VALUES
(1, 'Electronics', 500.00, '2024-01-10'),
(2, 'Electronics', 750.00, '2024-05-15'),
(3, 'Furniture', 1200.00, '2024-02-20'),
(4, 'Electronics', 650.00, '2024-08-12'),
(5, 'Furniture', 1300.00, '2024-09-30');

Solution:

SELECT 
SaleID,
ProductCategory,
SaleAmount,
SaleDate,
LAST_VALUE(SaleAmount) OVER (
PARTITION BY ProductCategory 
ORDER BY SaleDate 
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS LastSaleInCategory
FROM Sales
WHERE YEAR(SaleDate) = 2024;

 

Explanation:

• Table Filtering: The WHERE clause filters only sales occurring in 2024.
• Partition By: The ProductCategory ensures each category is treated as a separate group.
• Order By: The sales are sorted by the SaleDate within each category.
• Rows Between: The ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ensures that the LAST_VALUE function evaluates the last value of all rows in the partition.