Solving a Complex SQL Interview problem | Practice SQL Queries
HTML-код
- Опубликовано: 25 июл 2024
- In this video, let us solve a complex SQL interview problem. This is a REAL SQL Interview question that might seem impossible to solve just by using SQL at first. But during the video, I will simplify the problem by breaking it into multiple parts.
We shall first determine an approach that can solve such problems easily and then write the SQL query to solve this problem.
You can download the dataset, scripts, and solution from my website below:
techtfq.com/blog/lets-simplif...
🔴 My Recommended courses 👇
✅ Learn complete SQL: learnsql.com/?ref=thoufiqmoha...
✅ Practice SQL Queries: www.stratascratch.com/?via=te...
✅ Learn Python: codebasics.io/courses/python-...
✅ Learn Power BI: codebasics.io/courses/power-b...
🔴 WATCH MORE VIDEOS HERE 👇
✅ SQL Tutorial - Basic concepts:
• SQL Tutorial - Basic c...
✅ SQL Tutorial - Intermediate concepts:
• SQL Tutorial - Interme...
✅ SQL Tutorial - Advance concepts:
• SQL Tutorial - Advance...
✅ Practice Solving Basic SQL Queries:
• Practice Solving BASIC...
✅ Practice Solving Intermediate SQL Queries:
• Practice Solving INTER...
✅ Practice Solving Complex SQL Queries:
• Practice Solving COMPL...
✅ Data Analytics Career guidance:
• Data Analytics career ...
✅ SQL Course, SQL Training Platform Recommendations:
• SQL Course / Training
✅ Python Tutorial:
• Python Tutorial
✅ Git and GitHub Tutorial:
• Git and GitHub
✅ Data Analytics Projects:
• Data Analytics Projects
THANK YOU,
Thoufiq
Hi, I could get the same result without using joins into the CTEs. Also, when calculating the Row Number, you can add another CTE to avoid the subquery.
The way you explain and breakdown the problem in small parts is awesome.
Thank you so much for your contribution, such a nice content great job. :)
Great 👍 instructor of SQL every concept of SQL beginners to advance awesome here
what a beautiful solution to such a complex problem statement. I am mesmerized by your critical thinking and your approach. WOW! just WOW
Beautiful solution sir!!
Explanation was too good
And steps to approach the problem 💯
Your logic and problem breakdown approach is awesome....anyone with zero sql knowledge can understand...Thanks for the content...do solve more and more interview questions and upload videos frequently as much as possible....
thanks buddy, glad it was helpful!
you are too good, it helped me lot to improve my sql
thank you for providing such informative content. Your explanation of the concept of recursive has enabled me to comprehend it more fully. I greatly appreciate your efforts in sharing your knowledge !!
What a fantastic explanation. Great job and thank you!
Hi,
can you please create video on query optimization and performance tuning. Also explain best case practices to write queries.
In any sql interview asking tricky questions on performance tuning is really very common. So it will help us a lot.
Thanks in advance.
I have learned so much from you.
If you have time, could you make a video on triggers?
Thank you for your teaching.
I like your problem Breakdown, Great content🙌
Thanks for sharing, able to understand step by step clearly
phenomenal solving approach ! Very helpful ! Thanks much
your problem breakdown approah is really awesome...
Thanks for such an interesting and wonderful query question
Thanks for sharing your knowledge
Hello Sir, your Sql content helps me a lot to understand each n everything about sql in depth. Apart from this it's a request to you that guide me to gain knowledge of plsql in detail..
It was very interesting. I wanted to implement it in SQL Server
This was a good challenge, I followed the same approach in MySQL with slight differences:
with cte_order as (with recursive cte1 as (select order_number,1 n from orders
union
select o.order_number,n+1 from cte1, orders o where n
Love your complex SQL contents.
The first part that breakdown batch_id and order_id I actually come up with a solution using generate_series and cross join lateral.
---select batch_id,1 as quantity,row_number () OVER (order by batch_id) as rn from batch CROSS JOIN LATERAL generate_series(1,quantity,1);
Hope this is helpful to anyone :)
this is a very good alternative. thanks for sharing!
Very short and straight forward 🔥👍
Generate_series will work from Compatibility level 160 (2022 version)
hello sir,
If u don't mind I need u to make a video on what actually is schema and how is it different from a database? Also we don't need to create a schema in mySQL rdbms but MSSQL has a schema inside a database what does that really mean? Your explanation and grasp on the concept is so good that it takes just that one video to understand the concept instead of scrolling multiple videos for the same topic.
I hope you would consider making a video on the same so that this basic difference gets clarified in one shot.
Thank you!! All videos of yours are just awesome. Can you please make a video how EXPLAIN keyword in SQL.
Thanks for this awesome video! Can you explain the difference between "RECURSIVE" and "OPEN CURSOR?"
OMG ur great...plz complete playlist of MYSQL plz ...
Awesome explanation. Thank you for solving this problem. I tried to do the same in SQL server but with clause is not working as iner query of select statement. Can you please share the SQL server query for the same problem. Many thanks in advance
Splendid work thoufiq!!!!!!
I think I have a better solution. Code IS written for Mysql.
-- creating shema
drop schema if exists test_schema;
create schema test_schema;
use test_schema;
SET SQL_SAFE_UPDATES = 0;
-- creating raw tables
create table batch
(BATCH_ID char(5), QUANTITY int);
create table orders
(ORDER_NUMBER char(5), QUANTITY int);
-- adding values in that tables
insert into batch
vaLues("B1", 5),
("B2", 12),
("B3",8);
insert into orders
vaLues("O1", 2),
("O2", 8),
("O3",2),
("O4",5),
("O5",9),
("O6",5);
-- adding column 'up_border' in tables orders and batch
alter table batch
add column up_border int;
alter table orders
add column up_border int;
-- adding comulative sum of quantity in column 'up_border'
WITH v_batch_tmp AS
( SELECT BATCH_ID, sum(QUANTITY) over (order by BATCH_ID) as up_border
FROM batch )
update batch, v_batch_tmp
set batch.up_border = v_batch_tmp.up_border
where batch.BATCH_ID = v_batch_tmp.BATCH_ID;
WITH v_orders_tmp AS
( SELECT ORDER_NUMBER, sum(QUANTITY) over (order by ORDER_NUMBER) as up_border
FROM orders )
update orders, v_orders_tmp
set orders.up_border = v_orders_tmp.up_border
where orders.ORDER_NUMBER = v_orders_tmp.ORDER_NUMBER;
-- union of batches and orders for finding all up boundries
create table tmp_uni_ob
select row_number() over ( order by up_border) as RN,
ifnull(up_border - LAG(up_border)
OVER (ORDER BY up_border ),up_border) AS numbers,
tn.* from
(SELECT BATCH_ID as ID, up_border
FROM batch
UNION
SELECT ORDER_NUMBER as ID, up_border
FROM orders) as tn;
-- adding right order and branch id in results
create table expected_output
SELECT O.ORDER_NUMBER, B.BATCH_ID,
sum(t1.numbers) as numbers
FROM test_schema.tmp_uni_ob t1
inner join test_schema.orders o
on t1.up_border o.up_border-o.QUANTITY
inner join test_schema.batch B
on t1.up_border B.up_border-B.QUANTITY
group by O.ORDER_NUMBER, B.BATCH_ID
order by O.ORDER_NUMBER, B.BATCH_ID;
select * from expected_output;
-- droping tmp tables
drop table if exists tmp_uni_ob;
One day i can become pro in sql like you just by watching your contents 😊
A big 🙌 for your knowledge and thanks again for giving such a clear content 🙏
awesome!! what DB tool are you using to run those queries?
Hello sir...Can you please create a detail video on cursors and Tigger and how to use and when to use with some scenarios
Superbly done ✅ !
Very clever solution.
Would you please make a complete sql playlist end to end in you tube... If possible
Noted bro ,
@@techTFQ will wait. Thank you
@@techTFQ yes it would be really helpful if we can get a complete playlist by you.. As your way of teaching is Fab.. 😊
@@techTFQ bro I am also expecting this complete sql videos thanks
thank you for the video, perfect, very clear. but this will work only with integer numbers (without decimals), can we use the same method with numbers having decimals? without using the loop option but the recursive option? 😄
sir please make videos on power bi too, so that i as a fresher can be ready for job search.
Hai sir u r teaching is awesome
Awesome explanation ❤
Thank you for this video!! I just wanted to know how did the join condition work if
b.batch_id = cte.batch_id i.e. 5 = 1 are not equal??
It was so awesome 👍👍👍
One word - Awwwesome🎉
Great one bhai ek number
Thank you for sharing
Your welcome 🙏🏼
Great Approach!
Thanks:)
could u please make a video on grouping, grouping_id functions with multiple columns.
Hi thafiq
Can you please explain about why table has skewed
great awesome
Which certification cover all the topics from basic to advance SQL concept
Simply mind-blowing 🙌🙌🙌🙌
Thank you ☺️
Please make video on nested charindex and nested replace
Can we write logs from stored procedure to txt file without using "xp_cmdshell" in ms sql server.
Hi , I have one more if the Order qty is -1 or -2 how to add the stock to recent batch
what about generate_series function? it seems to me it could be easier...
Hello Toufeeq, interview question
Is it possible to generate sequence on Database side for an ID column in a table?
ID (p.k varchar) - can hold only 2 digits
A1,
A2
.
.
.
A9
B1,
B2,
.
.
.B9
C1
That’s change the alpha character to B after it reaches A9 similarly change it to C after it reaches B9
Please do a video on this. Thanks
Hi Thoufiq, Thanks for the video. When are you planning your SQL session?
Hopefully in a months time
Awesome
Awesome content.🎉
thank you 🙏🏼
Hi bro, do videos on snowflake technology also
Quite a complex problem but we'll explained❤
Thank you ☺️
Superb
Thanks:)
Hi sir just I am getting confused on this query last like you mentioned b.batchid=cte.batchid and b.quantity< cte.quantity how this both will work at a time please help on this
B.quantity>cte.quantity this will come 9 then how would be the 5 can please help on clarify on this doubt
how will get daily monthly weekly data in single query using procedure any suggestions please
The logic is kind of same but much simpler one.
with b_cte as
(select *, row_number() over() rnb from batch
cross join lateral
(select 1 as b_quantity from generate_series(1,quantity) ) as b),
o_cte as
(select *, row_number() over() rno from orders
cross join lateral
(select 1 as o_quantity from generate_series(1,quantity) ) as o)
select o.order_number, b.batch_id , sum(b_quantity) quantity
from o_cte o
left join b_cte b on b.rnb = o.rno
group by 1,2
order by 1,2
Hi, Would you please explain alternative way to solve this query with out using RECURSIVE. Since I couldn't do it with out using RECURSIVE.
have you figure out a way to solve it without recursive?
@@user-hv3iq8ur3l
My solution on MySQL without repeating each value one by one according to the quantity, this code would also scale well for higher quantity values.
with cte as(
select null as batch_number, orders.*,sum(quantity) over(order by order_number) as cumsum from orders
union all
select batch_id,null, quantity, sum(quantity) over(order by batch_id) from batch),
cte2 as (
select *,
sum(case when order_number is null then 0 else 1 end) over(order by cumsum desc, order_number desc) as group_o,
sum(case when batch_number is null then 0 else 1 end) over(order by cumsum desc, order_number desc) as group_b,
cumsum - lag(cumsum,1,0) over(order by cumsum, order_number desc) as dif
from cte),
cte3 as(
select *, max(batch_number) over(partition by group_b) as b,
max(order_number) over(partition by group_o) as o
from cte2 order by cumsum, order_number asc)
select o,b, case when b is null then null else sum(dif) end quantity from cte3
where group_b !=0 or group_o =1
group by o,b
having quantity !=0 or quantity is null
Thanks for these videos,
Your joins explanations helped me in my interview.
I got an question in my interview,
For example 2 tables A and B are there
With ID and phn number
We have to write an SQL query with which
If any change in number for same id in A it will go for an update,
For not finding it , it should go for insert and for same id and number it should ignore.
I was using Merge function, but however got stuck with ignore part, can you pls help me in that.
Cant explain in a comment ,, but this is similar to SCD Type 2.
@@sathishs7539 Yeah, I can do it via ETL tool.. but working out this with SQL query.. I need small guidance
Bro I think u hv to write delete cascade and update cascade
You’re a Sql bad ass!!
hi.....
can u please answer my question?
we have multiple tables in schema and we have column id,how to find perticular table name by using column id?
Select * from information_schema.columns where column_name like '' it will work.
how to solve this question in oracle SQL
please exp.
Hi Thoufiq sir,
I have come up with the solution but it is lengthy. Can you please give your verdict on this?
Solution :
with batch_cte as
(
select max(quantity) as max_quantity from batch
),
batch_cte1 as
(
select 1 as quantity
union all
select quantity+1 from batch_cte1
where quantity < (select max_quantity from batch_cte)
),
order_cte as
(
select max(quantity) as max_quantity from orders
),
order_cte1 as
(
select 1 as quantity
union all
select quantity+1 from order_cte1
where quantity < (select max_quantity from order_cte)
),
batch_data as
(
select a.batch_id , b.quantity , row_number() over(order by batch_id) as seq from batch as a , batch_cte1 as b where b.quantity
Hi sir, please send me link of SQL basic to advance course link, i wank to subscribe. Thank you..
Hi ,I need sql from basics to advanced level i can have the previous class videos and i will pay the nxt was on june 3rd but from nxt month onwards i have to do the work so if possible i will learn from this month onwards and i mailed you regarding this kindly rply me
In sql server WITH CTE is working bus as we use CTE as inner query giving can you please help so i can use in sql server also
Yes, I have the same problem in oracle, trying to solve it now (ORA-32034: unsupported use of WITH clause)
You can not use CTE as derived table in SQL server, make seprate view of both cte and apply left join logic.
@@rahulkumbhar533 Thanks a lot
👌👌
Note: without using subqueries. --> MYSQL... POSTGRE...
with batch_split as
(with recursive cte as (
select batch_id, quantity
from c_batch
union
select batch_id, quantity - 1
from cte
where quantity > 1)
select batch_id, 1 as quantity, row_number() over() as id
from cte
order by 1),
order_split as
(with recursive cte as (
select order_number, quantity
from c_orders
union
select order_number, quantity - 1
from cte
where quantity > 1)
select order_number, 1 as quantity, row_number() over() as id
from cte
order by 1)
select o.order_number, b.batch_id, sum(o.quantity) as quantity
from order_split o
left join batch_split b
on o.id = b.id
group by o.order_number, b.batch_id
The expression subject IN ('chemistry','physics') can be used as a value - it will be 0 or 1.
Show the 1984 winners and subject ordered by subject and winner name; but list chemistry and physics last.
Pls help me this SQL query 😢
This all videos questions for only freshers or experience person also?
It’s for everyone .. there is playlist for basic, intermediate and complex.. this particular question is under complex
@@techTFQ this type of questions, can company ask for freshers? Except faagn company
May be not
@@techTFQ hello sir have some doubt.... Those sql queries what you wrote in video... Can we directly import them to power bi and make it as a dashboard (those complex queries as a visualization)
In real life scenario,if you have millions of orders & quantities, wont expansion of tables by creating 1 record for each quantity result in performance bottlenecks??
cannot say for certain, the query is effecient enough to handle huge amounts of data
My solution on MySQL without repeating each value one by one according to the quantity, this code would also scale well for higher quantity values.
with cte as(
select null as batch_number, orders.*,sum(quantity) over(order by order_number) as cumsum from orders
union all
select batch_id,null, quantity, sum(quantity) over(order by batch_id) from batch),
cte2 as (
select *,
sum(case when order_number is null then 0 else 1 end) over(order by cumsum desc, order_number desc) as group_o,
sum(case when batch_number is null then 0 else 1 end) over(order by cumsum desc, order_number desc) as group_b,
cumsum - lag(cumsum,1,0) over(order by cumsum, order_number desc) as dif
from cte),
cte3 as(
select *, max(batch_number) over(partition by group_b) as b,
max(order_number) over(partition by group_o) as o
from cte2 order by cumsum, order_number asc)
select o,b, case when b is null then null else sum(dif) end quantity from cte3
where group_b !=0 or group_o =1
group by o,b
having quantity !=0 or quantity is null
Hi TFQ, good problem, thanks for sharing and breaking it down. Btw, I could figure out another solution:
with batch_proc
as
(
select batch_id,
1 as qty,
row_number() over (order by batch_id) as rn
from batch
cross join generate_series(1,quantity)
),
orders_proc
as
(
select *,
coalesce(lag(cum_sum) over (order by order_number asc),0) as prev_sum
from
(
select order_number,
quantity as qty,
sum(quantity) over (order by order_number asc) as cum_sum
from orders
) tmp
)
select order_number, batch_id, sum(y.qty)
from orders_proc x
left join batch_proc y
on x.prev_sum < y.rn
and x.cum_sum >= y.rn
group by order_number, batch_id
order by order_number, batch_id
bhai your website is down from long time
It’s not.. works fine here .. can u check using diff browsers or your internet connection?
Let me know what errors u get
@@techTFQ Dang this is strange, it doesnt open with my fibre broadband connection in phone or pc but opens with mobile data in phone. All this while i thought the website was down and forgot to ask in comments 😅
Ps. Just tried VPN, it works with VPN in Pc broadband connection 🤔
Hi sir i receieved a following question from mnc can you please help me on this
Tble A Table B
1 1
1 1
Null 1
Null
Inner join?left?right?fulloj?
inner join 6
left join 7
right join 7
full join 8
Basically you used sql to do for loop logic hahah
Can you please help me TFQ for below query
Input
Empid. Name
1 Emp1
2 Emp2
3 Emp3
5 Emp5
6 Emp6
Output like below
New_column
1Emp1,2Emp2
3Emp3,4Emp4
5Emp5,6Emp6
This is interview question asked in Barclays
Use ntile () function
create table emp (empid int , name varchar(50))
insert into emp values (1,'Emp1'),(2,'Emp2'),(3,'Emp3'),(4,'Emp4'),(5,'Emp5'),(6,'Emp6')
with cts as (
select empid, name, ntile ( 3 )over ( order by empid ) gr From emp
)select STRING_AGG (convert (varchar,empid)+name,',') From cts
group by gr
Please try this
My solution on MySQL without repeating each value one by one according to the quantity, this code would also scale well for higher quantity values.
with cte as(
select null as batch_number, orders.*,sum(quantity) over(order by order_number) as cumsum from orders
union all
select batch_id,null, quantity, sum(quantity) over(order by batch_id) from batch),
cte2 as (
select *,
sum(case when order_number is null then 0 else 1 end) over(order by cumsum desc, order_number desc) as group_o,
sum(case when batch_number is null then 0 else 1 end) over(order by cumsum desc, order_number desc) as group_b,
cumsum - lag(cumsum,1,0) over(order by cumsum, order_number desc) as dif
from cte),
cte3 as(
select *, max(batch_number) over(partition by group_b) as b,
max(order_number) over(partition by group_o) as o
from cte2 order by cumsum, order_number asc)
select o,b, case when b is null then null else sum(dif) end quantity from cte3
where group_b !=0 or group_o =1
group by o,b
having quantity !=0 or quantity is null
Could you please send to me SQL scenarios quaries because it was very helpful to interviews pls help me sir few days i sent u r mail request sir
hello sir want to join sql bootcamp course plz guide how to join.please
🙏🙏🙏🙏🙏🙏🙏🙏🙏🙏🙏🙏🙏🙏🙏🙏🙏🙏
sql serv er
with test
as
(
SELECT
row_number() over(order by name) idx,
value val
FROM
arbitrary_values
CROSS APPLY STRING_SPLIT(name, ',')
)
,cte as
(
select *,1 as iter,max(idx) over() as max_idx from test
where idx=1
union all
select cv.*,(iter+1) as iter,max(cv.idx) over() as max_idx from cte join test cv
on cv.idx between max_idx+1 and max_idx+1+iter)
select iter as grp,string_agg(val,',') from cte
group by iter order by iter
-- My SOLUTION:
CREATE DEFINER=`root`@`localhost` PROCEDURE `interview`()
begin
declare finished int default 0;
-- batch Table
declare batchid varchar(20);
declare batch_quantity int default 0;
-- order Table
declare order_num varchar(20);
declare order_quantity int default 0;
-- counter
declare order_counter int default -1;
-- batch cursor;
declare batch_cursor cursor for select batch_id from batch;
declare continue handler for not found set finished=1;
open batch_cursor;
begin
create table if not exists batch_order(order_number varchar(20), batch_id varchar(20), quantity int);
end;
get_order:loop
-- -----------------------------------------------------------------------
if batch_quantity=0 then
fetch batch_cursor into batchid;
if finished=1 then leave get_order;end if;
select quantity into batch_quantity from batch where batch_id=batchid ;
end if;
-- -----------------------------------------------------------------------
if order_quantity=0 then
set order_counter=order_counter+1;
select order_number into order_num from orders limit order_counter,1;
select quantity into order_quantity from orders limit order_counter,1;
end if;
-- -----------------------------------------------------------------------
if batch_quantity>= order_quantity then
set batch_quantity=batch_quantity-order_quantity;
insert into batch_order(ORDER_NUMBER,BATCH_ID,QUANTITY)
values(order_num,batchid,order_quantity);
set order_quantity=0;
else
set order_quantity=order_quantity-batch_quantity;
insert into batch_order(ORDER_NUMBER,BATCH_ID,QUANTITY)
values(order_num,batchid,batch_quantity);
set batch_quantity=0;
end if;
end loop get_order;
select * from batch_order;
end
Very good explanation but expected solution is different how to reach?
I dint get you
@@techTFQ he is asking how I need to contact you like personally expecting mailid Or some other
declare @batch table (batch_id char(2), quantity int);
declare @orders table (order_number char(2), quantity int);
declare @processingOrder table (order_number char(2), quantity int);
declare @output table (order_number char(2), batch_id char(2), quantity int)
declare @order_number char(2), @batch_id char(2)
, @bq int, @oq int, @total int, @totalQuantity int;
insert into @batch(batch_id, quantity) values
('B1',5),('B2',12),('B3',8);
insert into @orders(order_number, quantity) values
('O1',2),('O2',8),('O3',2),('O4',5),('O5',9),('O6',5);
insert into @processingOrder(order_number, quantity) values
('O1',2),('O2',8),('O3',2),('O4',5),('O5',9),('O6',5);
select @totalQuantity = sum(quantity) from @batch;
while @totalQuantity > 0
begin
select top 1 @order_number = order_number, @oq= quantity
from @processingOrder order by order_number
select top 1 @batch_id = batch_id, @bq= quantity
from @batch order by batch_id
if @bq > @oq
begin
begin tran
insert into @output (order_number, batch_id, quantity)
values (@order_number, @batch_id, @oq);
update @batch set quantity = @bq-@oq where batch_id = @batch_id;
delete from @processingOrder where order_number = @order_number;
set @totalquantity = @totalquantity - @oq;
commit
end
else
begin
begin tran
insert into @output (order_number, batch_id, quantity)
values (@order_number, @batch_id, @bq);
update @processingOrder set quantity = @oq-@bq where order_number = @order_number
delete from @batch where batch_id = @batch_id;
set @totalquantity = @totalquantity - @bq;
commit
end
delete from @batch where quantity = 0;
delete from @processingOrder where quantity = 0;
end
select o.ORDER_NUMBER
, ot.BATCH_ID
, ot.QUANTITY
from @orders as o
left outer join @output ot on o.order_number = ot.order_number;