Super Interesting SQL Problem | Practice SQL Queries
Vložit
- čas přidán 2. 06. 2024
- 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!
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 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;
Thanks Sir!! Best channel to learn SQL.
Amazing Logic...!! Totally appreciate your efforts
Super interesting !! Thanks for educating us.
Watching your videos from 2 years. Understood and thanks...
Very tricky question but you made very easy . Superb explanation 👌 👏 👍
Awesome work, you are just amazing. May Allah bless you.
Well explained. Super clear.
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
Love this, a masterpiece.
Great vid
Thank you for sharing
I did it this way in Oracle.
with cte as (
select level lev from arbitrary_values
connect by level
loved it vro
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
Correct
Exactly
This video is good for knowing that such things do exist 😂
Ms sql solve
with rec as (
select 'a' a, 1 b
union all
select 'a' a, b+1
from rec
where b+1
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;
I learn sql plzz make vedio for beginners...
Hi Toufiq, can you please plan a series where you explain regarding reading the json file through SQL. Thanx in advance
never seen join on between...whatta heil
Real time use hai iss ka ??? But useful for logical problem 👍🏻
sir, how to do it in mysql, especially string to array
are you going to launch SQL course ..Please share
Python : Welcome to the team , Sql.
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
this will not give correct result
Can you please answer this question in MS sql server
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
Sir, can we do the the same sql server,if yes wt is the function used
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
But this is not working in T-SQL
Input:1234567890
Output: ****56****
Please solve this by SQL
Ms sql server plz
Can this be solved in MS SQL server?
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
Group_concat()
Sir please help me i have not cracked interview
Can you share the question they asked
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!
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