Solving a SQL Puzzle | Infosys SQL Interview Question

[@ankitbansal6]

Do hit the like the button if you like the puzzle 🙏

[@appsgames3513]

I am using lead function but it's taking too much time to fetch the row and some rows are fill with null value so how to increase query speed to fetch the rows

[@rajeshp9319]

What if there formula might be dynamic with out particular structure

[@AjaySingh-dq4df]

This is one of the most interesting question I have solved in SQL. Loved it.

[@kartika5151]

Got the same question in a screening test yesterday, just the question was one step simpler, operators and values were given separately. Since I have already watched this video was able to do it. Thanks a lot sir.👏🙌

[@ankitbansal6]

Awesome

[@deepeshmatkati3058]

Your problem solving approach and way of explanation is awesome !

[@ankitbansal6]

🙏

[@Reacher1998]

Thanks for the problem sir..Here's my approach (MySQL;): select a.id,a.formula,a.value, case when MID(a.formula,2,1)='+' then a.value+b.value else a.value-b.value end as new_value from input a join input b WHERE a.id=cast(LEFT(a.formula,1) as UNSIGNED) and b.id=cast(RIGHT(a.formula,1)as UNSIGNED) order by a.id; Would love to hear your feedback!

[@shahrukhtheanalyst]

many thanks for yet another interesting question Ankit, here is my take on the above: SELECT id, formula, value, CASE WHEN id = '1' THEN value + LEAD(value,3) OVER(ORDER BY id) WHEN id = '2' THEN value + LAG(value,1) OVER (ORDER BY id) WHEN id = '3' THEN value - LAG(value,1) OVER (ORDER BY id) WHEN id = '4' THEN value - LAG(value,3) OVER (ORDER BY id) end as new_value from input;

[@ankitbansal6]

You cannot do hard coding 😊

[@Winter_Soldier100]

But this query works and is much simpler than what the normal approach was , why can't we use this ?

[@shubhamthombre4038]

@@Winter_Soldier100 what will happen if you have million records? you cant hardcode million case statements that's bad program

[@blitzhope]

beta you cannot do hard coding

[@medleyworld5549]

This was one of the most complex problems till now :) Thanks for sharing Ankit.

[@Freakouts_and_found_Insane]

Ankit sir please make videos on ... python as well..And how much python we need to cover and practice for data analyst role

[@vishwassharma3312]

Sir if number of operands and operaters are different in different rows so how can we saperate?

[@hairavyadav6579]

outstanding explanation sir thanks lot for helping students.

[@pitou00]

Brilliant as usual Ankit sir

[@MixedUploader]

Ankit tried with single inner jpin below: with cte as(select sd.*,i.value as d2_value from (select *,left(formula,1) as d1,right(formula,1) as d2,substring(formula,2,1) as operator from input) sd inner join input i on sd.d2=i.id) select id,formula, case when operator='+' then value+d2_value else value-d2_value end as new_value from cte

[@jhonsen9842]

Exactly it will work .

[@vinothkumars7421]

Never knew SQL can do this. Thanks a bunch, Ankit

[@payalbhatia5244]

I am new to channel but often followed you on linkedin. I have been asked lot of questions based on self joins and non equi joins and recursive cte based on which I failed. First I took lot of time and interviewer did not have enough patience. I was wrong. However , please do make videos on this

[@ankitbansal6]

Check this out kzbin.info/www/bejne/m6jQdnuHiLaMiK8

[@muthukumarmadasamy3918]

Is these way can we solve the promblem?Ankit sir select *, case when id =1 then (select value from input where id=1)+(select value from input where id=4) when id =2 then (select value from input where id=2)+(select value from input where id=1) when id =3 then (select value from input where id=3)-(select value from input where id=2) when id =4 then (select value from input where id=4)-(select value from input where id=1) end as new_value from input

[@sameermandaogade9890]

not a effecient way since it involve writing case expression multiple times and also it makes code more static..what if id =5 is introduced in the input data

[@Datapassenger_prashant]

Just because I am solving problem statements from your channel, so it looked quite easy, other wise its quite difficult. Acha Sir, can you solve this problem using Declare and Set , Using Dynamic SQL (with EXEC)

[@srinivasaraokothapalli7725]

select a.* ,case when substring(a.formula,2,1)='+' then b.value +c.value when substring(a.formula,2,1)='-' then b.value -c.value end from input a join input b on b.id=left(a.formula,1) join input c on c.id=right(a.formula,1)

[@macx8360]

select a.id,a.lefty,a.formula,a.righty,a.operation,a.value_1,b.value_2, case when a.operation='+' then a.value_1 + b.value_2 else a.value_1 - b.value_2 end from ( select id,formula,value as value_1,left(formula,1) as lefty,substring(formula,2,1) as operation,right(formula,1) as righty from input )a inner join (select id,right(formula,1) as righty,value as value_2 from input)b on a.righty=b.id

[@Alexpudow]

with a as (select id, value from input), b as (select row_number() over(order by id) id, (select value from a where id=SUBSTRING(formula,1,1)) id1, (select value from a where id=SUBSTRING(formula,3,1)) id2, SUBSTRING(formula,2,1) ch from input) select i.*, case ch when '+' then id1+id2 when '-' then id1-id2 end result from b join (select * from input) i on b.id=i.id

[@Ashu23200]

waah! kya query thi. mazza aa gaya

[@naveengulati4754]

Hi sir, if we have two or three digit id's then we can't use left/right functions. insert into input values (10,'10+11',10); insert into input values (11,'10+11',34); can you please help me how to solve this solution with these type of id's?

[@Datapassenger_prashant]

in that case we can use search function

[@ujjwalvarshney3188]

select a.* , case when substring(a.formula,2,1) ='+' then b.value + c.value when substring(a.formula,2,1) ='-' then b.value - c.value else null end as new_value from input a left join input b on left(a.formula,1) = b.id left join input c on right(a.formula,1) = c.id ;

[@jitendraurkude6152]

HI SIR I AM LOOKING FOR QUERY TO JOIN TWO TABLES AND FIND SECOND MAX(SALARY) CAN YOU PLEASE HELP ME OUT ????

[@ahra1357]

Hello Ankit Bansal, Can you please explain the login.

[@dikshantchaudhary3014]

Hi Ankit ,I love your content and the way you teach complex sql queries. Please make a video for Database table partitioning in SQL Server for handling huge data. Thanks.

[@ankitbansal6]

As soon as possible

[@RaviKumar-kl3vq]

what if id will have number like 11, 12, 125 as well ??

[@ankitbansal6]

Then we need to identify the position of the operator first and then use substring accordingly

[@sreesennu]

Thanks for sharing the ans for this question. What is the keyword for finding the position of sign

[@MistMelodies]

​@@sreesennu We can use Charindex to find position and then use substring according to find particular set of string.

[@Artouple]

@@sreesennu select * from input; with cte2 AS ( select *, (charindex('+', formula) + charindex('-', formula)) as operator_index from input ), cte3 as ( select substr(formula, 1, operator_index - 1) as id1, substr(formula, operator_index, 1) as operator, substr(formula, operator_index + 1, length(formula) - operator_index) as id2 from cte2 ) select b.id, b.formula, b.value , case when a.operator = '+' then b.value + c.value else b.value - c.value end as output from cte3 as A left join input as b on a.id1 = b.id inner join input as C on a.id2 = c.id; See, if this helps :)

[@atharwaborkar6778]

Please Also provide schema as well. It is not their in the description.

[@jhonsen9842]

Why is d1_value needed if the value is already there and both are the same? Can we reduce one inner join using this? This is exclusive to this query.

[@kunalparihar8665]

Nice video Ankit I have come across questions where it is asked to ungroup the data Can you please make some videos regarding those concepts

[@ankitbansal6]

Can to explain with an example

[@kunalparihar8665]

@@ankitbansal6 m.kzbin.info/www/bejne/rnu5iH9tmrKcqJI Ankit like this one

[@sarvesht7299]

Hi Ankit, i have a doubt here. Why are we joining the input tables twice ? Cant we join one time and use AND condition instead? Also why shd we not use the ID column from CTE itself for joining? - pls help me to understand Thanks

[@SuperMohit95]

WITH cte AS( SELECT *, CAST(SUBSTR(formula,1,1) AS number) AS left, CAST(SUBSTR(formula,-2,2) AS number) AS right FROM input ) , final AS( SELECT *, b.value AS b_val, CASE WHEN a.right < 0 THEN -1.0*b.value ELSE b.value END AS right_value FROM cte a LEFT JOIN cte b ON ABS(a.right) = b.id ) SELECT id, formula, value, value + CAST(right_value AS number) AS new_value FROM final ; I think this solution is very similar to yours. Honestly both are q=equally good.

[@makarsh29]

honestly very intresting questions

[@_Samridhijain_]

With base_table as ( select id, Value , substring(formula,1,1) as row_1, substring(formula,2,1) as operatorr, substring(formula,3,1) as row_2 from input_ank ) select b.id as b, b.value, a.operatorr, c.value, CASE a.operatorr WHEN '+' THEN b.value + c.value WHEN '-' THEN b.value - c.value END AS result from base_table b inner join base_table a on b.id = a.row_1 left join input_ank c on c.id = a.row_2

[@insidehead]

why we can't use d1_value instead of ip1.value in CASE stmt?

[@parmoddhiman678]

with cte as (SELECT * ,left(formula,1) as a1,right(formula,1) as a2,substring(formula,2,1) as o FROM input) select c.id,c.formula,c.o,c.value,ip1.value as a1_value,ip2.value as a2_value, case when c.o = '+' then ip1.value+ip2.value else ip1.value-ip2.value end as new_value from cte c join input ip1 on c.a1 = ip1.id join input ip2 on c.a2 = ip2.id order by id

[@subhojitchatterjee6312]

My approach before watching the solution: WITH CTE AS (SELECT *,CAST(LEFT(FORMULA,1) AS SIGNED) AS S1,CAST(RIGHT(FORMULA,1) AS SIGNED) AS S2,MID(FORMULA,2,1) AS OPERATOR FROM INPUT), CTE_2 AS( SELECT C.*,CAST(CONCAT(OPERATOR,I.VALUE) AS SIGNED) AS V2 FROM INPUT I JOIN CTE C ON I.ID=C.S2 ) SELECT ID,FORMULA,VALUE,VALUE+V2 AS NEW_VALUE FROM CTE_2;

[@tarigopulaayyappa]

@ankit bansal, I got offer in ness technologies on datastage...May i know how the company is? Can I join in ness? I have 8.7 years of exp .. Any idea which clients they have on datastage and how the work is?

[@ankitbansal6]

I worked for ness in 2015 for CTC client (Canadian tier corporation). It was overall good. They use datastage for etl.

[@tandaibhanukiran]

create table input_formula (id int,formula string, value int) insert into input_formula values (1,"1+4",10),(2,"2+1",5),(3,"3-2",40),(4,"4-1",20)

[@014_nasreenparween5]

I am new in SQL. I only know basics. But I am learning.

[@ankitbansal6]

Keep watching 😊

[@sreejitchakraborty6575]

My Sol: with cte as( select id, value from input ), extracted as( select formula, substr( formula, 1, length(formula)- pos ) as first_val, substr(formula, pos + 1) as second_val, substr(formula, pos, 1) as sign from ( select formula, case when instr(formula, '+')= 0 then instr(formula, '-') else instr(formula, '+') end as pos from input ) ) select i.id, f.formula, c1_real + c2_real from ( select q1.*, case when sign = '-' then - c1.value else c1.value end as c2_real from ( select e.*, c.value as c1_real from extracted e inner join cte c on(e.first_val = c.id) ) q1 inner join cte c1 on(q1.second_val = c1.id) ) f inner join input i on i.formula = f.formula

[@sreejitchakraborty6575]

Also handled 2 digit ids.

[@shwetasaini6892]

-- Infosys problem based on formula calculate the numbers -- select * from input with cte1 as( select *, LEFT(formula, 1) AS first_number, SUBSTRING(formula, 2, 1) AS operator, RIGHT(formula, 1) AS second_number from input), cte2 as ( select a.*, b.value as second_value from cte1 a join input b on a.second_number = b.id ) select id, formula, value, case when operator = '+' then (value+second_value) when operator = '-' then (value-second_value) when operator = '*' then (value*second_value) when operator = '/' then (value/second_value) else null end as new_value from cte2

[@maheboobpatel573]

Hello sir in your course of 2k rupees will u be giving live sessions or just recorded lecture

[@ankitbansal6]

Recorded

[@vandanaK-mh9zo]

with cte as( select id, value, formula, left(formula, 1) as key1, substr(formula, 2, 1) as opr, right(formula, 1) as key2 From input) select i1.*, case when opr = '+' then i1.value+i2.value when opr='-' then i1.value-i2.value end as new_value from cte c inner join input I1 on c.key1 = i1.id inner join input i2 on c.key2 = i2.id ;

[@Ghost_Rider_Asphalt_8]

with CTE as ( select *, LEFT(expression,1) as A,RIGHT(expression,1) as B, SUBSTRING(expression,2,1) as F from formula ), CTE3 as ( select CTE.*, CTE2.Result as Num from CTE inner join formula as CTE2 on CTE.B = CTE2.ID ) select *, Case when F = '+' then Result + Num else Result-Num end as Final_Result from CTE3

[@AppaniMadhavi]

Interesting one😊

[@sureshboya8957]

wonder explanation sir

[@ankitbansal6]

Keep watching

[@sridharanjb2928]

In formula if we wont have id numbers say 1+2,2+3,3-1,3-2 how to solve this

[@PSP2711]

Using row_number() window function

[@mohammedriyaz-g4s]

with cte as (select *,left(formula,1) as f1 ,right(formula,1) as f2 ,substring(formula ,2,1) as o from input), cte2 as (select i1.id,i1.formula,i1.value as f1_value ,c.o,i2.value as f2_value from input i1 inner join cte c on i1.id=c.f1 inner join input i2 on i2.id=c.f2) select *,case when o='+' then f1_value+f2_value else f1_value-f2_value end as correct_value from cte2

[@playwithvihaan7601]

Nice video..What are the skills required to become a Data Engineer?

[@ankitbansal6]

SQL python minimum

[@vinodbhul8899]

with cte as (select id,value,formula, case when length(replace(formula,'+',''))!=length(formula) then substring_index(formula,'+',1) else substring_index(formula,'-',1) end as value1, case when length(replace(formula,'+',''))!=length(formula) then substring_index(formula,'+',-1) else substring_index(formula,'-',-1) end as value2, case when length(replace(formula,'+',''))!=length(formula) then '+' else '-' end as operator from input) select c1.id,c1.value,c1.formula, case when c1.operator='+' then max(c2.value)+min(c2.value) else max(c2.value)-min(c2.value) end as derived_value from cte c1 inner join cte c2 on c1.value1 = c2.id or c1.value2 = c2.id group by c1.id,c1.formula,c1.value,c1.operator order by c1.id;

[@Yaseen-ec4ul]

Thnx

[@suryakanthbhullha7425]

what happen if formula has 10+11 (left and right wont work here)

[@ankitbansal6]

Then we need to first find the location of the operator and then accordingly use sub string

[@vlogsofsiriii]

with input as ( select 10 as id,'10+130' as formula,10 as value union select 11 as id,'11+10' as formula,5 as value union select 12 as id,'12-11' as formula,40 as value union select 130 as id,'130-10' as formula,20 as value ), CTE AS ( select * , LEFT(formula,PATINDEX('%[+,-]%',formula)-1) as l , SUBSTRING(formula ,PATINDEX('%[+,-]%',formula)+1 , len(formula) - PATINDEX('%[+,-]%',formula)) as r, SUBSTRING(formula, PATINDEX('%[+,-]%',formula) ,1) as sign from input ) SELECT cte.*,l.value as l_val ,r.value as r_val , case when sign ='+' then l.value + r.value when sign='-' then l.value - r.value end as nw_val FROM CTE JOIN input as l ON CTE.L = l.ID JOIN INPUT as r ON CTE.R= r.ID

[@KulwinderKaur-kd3bk]

👏🏻👏🏻👏🏻👏🏻👏🏻👏🏻👏🏻

[@SACHINKUMAR-px8kq]

Thankyou Sir

[@radhikamaheshwari4835]

Thanks for sharing. My solution : with sep as ( select substring(formula, 1, 1)::integer as a, substring(formula, 3, 1)::integer as b, concat(substring(formula, 2, 1), '1') :: integer as o, row_number() over(order by id) as rn from input ), vals as ( select rn, case when a = 1 then 10 when a = 2 then 5 when a = 3 then 40 when a = 4 then 20 end as aup, case when b = 1 then 10 when b = 2 then 5 when b = 3 then 40 when b = 4 then 20 end as bup from sep ) select aup, bup, aup + bup * o from vals v join sep s on v.rn = s.rn

[@monasanthosh9208]

MYSQL Solutions With CTE as (Select A.id,A.formula,A.Value as Value ,I.Value as Cal1,A.Cal,II.Value as Cal2 from (Select *,substring(formula,2,1) as Cal,Left(Formula,1) as Ast_Val,Right(Formula,1) as Bnd_Val from Input)A Join Input I on A.Ast_Val=I.id join input II on A.Bnd_Val=II.id order by id) Select id,Formula,Value,Case When Cal="+" then (Cal1+Cal2) else (Cal1-Cal2) end as New_Value from CTE;

[@sagarsaini5447]

so enjoy full puzzle

[@AdityaKumar-BG]

with cte as (select id , formula, case when formula like '%+%' then substr(formula,1,REGEXP_INSTR(formula,'[+]')-1) when formula like '%-%' then substr(formula,1,REGEXP_INSTR(formula,'-')-1) else null end as first_operand, case when formula like '%+%' then '+' when formula like '%-%' then '-' else null end as operation, case when formula like '%+%' then substr(formula,REGEXP_INSTR(formula,'[+]')+1,len(formula)) when formula like '%-%' then substr(formula,REGEXP_INSTR(formula,'-')+1,len(formula)) else null end as second_operand, value from input) select ilv1.id , ilv1.formula , case when ilv1.operation ='+' then (select ANY_VALUE(ilv2.value) from cte ilv2 where ilv2.id=ilv1.first_operand) + (select ANY_VALUE(ilv2.value) from cte ilv2 where ilv2.id=ilv1.second_operand) when ilv1.operation ='-' then (select ANY_VALUE(ilv2.value) from cte ilv2 where ilv2.id=ilv1.first_operand) - (select ANY_VALUE(ilv2.value) from cte ilv2 where ilv2.id=ilv1.second_operand) else null end as final_result from cte ilv1;

[@prabhatgupta6415]

WITH a AS (SELECT *, LEFT(formula, 1) AS l, RIGHT(formula, 1) AS r, Substring(formula, 2, 1) AS o FROM input), cte AS (SELECT o, b.id AS b_id, b.value AS b_value, c.id AS c_id, c.value AS c_value FROM a JOIN input b ON a.l = b.id JOIN input c ON a.r = c.id ORDER BY 2) SELECT b_value, o, c_value, CASE WHEN o = '+' THEN b_value + c_value ELSE b_value - c_value END AS new_value FROM cte;

[@Pyari_Guddiya]

Courses are EXTREMELY COSTLY . For Students who Dont Have money

[@ankitbansal6]

Send me an email with your student I'd and mention the course that you want . [e-mail]

[@mayur5327]

❤

[@puneetnaik8719]

WITH formula_sep AS (SELECT *, Substr(formula, 2, 1) AS op, Substring_index (formula, '+', 1) AS index1, Substring_index (formula, '+', -1) AS index2 FROM input WHERE formula LIKE "%\+%" UNION SELECT *, Substr(formula, 2, 1) AS op, Substring_index (formula, '-', 1) AS index1, Substring_index (formula, '-', -1) AS index2 FROM input WHERE formula LIKE "%\-%") SELECT a.id, a.formula, a.value, CASE WHEN a.op = '+' THEN a.value + b.value WHEN a.op = '-' THEN a.value - b.value END AS new_value FROM formula_sep a JOIN formula_sep b ON a.index2 = b.id

[@paradoxOP1002]

with cte as ( select *, left(formula,1) as var1, right(formula, 1) as var2, substr(formula, 2, 1) as opr from input ) select cte.id, cte.formula, cte.value, i.value, (case when cte.opr = '+' then cte.value + i.value else cte.value - i.value end ) as new_value from cte inner join input as i on cte.var2 = i.id order by id;

[@suriyas6338]

Hi @Ankit Bansal, My solution :) with cte1 as( select id, formula,LEFT(formula, 1) as left_val, RIGHT(formula, 1) as right_val, SUBSTRING(formula, 2,1) as operation, value from input ) , cte2 as ( select cte1.id, cte1.value, cte1.operation,cte1.formula, il.value as sum1, ir.value as sum2 from cte1 inner join input il on il.id = cte1.left_val inner join input ir on ir.id = cte1.right_val ) select id, formula, value, case when operation = '+' then sum1+sum2 when operation = '-' then sum1-sum2 end as new_value from cte2

[@ankitbansal6]

Thanks for posting 👏

[@HARSHRAJ-wz2rp]

with cte as( select SUBSTRING(formula,1,1) as first_operand,SUBSTRING(formula,2,1) as operator FROM input ), cte1 as( select cte.*,value,ROW_NUMBER()OVER() AS x1 FROM cte JOIN input ON cte.first_operand=input.id ), cte2 as( select SUBSTRING(formula,3,1) as second_operand FROM input ), cte3 as( select cte2.*,ROW_NUMBER()OVER() AS x2 FROM cte2 ),cte4 as( select cte1.first_operand,cte1.operator,cte1.value as value1,cte3.second_operand FROM cte1 JOIN cte3 ON cte1.x1=cte3.x2 ),cte5 as( select cte4.*,input.value as value2 FROM cte4 JOIN input ON cte4.second_operand=input.id ),cte6 as( select cte5.*, case when operator='+' THEN value1+value2 when operator='-' THEN value1-value2 END AS new_value FROM cte5 ) select first_operand as id,CONCAT(first_operand,operator,second_operand) as formula,value1 as value, new_value FROM cte6;

[@parthchamp9252]

create table input ( id int, formula varchar(10), value int ) insert into input values (1,'1+4',10),(2,'2+1',5),(3,'3-2',40),(4,'4-1',20);