SQL Interview Question - Solution (Part - XVIII) |
ฝัง
- เผยแพร่เมื่อ 14 ต.ค. 2024
- #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');
Thank you so much sir
for making videos on daily after watching all your videos im feeling that im improving everyday
Very good knowledge u have about querying..tks
this was tough
You are making awesome vidoes hope u get much more viewerships...
I really wish and Thank you so much for you support.
Awesome 👍
Request you to please make a detailed video on the stored procedure.
I have searched the entire youtube but not getting helpful
Thank you. I will do it soon.
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)
I think u have used joined tables from 3 corrsponding tables to use over here
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
Sorry. Understand the context behind.
Just one request pls use small cte names and aliases it would be easy for beginners all the best..🙂