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

https://www.youtube.com/watch?v=5F3Oa7nky64

[00:11] hello friends.
[00:14] so we'll start scenario based questions.
[00:17] part one so we have a problem statement.
[00:22] over here the transaction table has four columns.
[00:26] customer ID transaction ID transaction amount and transaction date.
[00:33] now user has to display all these fields along with maximum transaction amount for each customer ID and ratio of transaction amount and maximum transaction amount for each transaction.
[00:47] so if you look this table transaction table we have customer ID you can see there are two customer ID basically customer ID 1001 and 1002 and related transaction ID is there and corresponding transaction amount is there and transaction date is also available.
[01:07] so what we have to display we have to display the maximum amount.
[01:13] transaction amount in each customer ID.
[01:15] category.
[01:18] so if we take this customer ID 1001 the maximum amount is the 80,000.
[01:22] and in 1002 customer customer ID we have 22,000 is the maximum.
[01:26] now we have to find the ratio of transaction amount and the maximum transaction amount.
[01:33] so the output will be like see the highlighted one is the new columns which we have to add while writing the SQL query.
[01:47] so we have to get the maximum amount transaction amount which is present in the customer ID for the customer ID 1001 and 1002.
[01:54] and we have to get the ratio.
[02:02] now well I will show you how to solve this problem.
[02:09] so we can solve the our problem.
[02:14] statement by two methods first one is by using sub query and the other one is by you a common table expression that is CTE so first I will explain by using sub-query.
[02:27] so you know that in any complex SQL query it is the sub query which gets executed first.
[02:36] so our requirement in this the main requirement is we have to get the maximum transition amount if we get the maximum transaction amount then we can get a we can easily get the ratio so our first what we will do here first we will get the maximum transaction amount for each customer ID by simply grouping it by customer ID.
[02:59] so if I run this piece of code which is a sub query it is returning maximum transaction amount for each customer ID now what we have to do we have to a maketh inner join between
[03:15] our transaction table and this subway sub query table on the customer ID so.
[03:22] here what we are doing in select query select clause we are selecting the transaction amount the maximum transaction amount from the sub query.
[03:29] and the ratio of the transaction amount to the maximum transaction of maximum transaction amount as a ratio so if I run this whole piece of code will get that desire desired result you see over here for the customer ID 1,001 we have the maximum transition amount as 80,000 which is displaying for each transaction for the customer ID 1001 similarly for customer ID 1002 we have the maximum transaction amount as 22,000 which is getting displayed for each connection for customer ID now our second requirement is to get the ratio the ratio is nothing but the transaction.
[04:15] amount our maximum transaction amount.
[04:18] which is displayed over here so this is how we can solve this problem statement.
[04:21] by using a simple using sub query now we can do a similar similar methods by using common table expression.
[04:33] so in common table expression we are in the first in the first clause we are selecting customer ID transaction ID and transaction amount from the main transaction table and we are storing it in a temporary result set called city.
[04:50] now our second requirement is to get the maximum transaction amount so here we'll get another temporary result set named as City maxed ran where we will be getting the maximum transition amount by simply grouping by the customer ID which is getting stored in the in the secondary result such temporary result.
[05:15] Set that is city maximum transaction now.
[05:18] We have we have what we have to do is to simply join these CPE which is the first temporary digital set with the second result set the receipt EE maxed ran here.
[05:29] What I am doing you are making of your selecting transaction ID transaction amount maximum transaction amount and the ratio from the first result set that is city and the second result set that is CT maxed ran one customer ID.
[05:44] If I run the whole piece of code you will get the same result what we have got the earlier one.
[05:51] So there are two ways to techniques by which we can solve the our problem statement.
[05:59] So if you like this video please do like it and do subscribe the channel.
[06:04] Thank you.
[06:04] Thanks for watching.