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

[@protapnandi9729]

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;

[@sameerkumardash5795]

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

[@perumala4167]

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

[@hanishadua474]

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.

[@ShivamGupta_15]

Let's say 'x' should be printed 1 time. After the first iteration is printed (base query) and we reach the recursive query, the cte.total_count is still 1 and it will again print x because where condition has cte.total_count>0. Hence we get x printed twice which is wrong. Here we need to keep in mind that before coming to the recursive query we already have an iteration printed. P.S, writing this so that I don't forget when I'll come back to this video.

[@hanishadua474]

@@ShivamGupta_15 Oh okay. Wrapping my head around it. Thank you for replying. Big help :)

[@Santanu.Bhowmick]

Where clause executes first then the select clause. When the count is actually 1, it satisfies the condition of ">0" and it went to select clause which printed 1-1=0.

[@gauravsingh-gn4zz]

@@ShivamGupta_15 or we can use count -2 in second iteration

[@joefromdc]

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

[@sayantabarik4252]

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;

[@techTFQ]

Absolutely right.. that’s a good catch.. join was unnecessary in this case..

[@sayantabarik4252]

@@techTFQ Thanks for the response !!

[@rahulb3993]

Also, the level you have used is it required as I can see it is not of use in the query or may be I'm wrong

[@sayantabarik4252]

@@rahulb3993 That is not required. that is for better understanding how recursive cte works. He clearly mentioned that in the video.

[@deepapanneerselvam4618]

@@rahulb3993 That is for the people understanding

[@My_buddy_94]

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

[@kamalrock1]

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

[@varunas9784]

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

[@hilarylomotey7051]

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

[@techTFQ]

Thank you Hilary 🙏🏼

[@shubhamagrawal7068]

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

[@fahadmahmood7150]

Superb Explanation 👌

[@techTFQ]

Thank you ☺️

[@avi8016]

Great video as usual 💯

[@techTFQ]

Thank you buddy

[@venkatkrishnan2172]

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 While (@cnt

[@swapnilsolanki8595]

I remeber this question being posted in discord channel👍

[@techTFQ]

Is it.. I saw it in LinkedIn.. but a pretty good problem

[@chintanmistri7747]

Yupp @Swapnil Solanki, I have put it on discord channel

[@chintanmistri7747]

@@techTFQ Solved this question using almost same approach and posted on your discord channel, All hail to you for teaching the recursive queries in the best possible way, keep doing good work. Cannot solve this query without having foundations clear which you did in your previous videos. Thanks a lot🙌

[@techTFQ]

Thanks Chintan, I realized the join was unnecessary I’m in this solution… if you have a better solution then kindly share so I can add it to my blog so others can benefit

[@raghuveersangashetty4845]

Oracle: select id,item_name from items i cross join lateral (select level from dual connect by level

[@techTFQ]

Nice, thanks for sharing

[@uditkumarpatra5236]

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.

[@techTFQ]

Hopefully I’ll have 100 queries in the next few months in all my sql queries playlist

[@kennethoppong1881]

@@techTFQ I will look forward towards that sir, thank you for helping us out

[@mayurjoshi1452]

Sir do you have SQL database for practice, I'm practicing SQL do something for friend. 💐🙏

[@Tarasankarpaul1]

You are the best 💝

[@techTFQ]

Thank you buddy

[@fathimafarahna2633]

Awesome as always👌🏻

[@techTFQ]

Thank you 🙏🏼

[@ShubhamGautam-k1k]

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)

[@brianligat9493]

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.

[@venkateshdeekonda8665]

I have used for loop within another for in Oracle DB. One to select all items. Another to loop within the item count number.

[@techTFQ]

That’s works too

[@alagarrajkalaivanan2644]

Can you please share the code?

[@venkateshdeekonda8665]

@@alagarrajkalaivanan2644 /*Ungroup the data*/ declare n number; u number; cursor c is select * from travel; begin for i in c loop n := i.item_count; u := n/n; for j in 1..n loop dbms_output.put_line(i.id||','||i.item||','||u); end loop; end loop; end;

[@dantushankar4470]

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

[@subodhthore6454]

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

[@sameerkumardash5795]

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

[@souhailmainsi7800]

Why didn't you use the generate_series function in this case ?

[@udaykumarakavachanam7915]

I solved the problem using plpgfunction ------------------------------ create or replace function count_split() returns table(name varchar,id int) language plpgsql as $$ declare i record; count_1 int =1; begin create temporary table fruits (name_1 varchar(250) ,id_1 int); for i in select * from count_split loop count_1 = 1; while count_1

[@___bala84___]

Hai thoufiq....big fan of ur videos in recent times....learned a lot from ur videos......im working in data domain....im facing a issue like im not getting the difference between mysql and oracle sql....when i work on hackerrank there are some syntax differences between these two....can u pls make a video on difference between these two pls?

[@techTFQ]

Thank you Bala. The difference is these rdbms is not much.. generally difference will be in certain functions.. some functions are supported only in oracle n some other only in MySQL.. let me see if I can come up with a video highlighting such differences

[@___bala84___]

@@techTFQ It ll be really helpful if u do so.....Thanks a lot fr rplying♥️.....hoping to see u with many more subscribers🙂

[@techTFQ]

Thank you so much 🙏🏼

[@NewsbyAkki25]

Hey Hi Thoufiq can u please make video on greatest and least function in Oracle SQL like eg there is this students table and in that columns like name , sub1.sub2.sub3 know i want to know in which subject the particular student has scored highest (not the marks but the subject name should be fetched) BTW You are doing a great job Thank you🙏

[@techTFQ]

Thank you and let me consider this problem

[@shivaroyal9292]

So if you have the source table like below NAME PHYSICS CHEMISTRY MATHS ID SIVA 85 70 95 1 HARI 98 80 79 2 KRISHNA 75 81 92 3 SAMBA 86 98 95 4 Do you want the output like below right NAME SUBJECT SIVA MATHS HARI PHYSICS KRISH MATHS SAMBA CHEMISTRY ---------Try this Query--------- SELECT NAME,SUBJECT FROM (SELECT ID,NAME,'PHYSICS' AS SUBJECT,PHYSICS AS MARKS FROM STUDENTS UNION ALL SELECT ID,NAME,'CHEMISTRY' AS SUBJECT,CHEMISTRY AS MARKS FROM STUDENTS UNION ALL SELECT ID, NAME,'MATHS' AS SUBJECT,MATHS AS MARKS FROM STUDENTS) WHERE (NAME,MARKS) IN (SELECT NAME,MAX(MARKS) FROM( SELECT NAME,'PHYSICS' AS SUBJECT,PHYSICS AS MARKS FROM STUDENTS UNION ALL SELECT NAME,'CHEMISTRY' AS SUBJECT,CHEMISTRY AS MARKS FROM STUDENTS UNION ALL SELECT NAME,'MATHS' AS SUBJECT,MATHS AS MARKS FROM STUDENTS) GROUP BY NAME) ORDER BY ID;

[@ManiS-pr8ui]

Hi Toufique, Can you make another video on CTE for selecting level 2, level 3 managers

[@jeffreyelvir8188]

Hey, new subscriber here, I just started my journey of learning SQL. First of all, amazing content! I like the way to explain and make sure that it is very clear. regarding this example, this is my solution: WITH RECURSIVE travel AS ( SELECT * FROM travel_items UNION SELECT id, item_name, total_count - 1 AS total_count FROM travel WHERE total_count > 1 ) SELECT id,item_name FROM travel ORDER BY item_name DESC; and it gave me the exact same result, is this correct? or am I missing something?

[@iqbalmu]

Sir, can you now do the opposite, group given output data?

[@techTFQ]

That should be pretty straight forward right

[@iqbalmu]

@@techTFQ if you could post another video it would be greatly appreciated.

[@techTFQ]

I’ll make other videos for this concept bro

[@sheikhsheharyar6178]

Hi Toufiq please make videos on Cosmos DB

[@ameynaik1413]

Use groupby clause over item name, and count fun

[@sangrampujari9205]

please let me know if we can your materials in Udemy..

[@ravurisudheer176]

How to reach out ur blog

[@parveen8122]

flow of code is not clear my solution with recursive cte as( select 1 as n ,id, item_name ,total_count from travel_items union all select n+1,id, item_name ,total_count from cte where n< total_count ) select id,item_name, total_count from cte

[@vijay.s-ll1yq]

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

[@sureshkct]

What is the tool name you are using writing query ?

[@theshanerap]

pdAdmin

[@vijaymulimath6519]

Select I'd,item_name,count(*) Total_count from table group by I'd,item_name Order by I'd.............is it right?? Iam using Oracle

[@rakeshd7131]

in script , you used union all insted of union. please make it correct.

[@techTFQ]

Both works… does not matter for this problem

[@vishalsonawane.8905]

Done

[@yashsaxena7754]

Sharing an alternative solution(without join) if it helps someone. with recursive cte as (select id,item_name,1 as cnt from travel_items union select id,item_name,cnt+1 from cte where cnt

[@vishalsonawane.8905]

Thnx it's working

[@viswa6126]

WITH RECURSIVECTE AS( SELECT id,item_name,total_count, 1 AS Iter FROM travel_items UNION ALL SELECT id,item_name,total_count, Iter+1 FROM RECURSIVECTE WHERE Iter < total_count ) SELECT id, item_name FROM RECURSIVECTE ORDER BY id

[@Shivani-yk7tw]

here is my solution with cte as ( select id,item_name,total_count, 1 as rn from travel_items union all select id,item_name,total_count, rn+1 from cte where rn+1

[@bhavanapatel9368]

I have already sent you a mail in which a problem I faced.

[@janjanamsaibindu2840]

Bro I have doubt .. please help me out for SQL query. questions is who renewal the policy more than one month gap Name. Effectivedate. Terminatdate Sekar 01-01-2019. 01-10-2020 Sekar. 01-12-2020. 31-12-9999 Rajesh. 01-01-2019. 01-10-2021 Rajesh. 01-11-2021. 01-10-2022 Satish. 01-01-2019. 01-10-2020 Sravan. 01-01-2019. 01-10-2020 Sravan. 01-12-2020. 31-12-9999 Consider terminatdate with Max is active & date format (dd-mm-yyyy)

[@gokulprasandh1001]

We need oracle jobs

[@cristinasayavedra5721]

Hello Toufiq! How are you? I am a data analyst from Argentina, Im bilingual ando have an accountant background, please, please Toufiq, can you help me find a job please, can I Give you my resume? Please

[@Kindness-ys8gl]

fix your website please..

[@user-lx1ck9bn2j]

u are using same technique in every problem so ur every soln is too complicated take look simple sol with cte as (select id,item_name,total_count,1 as l from travel_items union all select id,item_name,total_count,l+1 as l from cte where cte.l