SQL Query To Find Products Where Revenue Increased
|
Article Topic SQL Query To Find Products Where Revenue Increased?
Write down a SQL Query to find a product whose total sales revenue has increased every year. Here you also need to add product_id, product_name and category in the query output.
Below are the two table which are Sales and Product table.
And output should be like below this.
Table of Contents
(1)Step 1:- Check the Correct Product to return
(2)Step 2:- Use of predefine function
(3)Step 3:-Get difference in Revenue Change
(4)Step 4:- Use of Table Parameter
(5)Step 5:-Use of MIN and Group by clause
(6)Step 6:-Final Step
|
(1)Step 1:- Check the Correct Product to return
|
Now in first we will check which product-id is correct answer of our query that we need to create. So as per below image only product id 3 is correct as this have the revenue increased.
|
(2)Step 2:- Use of predefine function
|
In query we use predefine function which is LAG and syntax for LAG is given below
LAG(expression,offset,default) which return Int
And below is the next step to run the query
select ProductID,Year,Sales,LAG(Sales) OVER (Partition by ProductId order by Year) as RevenueChange from tblsale
And output of above query is below which also return RevenueChange compare to previoud years:-
|
(3)Step 3:- Get difference in Revenue Change
|
And below query is used which return the difference in between sales for product wise
select ProductID,Year,Sales -LAG(Sales) OVER (Partition by ProductId order by Year) as RevenueChange from tblsale
And below is the output of above query
|
|
|
(4)Step 4:- Use of Table Parameter
|
Now we join this tablename with table to get the other column as gievn below
with RevenueChange as
(select ProductID,Year,Sales -LAG(Sales) OVER (Partition by ProductId order by Year) as RevenueChange from tblsale)
select P.ProductID,P.NameofProduct,P.Category,R.RevenueChange
from tblProduct P JOin RevenueChange R
on P.ProductId=R.ProductID
and output of above query is given below
|
(5)Step 5:- Use of MIN and Group by clause
|
Now we use MIN and Group by clause to find the all value which have difference of sales in + or -.
with RevenueChange as(
select ProductID,Year,Sales -LAG(Sales) OVER (Partition by ProductId order by Year) as RevenueChange from tblsale
)
select P.ProductID,P.NameofProduct,P.Category,min(R.RevenueChange)
from tblProduct P JOin RevenueChange R
on P.ProductId=R.ProductID
group by P.ProductID,P.NameofProduct,P.Category
and output of above query is given below
|
(6)Step 6:- Final Step
|
Now the final keyword we use in Having min(R.RevenueChange)>=0 which will provide the output we need
with RevenueChange as(
select ProductID,Year,Sales -LAG(Sales) OVER (Partition by ProductId order by Year) as RevenueChange from tblsale
)
select P.ProductID,P.NameofProduct,P.Category
from tblProduct P JOin RevenueChange R
on P.ProductId=R.ProductID
group by P.ProductID,P.NameofProduct,P.Category
having min(R.RevenueChange) >=0
and output of above query is given below
|
|
|