Super Interesting SQL Problem | Practice SQL Queries

This video will solve a complex SQL Query shared in my discord server. It's an SQL Problem where we must transform the given list of arbitrary values in a specific format. This can be a potential SQL Interview problem for experienced candidates.
THANK YOU for watching!

[@prathab1111]

The way you’r teaching is very simple and understandable brother. Keep Rocking us🎉

[@tonysun203]

It is really an amazing sql to solve this problem. You are using the window function together with recursion at the same time. Brilliant 🎉

[@Naveenvuppala]

Watching your videos from 2 years. Understood and thanks...

[@GamerShaggy]

create table arbitrary_values (name varchar(500)); insert into arbitrary_values values ('a1, a2, a3, a4, a5, a6, a7, a8, a9, a10, a11, a12, a13, a14, a15, a16, a17, a18, a19, a20, a21');

[@GamerShaggy]

with recursive cte as ( select *, 1 as iter, max(idx) over() as max_idx from cte_values where idx = 1 union select cv.*, (iter + 1) as iter, max(cv.idx) over() as max_idx from cte join cte_values cv on cv.idx between (max_idx + 1) and (max_idx + 1 + iter)), cte_values as ( select x.* from arbitrary_values cross join unnest(string_to_array(name, ', ')) with ordinality x(val, idx)) select iter as group, string_agg(val, ', ') as string_value from cte group by iter order by iter;

[@sravankumar1767]

Very tricky question but you made very easy . Superb explanation 👌 👏 👍

[@swetasuman4498]

Thanks Sir!! Best channel to learn SQL.

[@karunpreetsoni6090]

Amazing Logic...!! Totally appreciate your efforts

[@s.v.dhanalakshmi8771]

Hi TFQ, Thank you so much for all your Sql videos…. You r really great I have watched all your Sql videos and now become a good at Sql also I got a new job with good package all credits goes to you…. Thank you once again you are doing a amazing job

[@RaviTheVlogger]

Well explained. Super clear.

[@KoushikT]

Its pretty difficult to know all these functions in a real life interview considering we are seeing this problem for the first time, wondering who would ask such a question

[@Naveenvuppala]

Correct

[@SanjayKumar-sr5ft]

Exactly This video is good for knowing that such things do exist 😂

[@observer698]

I think setting the bar so high is always good, then when simple questions are asked we can be very confident solving them

[@prasanth5046]

Really a great explanation.. but i was seeing this type of problem first time.. I don't think it might have asked in interview.. wondering who would have asked this type of question.... Uff

[@kamalakant05]

Awesome work, you are just amazing. May Allah bless you.

[@anujshrigiriwar2901]

Super interesting !! Thanks for educating us.

[@bhavitavyashrivastava8600]

awesome explanation

[@Confidential007.]

In SSMS - select Concat('A',Value)Ser,value into #ttt from generate_series(1,21) ;with cte as (select *,1 Iter,max(value)over()M from #ttt where value =1 union all select b.*,Iter+1,max(b.value)over() from cte A join #ttt b on b.value between a.M+1 and a.M+1 +Iter and a.value>=a.M ) select Iter,STRING_AGG(ser,',') as Stri from cte group by Iter order by Iter

[@sztap]

Love this, a masterpiece.

[@malleswarasingam3427]

Hi Toufiq, can you please plan a series where you explain regarding reading the json file through SQL. Thanx in advance

[@Alexpudow]

Ms sql solve with rec as ( select 'a' a, 1 b union all select 'a' a, b+1 from rec where b+1

[@winstongraves8321]

Great vid

[@btemghare]

Thank you for sharing

[@bhavitavyashrivastava8600]

Why are the last two rows not coming as it should? Please clarify WITH cte_values AS ( SELECT x.val, (ROW_NUMBER() OVER ()) - 1 AS row_num FROM arbitrary_values CROSS JOIN UNNEST(string_to_array(name, ', ')) WITH ORDINALITY AS x(val, idx) ), grouped_values AS ( SELECT val, FLOOR((SQRT(8 * (row_num) + 1) - 1) / 2) AS grp Formula to calculate the group number FROM cte_values )

[@mahi_sz]

loved it vro

[@tteejjj]

Real time use hai iss ka ??? But useful for logical problem 👍🏻

[@mystery-v8y]

never seen join on between...whatta heil

[@observer698]

same !!! he is like playing magic with SQL! :D

[@rohitsethi5696]

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

[@sunilrao6090]

sir, how to do it in mysql, especially string to array

[@BHUGATHABHARGAV]

IN Which Database Your Doing Sir ?

[@iswillia123]

I did it this way in Oracle. with cte as ( select level lev from arbitrary_values connect by level

[@SaurabhSingh-kr9db]

are you going to launch SQL course ..Please share

[@chetanrajput6081]

Python : Welcome to the team , Sql.

[@sagargaud8460]

What is CTE Why its getting difficult for me to understand i complete position of mysql

[@tonysun203]

CTE is common table expression. Simply speaking is using ‘with’ clause. This is extremely useful of you write intermediate or advance SQL.

[@Preeti_kapoorpura]

I learn sql plzz make vedio for beginners...

[@Lolfy23]

But this is not working in T-SQL

[@anandreddy9591]

Sir, can we do the the same sql server,if yes wt is the function used

[@satyajitbiswal6162]

create table arbitrary_values (name varchar(500)); insert into arbitrary_values values ('a1,a2,a3,a4,a5,a6,a7,a8,a9,a10,a11,a12,a13,a14,a15,a16,a17,a18,a19,a20,a21'); with cte as (select value as k from arbitrary_values cross apply string_split(name,',')), cte2 as (select k,1 as cnt,1 as num from cte where k='a1' union all select b.k,case when a.num

[@atulsingh647]

Hi @techTFQ Could you please solve below query? item price Quantity pencil 200 20 book 150 3 pen 150 3 Yo have 300$ only, below s the output: price quantity 300 22

[@rohit_vora]

in postgresql: with unitprice as (select *, round(price*1.0/quantity) unitprice from items), summingup as (select * ,row_number() over() rn from (select item, (quantity/ quantity) qnt, unitprice , generate_series(1, quantity) from unitprice)) select rn qnt, runningsum price from (select rn, sum(unitprice) over(order by rn) runningsum from summingup) where runningsum = 300

[@djsahu98]

Can this be solved in MS SQL server?

[@satyajitbiswal6162]

create table arbitrary_values (name varchar(500)); insert into arbitrary_values values ('a1,a2,a3,a4,a5,a6,a7,a8,a9,a10,a11,a12,a13,a14,a15,a16,a17,a18,a19,a20,a21'); with cte as (select value as k from arbitrary_values cross apply string_split(name,',')), cte2 as (select k,1 as cnt,1 as num from cte where k='a1' union all select b.k,case when a.num

[@Rocking_Angel]

yes

[@vishnugottipati9373]

Ms sql server plz

[@siddhant7953]

Input:1234567890 Output: ****56**** Please solve this by SQL

[@funhelmet2102]

Group_concat()

[@sajidbhati4946]

Here is my logic instead of printing row number like this {1,2,3,4,5..} i printed them {1,2,2,3,3,3,4,4,4,4.....} and then grouped them --> with cte as ( SELECT FLOOR((SQRT(8*(row_number()over() -1) + 1) - 1) / 2) + 1 AS group_num, name FROM ( SELECT unnest(string_to_array(name, ',')) AS name FROM arbitrary_values ) AS split_names) select group_num,string_agg(name,' , ') from cte group by group_num order by group_num;

[@sravankumar1767]

WITH NumberedIDs AS ( SELECT id, ROW_NUMBER() OVER (ORDER BY value ) AS row_num FROM your table ), GroupedIDs AS ( SELECT id, CASE WHEN row_num % 3 = 1 THEN row_num WHEN row_num % 3 = 2 THEN row_num - 1 WHEN row_num % 3 = 0 THEN row_num - 2 END AS group_id FROM NumberedIDs ) SELECT group_id, STRING_AGG(value, ', ') WITHIN Group (order by value) as Value FROM GroupedIDs GROUP BY group_id

[@satyajitbiswal6162]

this will not give correct result

[@sravankumar1767]

Can you please answer this question in MS sql server

[@Rocking_Angel]

@@sravankumar1767 yup

[@tolulopeesho852]

Hello Thoufiq, Thank you so much for your videos. Please I tried this in the SQL server but my recursion did not terminate. Here is the query below: With AData as (Select Row_number() Over (Order By (select 0)) as RwNum, Value From ArbitraryData Cross apply String_Split(items, ',')), ArrData (Value, n, RwNum, MaxR) as (Select Value, 1 as n, RwNum, Max(RwNum) Over() as MaxR from AData where RwNum = 1 Union all Select Ad.Value, (n+1) as n, Ad.RwNUM, Max(Ad.RwNum) Over() as MaxR From ArrData Join AData AD ON Ad.RwNum between MaxR+1 and MaxR+1+n) Select * From ArrData Thank you!

[@viveks288]

Hi sir, the question asked for Accolite company please give me the answer sir input: tab_abc-----table name order entity ---- column xyz 5 -- values pqr 7 -- values write a insert statement for entity value times output: tab_pzn -----table name order ---- column xyz -- values xyz . xyz . xyz xyz . pqr . pqr . pqr pqr pqr pqr -- values