SQL Interview Question - Solution (Part - XVIII) |

#education #dataengineers #datascience #sqlinterview #dataanalyst #dataanalytics #meanlifestudies
Here are My profiles that will definitely help your preparation for data analyst or data engineer roles.
Medium: / mahendraee204
Github: github.com/mah...
Here are create and insert statements:
-------------------------------------------------------------
create table sales_info (
TransactionID int primary key,
Date date,
CustomerID int,
CustomerName varchar(20),
ProductID varchar(20),
Quantity INT,
SalesAmount float,
StoreID varchar(20),
StoreName varchar(20)
)
INSERT INTO Sales_Info values (1, '2023-01-01', 1000, 'Mahendra', 'P1', 2, 20, 'S1', 'Store A'),
(2, '2023-01-02', 1003, 'Mahi', 'P2', 1, 50, 'S2', 'Store B'),
(3, '2023-01-03', 1008, 'Ananya', 'P3', 3, 45, 'S3', 'Store C'),
(4, '2023-01-04', 2004, 'Rohit', 'P2', 1, 50, 'S1', 'Store A'),
(5, '2023-01-05', 3009, 'Priya', 'P1', 2, 20, 'S2', 'Store B'),
(6, '2023-01-06', 1000, 'Mahendra', 'P3', 4, 60, 'S3', 'Store C'),
(7, '2023-01-07', 1003, 'Mahi', 'P2', 2, 100, 'S1', 'Store A'),
(8, '2023-01-08', 1008, 'Ananya', 'P3', 1, 15, 'S2', 'Store B'),
(9, '2023-01-09', 2004, 'Rohit', 'P1', 1, 120, 'S3', 'Store C'),
(10, '2023-01-10', 3009, 'Priya', 'P2', 3, 150, 'S1', 'Store A'),
(11, '2023-01-11', 1000, 'Mahendra', 'P1', 1, 510, 'S2', 'Store B'),
(12, '2023-01-12', 1003, 'Mahi', 'P3', 2, 300, 'S3', 'Store C'),
(13, '2023-01-13', 1008, 'Ananya', 'P2', 1, 580, 'S1', 'Store A'),
(14, '2023-01-14', 2004, 'Rohit', 'P1', 2, 200, 'S2', 'Store B'),
(15, '2023-01-15', 3009, 'Priya', 'P3', 1, 180, 'S3', 'Store C'),
(16, '2023-01-16', 1000, 'Mahendra', 'P2', 3, 950, 'S1', 'Store A'),
(17, '2023-01-17', 1003, 'Mahi', 'P1', 1, 100, 'S2', 'Store B'),
(18, '2023-01-18', 1008, 'Ananya', 'P3', 2, 300, 'S3', 'Store C'),
(19, '2023-01-19', 2004, 'Rohit', 'P2', 1, 500, 'S1', 'Store A'),
(20, '2023-01-20', 3009, 'Priya', 'P1', 3, 350, 'S2', 'Store B');

[@gsrsakhilakhil528]

Thank you so much sir
for making videos on daily after watching all your videos im feeling that im improving everyday

[@namangarg7023]

Very good knowledge u have about querying..tks

[@AnandKumar-dc2bf]

You are making awesome vidoes hope u get much more viewerships...

[@MeanLifeStudies]

I really wish and Thank you so much for you support.

[@namangarg7023]

this was tough

[@harishsingh_424]

Awesome 👍
Request you to please make a detailed video on the stored procedure.
I have searched the entire youtube but not getting helpful

[@MeanLifeStudies]

Thank you. I will do it soon.

[@namangarg7023]

I think u have used joined tables from 3 corrsponding tables to use over here

[@kailashpatro5768]

hi sir
what i did first i create one table min(date), max(date) after that i join the table with sales_info then after i by using lead() function i solved the solution
with cte as (
select customername , min(date) as first_date, max(date) as last_date from sales_info
group by customername )
select *, ((lead(SalesAmount,1) over(partition by c.customerName order by c.first_date)) - s.SalesAmount )*100/s.SalesAmount as prev_amount
from cte c inner join sales_info s on s.date in (c.first_date,c.last_date)

[@namangarg7023]

can we do it buy partition of productid instead of customerid . I was trying found different results as against customerid.
I suppose table is not in 2nf form thats why confusing

[@MeanLifeStudies]

Sorry. Understand the context behind.

[@namangarg7023]

Just one request pls use small cte names and aliases it would be easy for beginners all the best..🙂