Zepto Product Analyst SQL Interview Question
Вставка
- Опубліковано 17 вер 2024
- In this video we are going to discuss a very interesting sql interview question asked in zepto product analyst interview. We will solve it using multiple method including with and without recursive cte.
Take your SQL skills to SKY : www.namastesql...
script:
create table numbers (n int);
insert into numbers values (1),(2),(3),(4),(5)
insert into numbers values (9)
Zero to hero(Advance) SQL Aggregation:
• All About SQL Aggregat...
Most Asked Join Based Interview Question:
• Most Asked SQL JOIN ba...
Solving 4 Trick SQL problems:
• Solving 4 Tricky SQL P...
Data Analyst Spotify Case Study:
• Data Analyst Spotify C...
Top 10 SQL interview Questions:
• Top 10 SQL interview Q...
Interview Question based on FULL OUTER JOIN:
• SQL Interview Question...
Playlist to master SQL :
• Complex SQL Questions ...
Rank, Dense_Rank and Row_Number:
• RANK, DENSE_RANK, ROW_...
#sql #productanalyst #zepto
Without using a recursive CTE, I was unsure how to approach this, but after watching your video, I found it really insightful. The way you explain things is excellent. Thank you!
Thanks for bringing this on the video..
here's my attempt on SQL server:
=====================================
with series as (select * from generate_series(1, (select MAX(num) from #numbers), 1))
select s1.value
from series s1
cross join series s2
where s2.value
select n2.n from numbers n1
join numbers n2 on n1.n
If input is 1,5 then it won't work
Really like the problem statement it was seems like easy at first but upon watching the video realised that simple cross join will not work.
However, when recursive cte is not allowed than we should not use approach of create table using r_cte. So Last solution orr approach is the only approach I guess we can follow.
Hello Ankit, this is my solution:
with cross_data as (
select
a.int_numbers as seq_numbers, b.int_numbers from_b,
rank() over(partition by a.int_numbers order by b.int_numbers) as rnk
from tbl_numbers a
cross join tbl_numbers b
)
select seq_numbers from
cross_data where rnk
i have tried this solution using connect by clause
with new_id as
(
select level as id from dual
connect by level =k.id and t.id in (select id from case2) order by t.id , k.id ;
if we skip any value this wil give correct result
superb explanation...! in python df = pd.DataFrame({'num': [1, 2, 3, 4, 5,9]})
df_repeated = df.loc[df.index.repeat(df['num'])].reset_index(drop=True)
df_repeated
Hi @aman_mashetty5185, I want to learn Python for DA can you include me as well while practicing, please. It would be more helpful for me to get started
@@saikanth447 firstly get basic understanding of Pandas, numpy and seaborn,matplotlib packages then use stratascratch or leet code for solving easy question and watch youtube videos its all about how would you practice thats all..
This should work:
select n
from numbers
cross apply generate_series(1, n)
Superb explanation Ankit 👌 👏 👍
Awesome
Ankit hi. Thanks a lot. But I think your explanation of recursive CTE is little bit wrong.
1. In the first step we get all rows from numbers, it is 1 to 5.
2. in the second step we get rows from CTE according WHERE statement, it is 2 to 5.
And the important thing is that the step 3 is going to contain rows we just got in step 2.
3. in the third step we again get rows from CTE according WHERE statement, it is 3 to 5.
And so on to step 5
It means in any follow step our table will contain rows from the only previous CTE.
Of course, if I understand Recursive CTE correctly 🙂
Maybe I didn't explain correctly. What I was trying to say is for the anchor element all 5 rows will go then for each row the next iteration will take place and then so on until the where condition is meeting for each row individually.
Mysql Solution: with recursive cte as (
select max(n) as n from numbers
union all
select n - 1 from cte
where n - 1 >= 1
)
select n2.n from numbers as n1
cross join numbers as n2 on n1.n
Happy Teacher's Day.
Thank you! 😃
Hi Ankit , I have had this doubt for many years. What is equi join ? Can inner and outer joins be equi joins ? I appreciate your reply and the clarity you will provide. Thank you
It's the same thing. Equi join means join based on equal to condition
@@ankitbansal6 yes ,but can joins like left and right joins act as equi joins if join condition uses = operator ?
select b.a from xy a cross join xy b
where a.a
If input is 1,5 it won't work
sitr how i can learn recursive cte,that the only thing in sql which find me as difficult now
WITH numbers as (
SELECT top 100 rownumber() over (order by (select null)) as num
FROM sys.objects
)
select i.value
FROM input_table I
JOIN numbers n on n.num
-- Sol 1
;with cte as (
select *,1 as n1 from #numbers
)
select n from cte cross apply
generate_series (n1,n)
order by n
-- Sol 2
; with cte as (
select min(n) as min_n , max(n) as max_n from #numbers
)
, cte2 as (
select value from cte cross apply
generate_series (min_n,max_n)
)
select n from cte2 c
left join #numbers n
on c.value n_counter
)
select n from r_cte order by n
with recursive cte as(
select min(num) as n from numbers
union all
select n+1
from cte
where n=c1.n
group by n1.num
order by n1.num;
my approach
solution for continuous values:
select n2.* from numbers n1 inner join numbers n2 on n1.n
If input is 1,5 it will not work
@@ankitbansal6 Agree With you
@@subhashyadav9262 I mentioned in the solution that 1st query is for continuous values starting from 1 and recursive solution will work everywhere. I didn't go through the video, I saw the question then wrote the query and then posted it .
@@ankitbansal6 I do not understand the usage of hybrid solution if we are using recursive cte then we will go with the recursive cte solution why will we use hybrid. One thing will be common in every solution we need to have continuous number series starting from 1 to the maximum number of the table to achieve this solution.