SQL Interview Questions and answers Part 1 | SQL Scenario based Interview Question and Answer

SQL Interview Questions and answers Part 1 | SQL Scenario based Interview Question and Answer
🔥🔥
Problem Statement:-
Transatcion_tbl Table has four columns CustID, TranID, TranAmt, and TranDate. User has to display all these fields along with maximum TranAmt for each CustID and ratio of TranAmt and maximum TranAmt for each transaction.

-------------------------------------------------------------------------
𝗝𝗼𝗶𝗻 𝗺𝗲 𝗼𝗻 𝗦𝗼𝗰𝗶𝗮𝗹 𝗠𝗲𝗱𝗶𝗮:🔥
-------------------------------------------------------------------------
🔴 Instagram :

🔴 Twitter:
-------------------------------------------------------------------------
🔴 Table and Insert SQL Script :
-------------------------------------------------------------------------
CREATE TABLE [dbo].[Transaction_Tbl](
[CustID] [int] ,
[TranID] [int] ,
[TranAmt] [float] ,
[TranDate] [date]
)

INSERT [dbo].[Transaction_Tbl] ([CustID], [TranID], [TranAmt], [TranDate]) VALUES (1001, 20001, 10000, CAST('2020-04-25' AS Date))
INSERT [dbo].[Transaction_Tbl] ([CustID], [TranID], [TranAmt], [TranDate]) VALUES (1001, 20002, 15000, CAST('2020-04-25' AS Date))
INSERT [dbo].[Transaction_Tbl] ([CustID], [TranID], [TranAmt], [TranDate]) VALUES (1001, 20003, 80000, CAST('2020-04-25' AS Date))
INSERT [dbo].[Transaction_Tbl] ([CustID], [TranID], [TranAmt], [TranDate]) VALUES (1001, 20004, 20000, CAST('2020-04-25' AS Date))
INSERT [dbo].[Transaction_Tbl] ([CustID], [TranID], [TranAmt], [TranDate]) VALUES (1002, 30001, 7000, CAST('2020-04-25' AS Date))
INSERT [dbo].[Transaction_Tbl] ([CustID], [TranID], [TranAmt], [TranDate]) VALUES (1002, 30002, 15000, CAST('2020-04-25' AS Date))
INSERT [dbo].[Transaction_Tbl] ([CustID], [TranID], [TranAmt], [TranDate]) VALUES (1002, 30003, 22000, CAST('2020-04-25' AS Date))
---------------------------------------------------------------------------------------------------------------------------------------

#SQLInterviewQuestionsandanswers #sqlInterviewQuestions #sqlInterviewQuestionsForTesting #sql InterviewQuestionsQuery #sqlInterviewQuestionsOnJoins #ITJunction4All #ETLInterviewQuestionsAndAnswers

[schymi]

I would also consider 3rd option, without cte or subquery, using max() over(partition by) like so:
select *,
MaxTranAmt = max(TranAmt) over(partition by CustID),
Ratio = TranAmt / (max(TranAmt) over(partition by CustID)),
TranDate
from Transaction_Tbl

____
And also: Good videos. Keep it up man.

[shivagarg]

Indeed, one of the best collections of SQL problems, which covers a wide range of concepts. I highly recommend going to each and every question before the interview.

Thanks

[quadirnawaz]

Thank you so much for the entire playlist. This series includes all the concept of SQL. Highly recommended to anyone who's watching this series. Also, thanks for providing table scripts as well ❤️

[prashant]

select custid, tranid, tranamt, tranamt/last_value(tranamt) over (partition by custid order by tranamt
rows between unbounded preceding and unbounded following
) as ratio
from Transaction_Tbl;

[antoourfalian]

is there a reason you're not using a window function to get the same results in a single query?

select *
, max(tranamt) over (partition by custid) as maxTranAmt
, TranAmt/max(tranamt) over (partition by custid) ratioTranAmt
from transaction_tbl

[ashimaghiya]

It is all still relevant what an amazing collection I only go through these before every interview

[ArunKumar-jhqe]

please Keep uploading these types of SQL interview questions. It's really Helpful for us. Thanks!

[Vtraveller]

the most awesome thing in video....you have a code to create table ...viewers can try by itself then look for you are doing Great job...please dont stop ..keep Bringing tricky questions.

[electronics]

Thanks for the explanation with great scenario.keep it up..

[YASHKUMARJAIN]

I am loving ur series, very informative and challenging
select tranid, tranamt, max(tranamt) over (partition by custid) maxi, tranamt / max(tranamt) over (partition by custid), trandate
from transaction_tbl

[keyurkulkarni]

Thank you for the playlist, it does help. Wondering if the following query will be easy to understand.
With cte as
(select *, MAX(TranAmt) OVER(Partition by CustID order by CustID) as max_transaction_amount
from Transaction_Tbl)

Select *, as ratio
From cte

[krantiabinash]

Nice explanation and excellent content. 👏

[MyShrihari]

Hi Sunil, Nice video and good explanation. Could you please put all the queries used in the video into the description section?

[kumaru]

It's really helpful .. do more on this ..

[pritzcutz]

select custid, tranid, transamt, trandate, transamt/maxtransamt as ratio, maxtransamt, trandate from (select custid, tranid,transamt,trandate, max(transamt) over (partition by custid order by custid) maxtransamt from tablename )

[amanpatra]

THESE ARE VERY HELPFUL KEEP UPLOADING ...EXCUSE ME FOR TYPING IN CAPS BECAUSE WRITING SQL CODE ALONG WITH WATCHING AND TOO LAZY TO TURN THE CAPSLOCK OFF

[anirbanbiswas]

the answer can achieved by this method
with cte as(select *, max(tranamt) over(partition by custid)as max_sal_per_cust
from transaction_tbl)
select *, round((tranamt/max_sal_per_cust), 2)as ratio from cte

[Ilovefriendswebseries]

with cte as(
select *, max(tranamt) over(partition by custid) as mx_amt from Transaction_Tbl)
select custid, tranid, tranamt, mx_amt, (tranamt/mx_amt) as ratio from cte;

[himanish]

Please speak about the optimisation of code.In interview they will ask which is more optimised ??

[jatinnandwani]

Thanks so much
This playlist is quite helpful