Solving SQL Interview Query | Ungroup given input data | FAANG Interview Query

In this video, we will solve an SQL Interview query which was asked during a interview by a FAANG company. The problem statement is to ungroup the given input data.
We need to write a query to split the given input data into multiple records as per the expected output.

To solve this problem, I will be using recursive SQL query. I will solve it in PostgreSQL but similar solution will also work in all the major RDBMS such as MySQL, MSSQL and Oracle.

All the scripts, dataset and sql queries can be downloaded for free from my website. You will also find solution to this problem in PostgreSQL, Oracle, MySQL and Microsoft SQL Server in my blog.

Timestamp:
00:00 Intro
00:48 Understanding the problem statement
02:22 Deciding the approach to solve this SQL problem
03:21 Syntax and execution process of Recursive SQL Query
05:15 Writing the SQL Query to solve the problem

🔴 My Recommended courses:

🔴 WATCH MORE VIDEOS HERE 👇

✅ SQL Tutorial - Basic concepts:

✅ SQL Tutorial - Intermediate concepts:

✅ SQL Tutorial - Advance concepts:

✅ Practice Solving Basic SQL Queries:

✅ Practice Solving Intermediate SQL Queries:

✅ Practice Solving Complex SQL Queries:

✅ Data Analytics Career guidance:

✅ SQL Course, SQL Training Platform Recommendations:

✅ Python Tutorial:

✅ Git and GitHub Tutorial:

✅ Data Analytics Projects:

THANK YOU,
Thoufiq

[protapnandi]

A shorter solution--

with recursive cte as (
select id, item_name, total_count, 1 as cnt from travel_items
union
select id, item_name, total_count, cnt+1 from cte where cte.total_count>=cnt+1
)
select id, item_name from cte order by 1;

[perumala]

We can use generate_series simplying the query
Select Id, item_name from transaction
Group by Id, item_name, generate_series(1, toatlcount)

[varunas]

Good one Thoufik!
Here's my solution on MS SQL server solved via temp table variables



declare @t table (id int, item varchar (10))
declare @id int
declare @itr int
declare @cnt int
set @cnt = 1
set @id = 1

while @id <= (select MAX(id) from #temp_order)
begin
set @itr = (select integer from #temp_order where id = @id);
while @cnt <= @itr
begin

insert into @t
select id, item from #temp_order where id = @id

set @cnt = @cnt+1;

end;

set @cnt = 1
set @id = @id+1

end;
select * from @t

[hanishadua]

Thank you for this solution, Thoufiq. Very new concept to me. I am still stuck as to why we have rows executed for zero count (when you had cte.total_count>0), technically it should not give any rows which are equal to zero as well.

[shubhamagrawal]

No need to use join in recursive part. It can be done without using join : -

WITH RECURSIVE cte AS(
SELECT id, item_name, total_count, 1 AS cnt FROM travel_items
UNION ALL
SELECT id, item_name, total_count, (cnt+1) AS cnt FROM cte WHERE total_count > cnt
)

SELECT id, item_name FROM cte ORDER BY 1

[joefromdc]

That's a lot. Glad we got power query and Tableau prep 🤣

[sayantabarik]

Hi Thoufiq, I have a question that why there is a need of using join in the recursive query for this solution. We can use the data from cte itself for the next iteration by reducing the total_count-1 and we can put total_count>1 as terminating condition. Please find the below solutions. Please let me know if this will cause any issue.

with cte as
(
select id, item_name, total_count from Travel_items
union all
select id, item_name, total_count-1 from cte where total_count>1
)
select id, item_name from cte order by id;

[My_buddy_]

HI @techTFQ nice explanation i tried in ms sql
;with cta_sample as(
select s.num, s.nameoffruit, s.total from sampel as s
union all
select cs.num, cs.nameoffruit, cs.total-1 from cta_sample as cs
where cs.total>1
)
select num, nameoffruit from cta_sample
order by num

it worked for me

[dantushankar]

short solution : select id, item_name from travel_items t, generate_series(1, t.total_count);

[sameerkumardash]

This is a better solution i guess

with recursive CTE as
(
select id, item_name, total_count
from travel_item
union
select id, item_name, total_count-1
from cte
where total_count>1

)

select id, item_name from cte
order by 1

[ShubhamGautam-kk]

from oracle : this will also work
select id, item_name from (select id, item_name, total_count from table
union
select id, item_name, total_count-1 from table
where total_count>0)

[venkatkrishnan]

Using temp tables,

Declare @cnt int=1;
Declare @cnt1 int=1;
Declare @totalrows int;
Select @totalrows = count(1) from travel_items;
Declare @stg int;
With cte as
(
Select *, row_number() over(order by (select 1)) as rn from travel_items
)
Select * into #temp from cte
begin
Set @cnt1=1
begin
Insert into #result
end
Update
end

[subodhthore]

My solution after knowing it can be solved using Recursion:
with recursive cte as
(
select id, item_name, total_count, 1 as ct from travel_items
union all
select id, item_name, total_count, ct+1 as ct from cte where total_count>=ct+1
)
select id, item_name from cte
order by id;

-- first iteration
with cte as (select id, item_name, total_count, 1 as ct from travel_items),

-- second iteration
cte1 as (select id, item_name, total_count, ct+1 as ct from cte where total_count>=(ct+1)),
-- third iteration
cte2 as (select id, item_name, total_count, ct+1 as ct from cte1 where total_count>=ct+1),
-- fourth iteration
cte3 as (select id, item_name, total_count, ct+1 as ct from cte2 where total_count>=ct+1)
-- fifth iteration
select id, item_name, total_count, ct+1 as ct from cte3 where total_count>=ct+1

[kamalrock]

Thank you Thoufiq for this!🙏🏻
You are such a amazing tutor… 🙌🏻

[raghuveersangashetty]

Oracle:

select id, item_name
from items i cross join lateral (select level from dual connect by level <= i.total_count)
order by id;

[brianligat]

You get the "extras" because of the base query AND the recursive part, so the recursive part just needs to one less than you first thought. Just reducing the total count is enough for the logic - no need for the level apart from for explanation.

[hilarylomotey]

always on point .U r the best have a wonderful day and thanks for this

[uditkumarpatra]

Hey Toufique . You are a great teacher, just asking it would be really nice if you put a top 100 sql questions, that you have come across. If possible can your upload those on your blog.

[vijay.s-llyq]

with cte as
(select id, iteam_name, total_count, 1 as rnk from fruits
union all
select id, iteam_name, total_count, rnk+1 from cte
where rnk+1 <= total_count
)
select id, iteam_name from cte
order by 1

[swapnilsolanki]

I remeber this question being posted in discord channel👍