Swiggy Data Analyst SQL Interview Question and Answer

[@ankitbansal6]

Please like the video for more interesting videos.

[@codjawan]

My easy approach, with cte as ( SELECT *,datepart(day,record_date)-row_number() over (partition by supplier_id, product_id order by record_date) as rows_diff FROM stock WHERE stock_quantity = 2

[@NavneetMishra-n9j]

Appreciate

[@KisaanTuber]

Thanks Ankit for sharing this problem statement. Here is my approach: with t1 as (SELECT * , strftime('%d', record_date) - (row_number() over(partition by supplier_id, product_id order by record_date)) as group_number FROM stock s1 where stock_quantity < 50) select supplier_id, product_id, count(*) as total_days, min(record_date) as first_date from t1 group by supplier_id, product_id, group_number HAVING count(*)>=2;

[@MubarakAli-qs9qq]

Bhai sab hoshiyar log h yha pe , sab se ban gya ye, or ankit sir to genius h

[@bankimdas9517]

The problem is a bit tricky but Ankit sir made it very easy. Thanks a lot sir

[@ankitbansal6]

Most welcome😊

[@mantisbrains]

Thanks, Ankit ! select supplier_id, product_id, full_count as no_of_days,record_date as first_date from (select *,dense_rank() over (partition by daydiff order by record_date)rnk from (select *, count(daydiff) over (partition by daydiff,supplier_id,product_id) as full_count from (select *, extract (day from record_date)::int - rn as daydiff from (select *,row_number() over ()rn from stock where stock_quantity < 50)a)n)p)k where rnk = 1 and full_count >=2 order by product_id, supplier_id;

[@rajveerdhumal3143]

I do not used to comment but you make me feel thanks. such a detail explanation

[@nandan7755]

Detailed solution Ankit bhaiya is really easy to understand.... And helpful Thank you 👍❤❤

[@ankitbansal6]

Most welcome 😊

[@rahulkushwaha6469]

I can relate it with one of your previous videos where you had taught about creating groups. This is next level as always. Thank you ❤

[@SunilKumar_67]

Amazing and Tricky question, that combines multiple concepts in one go

[@ashutoshsharma7119]

Thank you for bringing up this level of Sql Problems. Here's my Solution :- with cte as ( select *, DAY(record_date) - ROW_NUMBER() over(partition by supplier_id, product_id order by record_date) grp from stock where stock_quantity < 50 ) select supplier_id, product_id, count(*) no_of_days, min(record_date) first_date from cte group by supplier_id, product_id, grp having count(*) > 1 order by supplier_id, product_id;

[@vishalmane3139]

good!

[@clickthebaititsnotaclickba7270]

doesnt work when having many months in the data

[@333Stan]

it will cause an error if you have dates coming from previous year.

[@RichaJoshi-bh4pw]

using Gaps and Island approach- with t1 as (select supplier_id, product_id, stock_quantity, record_date, row_number() over(partition by supplier_id, product_id order by record_date) as rnk from stock where stock_quantity=2 order by 1,2,3

[@Actionbros244]

Wow wht a logic and explanation Ankit , its Great ..

[@gospelmoto2833]

Amazing! You got a new sub here. I like your content, they are practically usable in the real business world.

[@ankitbansal6]

Awesome, thank you!

[@dasoumya]

Hello ankit here is my simple solution: with cte as(select *,dateadd(day,-1*row_number()over(partition by supplier_id,product_id order by record_date),record_date) as date_grp from stock where stock_quantity=2;

[@Ashu23200]

this one is mentos Zindagi

[@priyamvadhakp7526]

Impressive solution💯

[@vijaypratap8173]

Nice one 😍 Thanks alot @Ankit Sir apke channel se concept clear ho gye sabhi

[@MubarakAli-qs9qq]

Fir hua kahi selection

[@MovieBuzz-uu8kp]

My answer is straigh forward and easy to understand select * from ( select supplier_id,product_id,count(*) as cnt,min(record_date) as record_date from ( select *, datediff(day,rw,record_date) as id from ( select *, row_number () over (partition by supplier_id,product_id order by record_date) as rw from stock where stock_quantity1

[@akashgoel601]

Great question!! really took a while to get the output :) thanks.. cheers!!

[@UnrealAdi]

with cte as ( select *, dense_rank() over(partition by supplier_id, product_id order by record_date) rn, record_date - (dense_rank() over(partition by supplier_id, product_id order by record_date)) as datediff from stock where stock_quantity 1 ;

[@sumitsaraswat5884]

One Question when you are 14:17 why did not considered row 8 and 9 as a group where supplier id is and product id is 2 and they are on the consecutive days like 8th and 9th? if anyone can help me with this

[@pankajjadhav5485]

with cte as (select *, ROW_NUMBER() over (partition by supplier_id,product_id order by record_date) as rnk from stock where stock_quantity < 50), cte2 as ( SELECT *, DATE_SUB(record_date, interval rnk day) as grp_date from cte) select supplier_id,product_id,count(*) as no_of_stocks,min(record_date) from cte2 group by supplier_id,product_id,grp_date having count(*)>1

[@vikas261196]

I wonder if interviewer is willing to give 15-30 minute to sole such kind of hard problem considering the time pressure it might even take more time to think while performing all this in a live interview, also they won't be providing sql workbench environment, Also for how many year of experience candidate these type of questions are expected ? Can you please share your view @ankit ?

[@zorawarmodi19]

Right

[@saipraneeth1660]

True

[@ezdatalearning]

In that type of questions.. interviewer want your logical thinking..they don't want to let you write the whole script ..

[@srinivasareddybandi983]

my solution using PG with cte as ( select * from stock where stock_quantity=2 order by supplier_id,product_id,rd

[@pandeyRaman]

my sol in pgsql:- with modif as(select *,extract(day from record_date ) - row_number() over(partition by supplier_id,product_id order by supplier_id,product_id,record_date) diff from stock where stock_quantity1 order by supplier_id, product_id,min(record_date)

[@AmanRaj-p8w]

Easy solution: with cte as ( select *, lead(record_date, 1) over (partition by supplier_id, product_id order by record_date) as next_date, lead(record_date, 2) over (partition by supplier_id, product_id order by record_date) as next_to_date from stock where stock_quantity < 50 ) select supplier_id, product_id, record_date as first_date from cte where datediff(next_date, record_date) = 1 and datediff( next_to_date, record_date) = 2

[@VikasChavan-v1c]

with cte as( select *,record_date-(dense_rank() over(partition by supplier_id, product_id order by record_date)) as datediff from stock where stock_quantity=2

[@PraveenSinghRathore-df3td]

Hi Ankit Sir, here is my solution in MYSQL by using a concept which was taught by you only. with less_than_50 as (select *, row_number() over(partition by supplier_id, product_id order by record_date asc) as rnk, date_add(record_date, interval -row_number() over(partition by supplier_id, product_id order by record_date asc) day) as group_date from stock where stock_quantity < 50) select supplier_id, product_id, count(*) as no_of_days, min(record_date) as started_on from less_than_50 group by 1,2,group_date having count(*) >= 2 order by 1,2,4;

[@rohitsharma-mg7hd]

with cte as ( select * ,rank()over(partition by supplier_id,product_id order by record_date rows between unbounded preceding and current row) from stock where stock_quantity=2 order by supplier_id,product_id,cons_date)

[@nidhisingh4973]

Hello Ankit, Below is my approach. WITH grp_date AS ( Select *, dateadd(day- (ROW_NUMBER() OVER (partition by supplier_id, product_id ORDER BY record_date) -1) ),record_date ) AS NewDate from stock where stock_quantity=2

[@bituparnasaikia4347]

with cte as( select *, row_number()over(partition by product_id,supplier_id order by record_date ) as dd from stock where stock_quantity= 2

[@aayushibirla2590]

What an Amazing solution!

[@sahilummat8555]

with cte as ( select *, ROW_NUMBER()over(partition by supplier_id,product_id order by record_date asc) as rn, DATEADD(day,-1*(ROW_NUMBER()over(partition by supplier_id,product_id order by record_date asc)), record_date ) as keyy from stock where stock_quantity < 50 ) select supplier_id,product_id ,count(*) as number_of_days, MIN(record_date) as first_date from cte group by supplier_id,product_id,keyy having count(*)>1

[@harshitsalecha221]

Thank you for such and amazing question

[@AdilRasid-n4e]

with cte as( select supplier_id,product_id,record_date ,row_number() over (partition by supplier_id,product_id order by record_date) as rn ,day(record_date) - row_number() over (partition by supplier_id,product_id order by record_date) as group_id from stock where stock_quantity < 50) select supplier_id,product_id, count(*) as no_of_records, min(record_date) as first_date from cte group by supplier_id,product_id,group_id having count(*)>=2

[@piyushbamboriya1288]

with cte as (select supplier_id, product_id, stock_quantity, record_date, (datepart(day, record_date)-rank() over(partition by supplier_id, product_id order by datepart(day, record_date))) as rnk from stock where stock_quantity < 50 ) select supplier_id,product_id,count(*) as no_of_records, min(record_date) as first_date from cte group by supplier_id,product_id,rnk having count(*) > 1

[@sravankumar1767]

Superb explanation Ankit 👌 👏 👍

[@ethyria7685]

with cte as (SELECT *, LAST_VALUE(case when prev -1 then record_date end) ignore nulls over(PARTITION BY supplier_id, product_id ORDER BY record_date) as starting_date from (SELECT *, DATEDIFF(day, record_date, lag(record_date,1,record_date)over(PARTITION BY supplier_id,product_id ORDER BY record_date )) as prev, DATEDIFF(day, lead(record_date,1,record_date)over(PARTITION BY supplier_id,product_id ORDER BY record_date), record_date) as next from stock where stock_quantity < 50) a where prev = -1 or next = -1) SELECT supplier_id,product_id,count(starting_date) as no_of_days, starting_date from cte GROUP by supplier_id,product_id, starting_date

[@MovieBuzz-uu8kp]

select supplier_id,product_id, count(rw) as cnt, min (record_date) as start_date from ( select *, concat(date_sub(record_date, INTERVAL rw day),",",supplier_id) as group_id from ( SELECT *, row_number() over (partition by supplier_id,product_id order by record_date) as rw FROM stock where quantity < 50 order by supplier_id)) group by group_id,1,2 having cnt>=2 order by supplier_id,product_id

[@iamkiri_]

My Solution with cte as ( select *, extract(day from record_date ) as day_date, row_number() over(partition by supplier_id , product_id order by record_date ) as rn, extract(day from record_date ) - row_number() over(partition by supplier_id , product_id order by record_date ) as flag from stock where stock_quantity < 50 ) select supplier_id, product_id , min(record_date) as first_recorddate, count(*) as nofConsecutivedays from cte group by supplier_id, product_id ,flag having count(*) >= 2 ;

[@maheshnagisetty4485]

i wrote below code for same ouput with cte as( select * , rank() over(partition by supplier_id order by record_date) as rn from stock where stock_quantity=2) select supplier_id,product_id,no_of_days,first_date from cte2

[@rakeshkoll7112]

with x as (SELECT *,date_add(record_date,interval -row_number() over (partition by supplier_id ,product_id order by record_date) DAY)grp_date, row_number() over (partition by supplier_id ,product_id order by record_date)rn FROM stock_data) select x.supplier_id,x.product_id,count(1)no_of_days,min(record_date)first_date from x where x.stock_quantity=2

[@vaibhavverma1340]

My Solution If the table has same year only :) with cte as (select *, datepart(day, record_date)-row_number() over (order by (select null))rn from stock) select supplier_id, product_id, count(*)no_of_days, min(record_date) as first_date from cte where stock_quantity < 50 group by supplier_id, product_id, rn having count(*) >= 2

[@rahulmehla2014]

my soln : with cte as( select *,row_number() over(partition by supplier_id,product_id order by record_date) as rn from stock), cte2 as( select *,abs(day(record_date)-rn) as diff from cte where stock_quantity < 50) select supplier_id,product_id,count(1) no_of_days,min(record_Date) record_date from cte2 group by diff,supplier_id,product_id having count(1) >= 2;

[@arpanscreations6954]

My Solution: with stock50 as ( select supplier_id, product_id, record_date , lag(record_date, 1, record_date) over(partition by supplier_id, product_id order by record_date) as prev_date from stock where stock_quantity < 50 ) , grouped_stock as ( select supplier_id, product_id, record_date , sum(case when datediff(day, prev_date, record_date)=2

[@SHUBHAM_707]

## Simplest Solution with cte as( select * , DATEADD(DAY, -ROW_NUMBER() over(partition by supplier_id, product_id order by record_date), record_date) Con_Dates from stock where stock_quantity < 50 ) select supplier_id, product_id, count(*) no_of_days, min(record_date) first_date from cte group by supplier_id, product_id, Con_Dates having count(*)>1

[@KoushikT]

***** My Solution in PosgreSQL without lag and lead ****** with A as (select *, extract(day from record_date) - row_number() over (partition by supplier_id,product_id order by record_date) as grp from stock where stock_quantity < 50) select supplier_id, product_id, no_of_days, first_date from ( select supplier_id, product_id, grp, count(grp) as no_of_days, min(record_date) as first_date from A group by 1,2,3 having count(grp) >= 2) A order by 1,2,4

[@rawat7203]

Hi Sir My Way: with cte as( select *, row_number() over(partition by supplier_id order by record_date) as rn, DATEPART(day, record_date) - row_number() over(partition by supplier_id order by record_date) as flag from stock where stock_quantity < 50) select supplier_id, product_id, count(*) as no_of_records, min(record_date) as start_date from cte group by supplier_id, product_id, flag having count(*) >=2;

[@DataAnalyst251]

Figured it out till DATEDIFF but couldn't figure out how to remove those single date rows.

[@HARSHRAJ-gp6ve]

WITH cte AS ( SELECT * FROM stock WHERE stock_quantity < 50 ), cte1 AS ( SELECT cte.*, DAY(record_date) AS date1, ROW_NUMBER() OVER () AS r1 FROM cte ),cte2 as( SELECT supplier_id, product_id, record_date, CAST(date1 AS SIGNED) - CAST(r1 AS SIGNED) AS r2 FROM cte1 ) select supplier_id,product_id,COUNT(*),MIN(record_date) FROM cte2 GROUP BY supplier_id,product_id,r2;

[@shubhambarolia8220]

Question is very tricky.

[@Ashu23200]

bahot tough ho gaya ye toh. iski mentos zindagi solution nahi hain kya?

[@bhartisingh3750]

I dint understand group_id.. can someone please explain. Remaining everything i understood

[@SunilKumar_67]

Group id is important because, in the data if you see, we have multiple dates for the same supplier_id, product_id where the output is coming. If we don't have an identifier to differentiate those same supplier_id, product_id combinations, we can't output the data in the way we want. So, in order to make it convenient for us, we are incorporating a temporary flag sort of thing (group id) that descrimantes each group and also finally helps us to get the data at a level where we can simply use minimum function to get the date for each group. Did you understand?

[@bhartisingh3750]

@@SunilKumar_67 I understood that group id is important, but my question was how group id is being created here,, i dint understand that. I was looking for explanation on that.

[@SunilKumar_67]

@@bhartisingh3750 Acha got it. See, every time the difference in the dates is greater than 1 that means that it is the start of a new group in the same supplier and product id. If we create a new column which is basically a cumulative sum of this date diff flag, that cum_sum_flag will always be different for each group. So, since the cum_sum_flag is different for each group, we can use it to distinguish or use group by on that cum_sum_flag to get the number of days in that each group. If you still didn't understand, i suggest go step by step as explained by Ankit and see the changes in the output, you will understand it on your own

[@aktham-d3l]

Can we solve this problem using pandas? If yes, please provide the answer.

[@shubhamkhandelwal1111]

Hello Ankit, I have purchased your SQL course and the money is also deducted but the course is not reflecting in my profile. Please help me with this,i have already mailed you with the transaction screenshot

[@ankitbansal6]

Checking

[@Damon-007]

Ms Sql: with cte as(select *, row_number() over(partition by supplier_id, product_id order by iif( stock_quantity

[@balipavankalyan5008]

Thnak you so much

[@vamsivamsi2029]

Thanks @ankit

[@dakshbhatnagar]

👌

[@balipavankalyan5008]

challanging problem

[@Alexpudow]

MS SQL approach with recursive CTE with rec as ( select record_date from stock where record_date = '2022-01-01' union all select dateadd(day,1,record_date) record_date from rec where dateadd(day,1,record_date)

[@lovishbabar2154]

TBH very complicated solution...

[@Damon-007]

with cte as(select *, dateadd(day, -1*row_number() over(partition by supplier_id, product_id order by record_date),record_date) grp from stock where stock_quantity1;

[@Bigbakerscafe01]

He doesn't know how to explain in good way

[@tejas4054]

Kya sikha rha hai 🙄

[@vatsalasharma6131]

WITH InventoryCTE AS ( SELECT supplier_id, product_id, record_date, stock_quantity, LAG(stock_quantity) OVER (PARTITION BY supplier_id, product_id ORDER BY record_date) AS prev_stock_quantity FROM inventory ) SELECT supplier_id, product_id, MIN(record_date) AS starting_date FROM InventoryCTE WHERE stock_quantity < 50 AND prev_stock_quantity < 50 GROUP BY supplier_id, product_id HAVING COUNT(*) >= 2;

[@skkholiya]

WITH less_than_50 AS ( SELECT *, IF(datediff(record_date,lag(record_date,1) over(partition by supplier_id,product_id order by record_date))

[@Katakam.Ravikumar]

with cte as ( SELECT *, EXTRACT('doy' FROM record_date) as doy, ROW_NUMBER() OVER(PARTITION BY supplier_id, product_id ORDER BY record_date) as rn, EXTRACT('doy' FROM record_date) - ROW_NUMBER() OVER(PARTITION BY supplier_id, product_id ORDER BY record_date) as group_cl FROM stock WHERE stock_quantity= 2 ORDER BY supplier_id, product_id, first_date

[@prakritigupta3477]

Could you please explain the reason for making the column group_cl?

[@Katakam.Ravikumar]

@@prakritigupta3477 to find the groups of consecutive days

[@mattmatt245]

SELECT product_id, supplier_id, MIN(record_date) AS start_date FROM ( SELECT product_id, supplier_id, record_date, LEAD(stock_quantity) OVER (PARTITION BY product_id, supplier_id ORDER BY record_date) AS next_day_stock, LAG(stock_quantity) OVER (PARTITION BY product_id, supplier_id ORDER BY record_date) AS prev_day_stock FROM supplier_inventory ) AS subquery WHERE stock_quantity < 50 AND (next_day_stock < 50 OR prev_day_stock < 50) GROUP BY product_id, supplier_id;

[@DeolSahil]

Easier Solution : SELECT supplier_id, product_id, COUNT(*) AS total_consecutive_count, MIN(record_date) AS first_date FROM( SELECT supplier_id, product_id, stock_quantity, record_date, ROW_NUMBER() OVER(PARTITION BY supplier_id, product_id ORDER BY record_date) AS rn, DATEDIFF(DAY, ROW_NUMBER() OVER(PARTITION BY supplier_id, product_id ORDER BY record_date), DAY(record_date)) AS difference FROM stock WHERE stock_quantity < 50 ) subquery GROUP BY difference, supplier_id, product_id HAVING COUNT(difference) >= 2

[@vijaygupta7059]

my solution in MSSQL : little more like of code : with cte as ( Select * ,row_number()over(partition by supplier_id,product_id order by record_date ) as rn , cast (dateadd(day, row_number()over(partition by supplier_id,product_id order by record_date ) , '1900-01-01' ) as date) as new from stock where stock_quantity < 50 ),cte2 as( Select *,DATEDIFF(day,new,record_date) as new1 from cte ),cte3 as( Select * ,FIRST_VALUE(record_date)over(partition by supplier_id,product_id,new1 order by supplier_id ) as new3 from cte2) Select supplier_id,product_id,cnt, record_date from ( Select * ,row_number()over(partition by supplier_id,new3 order by supplier_id) as rn1 ,count(new3)over(partition by supplier_id,new3 order by supplier_id) as cnt from cte3 ) as a where rn1=1 and cnt > = 2

[@ankushjain4128]

Can we directly create a flag by using row_number ? SELECT a.supplier_id, a.product_id, count(*), min(record_date) as [start_date] FROM ( SELECT supplier_id, product_id, record_date, DateAdd(day, - ROW_NUMBER() Over(order by record_date), record_date) as flag_date FROM stock WHERE stock_quantity < 50 ) a GROUP BY a.flag_date, a.supplier_id, a.product_id HAVING COUNT(*) >= 2 ORDER BY a.supplier_id, a.product_id @Ankit : if you suggest any use case where above code will not work it will be more helpful to understand better way

[@harshpalsingh763]

WITH cte AS ( SELECT supplier_id, product_id, record_date, DENSE_RANK() OVER (PARTITION BY supplier_id, product_id ORDER BY record_date) AS rn, record_date - DENSE_RANK() OVER (PARTITION BY supplier_id, product_id ORDER BY record_date) AS date_diff FROM stock ), grouped AS ( SELECT supplier_id, product_id, record_date, min(record_date) over(PARTITION BY supplier_id, product_id, date_diff) as start_Date, COUNT(*) OVER (PARTITION BY supplier_id, product_id, date_diff) AS number_of_days FROM cte ) SELECT supplier_id, product_id, start_date AS first_date, number_of_days FROM grouped WHERE number_of_days >= 2 GROUP BY supplier_id, product_id, start_date, number_of_days;