Solving a SQL Puzzle | Infosys SQL Interview Question

In this video we are going to solve a vey interesting SQL puzzle kind of problem. This was asked in a Data Analyst Interview.

Master the art of SQL here :

Here is the ready script:
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);

Zero to hero(Advance) SQL Aggregation:

Most Asked Join Based Interview Question:

Solving 4 Trick SQL problems:

Data Analyst Spotify Case Study:

Top 10 SQL interview Questions:

Interview Question based on FULL OUTER JOIN:

Playlist to master SQL :

Rank, Dense_Rank and Row_Number:

#sql #dataengineer

[ankitbansal]

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

[AjaySingh-dqdf]

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

[kartika]

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.👏🙌

[deepeshmatkati]

Your problem solving approach and way of explanation is awesome !

[Reacher]

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!

[srinivasaraokothapalli]

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)

[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

[medleyworld]

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

[vinothkumars]

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

[hairavyadav]

outstanding explanation sir thanks lot for helping students.

[vishwassharma]

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

[macx]

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

[_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

[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;

[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

[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

[ujjwalvarshney]

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 ;

[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)

[parmoddhiman]

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

[shwetasaini]

-- 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