A correction in the solution using SQL Server.. I missed to add the length function in the video. You can find the complete solution in my blog. My apologize if this confuses anyone.
in Spark SQL:- %sql with T as(select id , explode(split(items , ','))as items from B), R as(select id, items, len(items) as L from T) select id , array_join(collect_list(L), ',') as item from R group by id in SQL-SERVER:- select id, string_agg(len(items),',') as length from B cross APPLY string_split(items,',') group by id;
select id, listagg(length_value, ',') as length_value from ( select id, length(value) as length_value from ( select * from item, lateral split_to_table(item.items, ',') order by seq, index ) ) group by id order by id;
Great thanks sir, it's like revising every topic per day and practising.. Sir how much python should we know for the data science field. Also python for development and python for data fields is different so how much to study. Can I solve questions from leetcode and hackerank. So how to decide what level of questions are there for the data science field on leetcode and hackerank to be asked for a candidate for data science field
Thanks, here i reframed your code with recursive cte as (select *, 1 as digit, substring_index(items, ',', 1) as vals from item union all select id, items, digit+1, substring_index(substring_index(items, ',', digit+1),',',-1) as vals from cte where digit
SQL SERVER SOLUTION : GO with cte as( select ID,items,value,CAST(LEN(value) AS varchar(max)) AS L from item CROSS APPLY (select value from string_split(items,',')) x ) select ID,STRING_AGG(L,',') as LENGTHS from cte group by ID
Hi Sir, (SQL) an interviewer asked me to calculate number of strings. For eg. Input = ‘AATTTJPPPKKBRR’, Output = ‘2A3T1J3P2K1BRR’. Please tell me the solution for this.
WITH unnest_cte AS ( SELECT id, LENGTH(UNNEST(STRING_TO_ARRAY(items, ','))) FROM item ) SELECT id, STRING_AGG(length::VARCHAR, ',') FROM unnest_cte GROUP BY id ORDER BY id;
In MS SQL select id, string_agg(len(value),',') lenn from (select id, value from item cross apply string_split(items, ',') ) a where len(value) > 0 group by id
select id,string_agg(cast(len as varchar),',') as lengths from ( select id ,length(unnest(string_to_array(items, ','))) as len from item ) x group by 1 order by 1
WITH RECURSIVE split_items AS ( -- Base case: extract the first part of the string SELECT id, TRIM(SUBSTRING_INDEX(items, ',', 1)) AS item, CASE WHEN INSTR(items, ',') = 0 THEN '' ELSE SUBSTRING(items, INSTR(items, ',') + 1) END AS rest FROM item
UNION ALL
-- Recursive case: continue extracting parts SELECT id, TRIM(SUBSTRING_INDEX(rest, ',', 1)) AS item, CASE WHEN INSTR(rest, ',') = 0 THEN '' ELSE SUBSTRING(rest, INSTR(rest, ',') + 1) END AS rest FROM split_items WHERE rest '' ) SELECT id, GROUP_CONCAT(LENGTH(item) ORDER BY ID SEPARATOR ', ') AS item_lengths FROM split_items WHERE item '' GROUP BY id ORDER BY id;
I have tried this in snowflake and in oracle. Solution is given below - With Base_Cte as ( Select ID, Split(Items, ',') Itm, ARRAY_SIZE(Itm) Itm_Cnt From Item ), Cte As ( Select Id, Itm, Itm_Cnt From Base_Cte Union All Select Cte.Id, Cte.Itm, Cte.Itm_Cnt-1 From Cte Where Cte.Itm_Cnt-1 >=1 )Select ID, ListAgg(Length(Get(ITM, ITM_CNT-1)),',') Within Group (Order By ITM_CNT Asc) Lenths from Cte Group By ID Order By ID Asc;
with cte as (select id, value from item cross apply string_split(items,',')) , cte2 as (select id, len(value) as lengths from cte ) select id, string_agg(lengths,',') as lengths from cte2 group by id
There is one mistake you have made in Microsoft server you return the same table like input you should use the length function to calculate the length of individuals. Is that correct or not please reply to this comment? I love watching this type of query to learn more about SQL. Thank you so much for creating this beautiful 30 day challenge. Keep teaching me and others.
i just started learning Normalisation... 1NF says each cell should contain only one value, but here you showed multiple values in same field.. then whats use of 1NF... someone please explain
It is one whole value in a cell. Here it is being split as stated in the problem statement. To illustrate further, consider it has a house and the house has many rooms. This will hold good only for this example not for all.
Sir in sql database you didnt solve the problem please see the video you will know what I mean rather than having count of each row you again get the question @techTFQ and thanks for these questions have learned a lot from you
if any1 looking for MYSQL implementation - with recursive cte as (Select id, items, comma, substring_index(substring_index(items,',',test),',',-1) as field, test from (select id, items, length(items) - length(replace(items,',',''))+1 as comma, 1 as test from item) x union all select id , items, comma-1,substring_index(substring_index(items,',',test +1),',',-1) as field, test +1 from cte where test
@@sapnasaini851 so length(items)-length(replace(items,",","")) is used to calculate number of comma, we see in this sentence "22,122,1022" there are 2 commas but words are 3 so we need to do number of comma + 1 to get all words, if u don't add 1 then one word will miss out.
My solution on MYSQL with recursive cte as (select id,items, length(items)-length(replace(items,",",""))+1 as cnt from item union select id,items,cnt-1 from cte where cnt >1), cte2 as ( select *, length(substring_index(substring_index(items,",",cnt),",",-1)) as word_length from cte) select id,group_concat(word_length order by cnt) as lengths from cte2 group by id;
@@sapnasaini851 INshort the +1 is used to get the entire string as an input for the 2nd substring_index for detail please look for below explantion Let suppose that we dont add 1 to the length(items)-length(replace(items,",","")) and for simplicity i have taken only id = 1 So for this with recursive cte as (select id,items, length(items)-length(replace(items,",","")) as cnt from item where id =1 union select id,items,cnt-1 from cte where cnt >1) select * from cte The result would be id items cnt 1 22,122,1022 2 1 22,122,1022 1 NOW when we use substring_index(substring_index(items,",",cnt),",",-1) the inner substring_index will run for cnt=2 and cnt =1 for cnt = 2 it will take 2nd instance of "," occurance and for cnt = 1 it will take 1st instance of "," occurance so for 2nd substring_index the input will look like as below id items cnt broken_string 1 22,122,1022 2 22,122 1 22,122,1022 1 22 now when 2nd substring index gets executed for -1 then it will give output as id items cnt broken_string 1 22,122,1022 2 122 1 22,122,1022 1 22 but here we are not getting 1022 thats why we have added 1 so that cnt =3 is also included when the instance count is more than number of delimiters substring_index returns the entire string so for cnt =3 the output will be id items cnt broken_string 1 22,122,1022 2 22,122,1022 this when used by outer substring_index will give 1022 as well
A correction in the solution using SQL Server.. I missed to add the length function in the video. You can find the complete solution in my blog.
My apologize if this confuses anyone.
PySpark 30 Days Challenge of these problems :
th-cam.com/play/PLqGLh1jt697xzk9LCLL_wFPDZi_xa0xR0.html
in Spark SQL:-
%sql
with T as(select id ,
explode(split(items , ','))as items
from B),
R as(select id, items, len(items) as L
from T)
select id ,
array_join(collect_list(L), ',') as item
from R
group by id
in SQL-SERVER:-
select id, string_agg(len(items),',') as length
from B
cross APPLY string_split(items,',')
group by id;
select id, listagg(length_value, ',') as length_value
from (
select id, length(value) as length_value
from (
select *
from item, lateral split_to_table(item.items, ',')
order by seq, index
)
)
group by id
order by id;
Great thanks sir, it's like revising every topic per day and practising..
Sir how much python should we know for the data science field. Also python for development and python for data fields is different so how much to study. Can I solve questions from leetcode and hackerank. So how to decide what level of questions are there for the data science field on leetcode and hackerank to be asked for a candidate for data science field
Solution using mysql (Easy to understand)
with recursive cte as (
select 1 as digits
union all
select digits+1 as digits from cte
where digits
Thanks,
here i reframed your code
with recursive cte as
(select *, 1 as digit, substring_index(items, ',', 1) as vals from item
union all
select id, items, digit+1, substring_index(substring_index(items, ',', digit+1),',',-1) as vals from cte
where digit
SQL SERVER SOLUTION :
GO
with cte
as(
select ID,items,value,CAST(LEN(value) AS varchar(max)) AS L
from item
CROSS APPLY (select value from string_split(items,',')) x
)
select ID,STRING_AGG(L,',') as LENGTHS from cte
group by ID
Hi Sir, (SQL) an interviewer asked me to calculate number of strings.
For eg. Input = ‘AATTTJPPPKKBRR’, Output = ‘2A3T1J3P2K1BRR’.
Please tell me the solution for this.
anyone??
WITH unnest_cte AS (
SELECT
id,
LENGTH(UNNEST(STRING_TO_ARRAY(items, ',')))
FROM
item
)
SELECT
id,
STRING_AGG(length::VARCHAR, ',')
FROM
unnest_cte
GROUP BY
id
ORDER BY
id;
In MS SQL
select id, string_agg(len(value),',') lenn
from
(select id, value
from item
cross apply string_split(items, ',') ) a
where len(value) > 0
group by id
select id,string_agg(cast(len as varchar),',') as lengths from (
select id ,length(unnest(string_to_array(items, ','))) as len
from item
) x group by 1 order by 1
WITH RECURSIVE split_items AS (
-- Base case: extract the first part of the string
SELECT
id,
TRIM(SUBSTRING_INDEX(items, ',', 1)) AS item,
CASE
WHEN INSTR(items, ',') = 0 THEN ''
ELSE SUBSTRING(items, INSTR(items, ',') + 1)
END AS rest
FROM item
UNION ALL
-- Recursive case: continue extracting parts
SELECT
id,
TRIM(SUBSTRING_INDEX(rest, ',', 1)) AS item,
CASE
WHEN INSTR(rest, ',') = 0 THEN ''
ELSE SUBSTRING(rest, INSTR(rest, ',') + 1)
END AS rest
FROM split_items
WHERE rest ''
)
SELECT
id,
GROUP_CONCAT(LENGTH(item) ORDER BY ID SEPARATOR ', ') AS item_lengths
FROM split_items
WHERE item ''
GROUP BY id
ORDER BY id;
I have tried this in snowflake and in oracle. Solution is given below -
With Base_Cte as
(
Select ID, Split(Items, ',') Itm, ARRAY_SIZE(Itm) Itm_Cnt From Item
), Cte As
(
Select Id, Itm, Itm_Cnt From Base_Cte
Union All
Select Cte.Id, Cte.Itm, Cte.Itm_Cnt-1
From Cte
Where Cte.Itm_Cnt-1 >=1
)Select ID, ListAgg(Length(Get(ITM, ITM_CNT-1)),',') Within Group (Order By ITM_CNT Asc) Lenths
from Cte
Group By ID
Order By ID Asc;
with cte as
(select id, value from item
cross apply string_split(items,','))
, cte2 as
(select id, len(value) as lengths
from cte )
select id, string_agg(lengths,',') as lengths
from cte2
group by id
with cte as (select * from item cross apply
string_split(items,','))
select id,string_agg(len(value),',')
from cte
group by id
another good video ...learnt how to use unnest and string_agg functions.......please try to cover stored procedure based queries ..........
where can i find more questions like these for practice
is there any mysql solution without recursive cte pls post
There is one mistake you have made in Microsoft server you return the same table like input you should use the length function to calculate the length of individuals.
Is that correct or not please reply to this comment?
I love watching this type of query to learn more about SQL.
Thank you so much for creating this beautiful 30 day challenge. Keep teaching me and others.
yeah my bad, missed to add length.
if you look at my scripts from blog, it has the complete solution with length function
i just started learning Normalisation... 1NF says each cell should contain only one value, but here you showed multiple values in same field.. then whats use of 1NF... someone please explain
It is one whole value in a cell. Here it is being split as stated in the problem statement.
To illustrate further, consider it has a house and the house has many rooms.
This will hold good only for this example not for all.
Sir in sql database you didnt solve the problem please see the video you will know what I mean rather than having count of each row you again get the question @techTFQ and thanks for these questions have learned a lot from you
yeah my bad, missed to add length.
Thanks for your video, Seems in ms sql statement output return as actual value i/o length of value .if i wrong pls correct me...
yeah my bad, missed to add length.
Thanks for teaching us how to use split function. Today’s video is useful. Thanks again for your work.😊
Your welcome Tony ☺️
if any1 looking for MYSQL implementation - with recursive cte as (Select id, items, comma, substring_index(substring_index(items,',',test),',',-1) as field, test from (select id, items, length(items) - length(replace(items,',',''))+1 as comma, 1 as test from item) x
union all
select id , items, comma-1,substring_index(substring_index(items,',',test +1),',',-1) as field, test +1 from cte
where test
Why are you adding 1 in length(items)-length(replace(items,",",""))+1 ; I am not understanding the logic behind it, please explain.
@@sapnasaini851 so length(items)-length(replace(items,",","")) is used to calculate number of comma, we see in this sentence "22,122,1022" there are 2 commas but words are 3 so we need to do number of comma + 1 to get all words, if u don't add 1 then one word will miss out.
My solution on MYSQL
with recursive cte as
(select id,items, length(items)-length(replace(items,",",""))+1 as cnt from item
union
select id,items,cnt-1 from cte
where cnt >1),
cte2 as (
select *,
length(substring_index(substring_index(items,",",cnt),",",-1)) as word_length
from cte)
select id,group_concat(word_length order by cnt) as lengths
from cte2
group by id;
Why are you adding 1 in length(items)-length(replace(items,",",""))+1 ; I am not understanding the logic behind it, please explain.
@@sapnasaini851
INshort the +1 is used to get the entire string as an input for the 2nd substring_index
for detail please look for below explantion
Let suppose that we dont add 1 to the length(items)-length(replace(items,",",""))
and for simplicity i have taken only id = 1
So for this
with recursive cte as
(select id,items, length(items)-length(replace(items,",","")) as cnt from item where id =1
union
select id,items,cnt-1 from cte
where cnt >1)
select * from cte
The result would be
id items cnt
1 22,122,1022 2
1 22,122,1022 1
NOW when we use
substring_index(substring_index(items,",",cnt),",",-1)
the inner substring_index will run for cnt=2 and cnt =1
for cnt = 2 it will take 2nd instance of "," occurance and for cnt = 1 it will take 1st instance of "," occurance
so for 2nd substring_index the input will look like as below
id items cnt broken_string
1 22,122,1022 2 22,122
1 22,122,1022 1 22
now when 2nd substring index gets executed for -1 then it will give output as
id items cnt broken_string
1 22,122,1022 2 122
1 22,122,1022 1 22
but here we are not getting 1022
thats why we have added 1 so that cnt =3 is also included
when the instance count is more than number of delimiters substring_index returns the entire string
so for cnt =3 the output will be
id items cnt broken_string
1 22,122,1022 2 22,122,1022
this when used by outer substring_index will give 1022 as well
Fantastic
It's not running in background
what do you mean bro?
we are not able to see your today video..the content is not displaying
Now it's working fine thanks
okay 👌