SQL Exercise – 5

SQL Exercise:

You have a Sales table that tracks product sales in different cities for various dates. Now, you have to get the following information.

1. Retrieve the first sale amount for each city based on the earliest sale date.
2. Include all sales data but add a column showing the first sale amount for the respective city.

Table Creation Script:

CREATE TABLE Sales (
SaleID INT PRIMARY KEY,
Product VARCHAR(50),
City VARCHAR(50),
SaleAmount DECIMAL(10, 2),
SaleDate DATE
);

Insert Data into Sales Table:

INSERT INTO Sales (SaleID, Product, City, SaleAmount, SaleDate)
VALUES
(1, 'Laptop', 'Mumbai', 80000, '2024-01-01'),
(2, 'Smartphone', 'Delhi', 40000, '2024-01-02'),
(3, 'Laptop', 'Mumbai', 75000, '2024-01-03'),
(4, 'Tablet', 'Bangalore', 30000, '2024-01-04'),
(5, 'Smartphone', 'Delhi', 45000, '2024-01-05'),
(6, 'Monitor', 'Bangalore', 20000, '2024-01-06'),
(7, 'Laptop', 'Mumbai', 85000, '2024-01-07');

 

Solution:

SELECT saleid,
product,
city,
saleamount,
saledate,
First_value(saleamount)
OVER (
partition BY city
ORDER BY saledate ASC) AS FirstSaleAmount
FROM sales;

Output:

Explanation:

1. PARTITION BY City:
   • Divides the data into groups based on the City.
2. ORDER BY SaleDate ASC:
   • Within each city group, sorts the rows by SaleDate in ascending order.
3. FIRST_VALUE(SaleAmount):
   • Retrieves the first sale amount for each city based on the earliest sale date.