Super Interesting SQL Problem | Practice SQL Queries
ฝัง
- เผยแพร่เมื่อ 2 มิ.ย. 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;
Amazing Logic...!! Totally appreciate your efforts
Thanks Sir!! Best channel to learn SQL.
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.
Love this, a masterpiece.
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
Thank you for sharing
Great vid
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 😂
never seen join on between...whatta heil
loved it vro
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;
Ms sql solve
with rec as (
select 'a' a, 1 b
union all
select 'a' a, b+1
from rec
where b+1
I learn sql plzz make vedio for beginners...
I did it this way in Oracle.
with cte as (
select level lev from arbitrary_values
connect by level
Hi Toufiq, can you please plan a series where you explain regarding reading the json file through SQL. Thanx in advance
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
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
But this is not working in T-SQL
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
Python : Welcome to the team , Sql.
Ms sql server plz
Input:1234567890
Output: ****56****
Please solve this by 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
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
Sir please help me i have not cracked interview
Can you share the question they asked
Group_concat()
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