Freshworks Data Analyst SQL Interview Problem

Freshworks Data Analyst SQL Interview Problem | SQL For Data Analytics

Рет қаралды 21,714

4 ай бұрын

In this video we will discuss a SQL interview problem asked in Freshworks for a data analyst position. We will solve this problem using 2 methods with and without calendar table.
here is the script:
create table sku
(
sku_id int,
price_date date ,
price int
);
delete from sku;
insert into sku values
(1,'2023-01-01',10)
,(1,'2023-02-15',15)
,(1,'2023-03-03',18)
,(1,'2023-03-27',15)
,(1,'2023-04-06',20)
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 #dataanalytics #freshworks

Пікірлер: 71

@ankitbansal6 4 ай бұрын

Give me 1000 likes on this video and I will create a video on how to create a calendar table from scratch 😊

@shipra959 4 ай бұрын

Bring only records with StudentMarks greater than 75. Catch is DO NOT use WHERE/GROUPBY CLAUSE Sample Input: StudentId StudentMarks 1 99 2 76 3 71 4 50 5 76 Expected Output: StudentId StudentMarks 1 99 2 76 5 76 recently i faced this qus in ey interview for data engineer with 4 yr of exp

@avi8016 4 ай бұрын

Wow, this was great 💯 I guess I'll need to work on the date function Thankyou 🙏

@KisaanTuber 4 ай бұрын

Hi Ankit. Thanks for posting & explaining such challenging SQL problems. Here is my stab at the problem without using calendar table: with RECURSIVE t1 as ( SELECT date_trunc('month', MIN(price_date)) as month_date from sku UNION ALL SELECT month_date+interval '1 month' as month_date from t1 where month_date=sku.price_date) SELECT month_date, month_price from t2 where price_rnk=1 ORDER by 1;

@Amulya869 4 ай бұрын

This KZfaq channel is more useful.Give me some more like this

@nidhisingh4973 2 ай бұрын

Hello Ankit, Really grateful to you for all these amazing videos.

@naveenbhandari5097 4 ай бұрын

Hi Ankit bhai, Today I have completed all the videos from your channel. Here I just want to thank you for making such amazing videos. Your way of explaining things is really commendable, I have failed in many interviews bcos of advanced SQL concepts but this time I have gained confidence I never had. Once again thank you for making such life-changing videos. Keep doing great! may god give you all the success you wish! Thanks, Man. looking forward a great learning ahead from your channel.

@ankitbansal6 4 ай бұрын

Glad to know that ☺️ keep rocking 💪

@user-im6ui9zd8v 3 ай бұрын

great question

@girishpv8193 4 ай бұрын

Very good explanation Ankit... Initially I thought this looks simple..but the way you generalized the query is awesome.. Keep going 👏

@ankitbansal6 4 ай бұрын

Thanks a ton🙏

@sandhyakumari8204 4 ай бұрын

Thanks Ankit, it will be helpful if you can create a video on making of calendar table!

@ankitbansal6 4 ай бұрын

Okay sure

@xiamojq621 4 ай бұрын

Good video but have you thing about procedures and functions questions there are very rare in KZfaq

@kadagaladurgesh3691 4 ай бұрын

Great explanation Thanks for the video, I have a doubt At time 13:56 to avoid duplicates we use new condition with and operator, can we achieve same result with Union instead of union all

@srikarrar261 4 ай бұрын

Sir, please Create Calendar Table video

@sobermenezes 3 ай бұрын

Excellent video Ankit. A query on your second method though: the inner join you used has an incomplete ON clause (on.c.cal_date). How’s that possible?

@Aman-lv2ee 4 ай бұрын

We can use union also instead of union all and a subquery: with cte as ( select *,dense_rank()over(partition by sku_id, extract(month from price_date), extract(year from price_date) order by price_date desc) as dk from sku ), cte2 as ( select sku_id, price_date as new_price_date, price from sku where date_part('day', price_date) = 1 union select sku_id, date(date_trunc('month',price_date+INTERVAL '1 month')) as new_price_date , price from cte where dk =1 ) select *, lag(price,1,10)over(order by extract(month from new_price_date)), price-lag(price,1,10)over(order by extract(month from new_price_date)) as difference from cte2

@vaibhavverma1340 4 ай бұрын

Here is my Attempt Sir , Please have a look. with cte as (select *, DATEFROMPARTS(year(price_date),month(price_date),'01')start_of_month ,(case when price_date > DATEFROMPARTS(year(price_date),month(price_date),'01') then lag(price,1) over (partition by sku_id order by price_date) else price end)price_start_of_month from sku) ,eliminate_duplication_months as (select sku_id, start_of_month, price_start_of_month, dense_rank() over (partition by sku_id, start_of_month order by price_date)dr from cte) select sku_id as SKU, start_of_month as [Date] , price_start_of_month as Price, price_start_of_month -lag(price_start_of_month,1,price_start_of_month) over (partition by sku_id order by price_start_of_month)Dif from eliminate_duplication_months where dr =1

@harshSingh-if4zb Ай бұрын

A little mess but getting correct output: select sku_id ,price_date, price, dr, case when month = 0 then price else lg end as final_price from (select *, lag(price,1,0) over(order by price_date) as lg from (select *, dense_rank() over(partition by month1 order by price_date desc) as dr from (select *, concat(left(price_date , 7) , "-01") as month1, datediff(price_date, concat(left(price_date , 7) , "-01")) as month from sku) a)b where dr=1)c;

@srikarrar261 4 ай бұрын

14:24 Hi Sir, may I know what will happen instead of taking UNION ALL with UNION. I think we don't need to use Subquery to filter out the price data having 1st day of month

@apna9656 4 ай бұрын

Hi Ankit, It would be helpful for us, if you can create a video on calender table

@ankitbansal6 4 ай бұрын

Okay sure

@rajkumarrajan8059 4 ай бұрын

Ankit, Please create the calendar table from scratch !!!!!

@grim_rreaperr 4 ай бұрын

DECLARE @StartDate DATE = CAST('2000-01-01' AS DATE); /*set start date*/ DECLARE @EndDate DATE = CAST('2024-12-31' AS DATE); /* set end date */ WITH calendar AS ( SELECT @StartDate AS cal_dates UNION ALL SELECT DATEADD(DAY, 1, cal_dates) FROM calendar WHERE cal_dates < @EndDate ) SELECT cal_dates, MONTH(cal_dates) AS cal_month, DATEPART(DAYOFYEAR, cal_dates) AS cal_year_day, DAY(cal_dates) AS cal_month_day, DATEPART(WEEK, cal_dates) AS cal_week, DATEPART(WEEKDAY, cal_dates) AS cal_week_day, DATEPART(QUARTER, cal_dates) AS cal_quarter_num FROM calendar OPTION(MAXRECURSION 0);

@madhurimadas6260 4 ай бұрын

I did like this with cte as(SELECT *, DATEADD(month, DATEDIFF(month, 0, price_date) + 1, 0) AS first_day_of_month, row_number()over(partition by year(price_date),month(price_date) order by price_date)as rnk, lead(price) over(partition by year(price_date),month(price_date) order by price_date)as pre FROM sku) select cte.sku_id,case when pre is null then price else pre end as price,first_day_of_month from cte where cte.rnk=1 union all select sku_id,price,price_date from sku where datepart(day,price_date)=1 order by first_day_of_month

@karangupta_DE 4 ай бұрын

with recursive cte as ( select (select min(price_date) from sku) as all_dates union all select all_dates + interval '1 day' from cte where true and all_dates

@vikasvk9174 4 ай бұрын

Hi Ankit, I have one doubt instead of 2023-01-01 we have 2023-01-10 in that case will not get first recode in our final output ryt ?

@zaravind4293 4 ай бұрын

Hi Ankit I have one query how to get the alternate characters in upper case remaining in lower case like name is Rahul then output should be RaHuL. how can we achieve this in sql

@nash_life 4 ай бұрын

Good Explanations Sir. I failed 3 interviews in the past 3 days because of SQL. i am not sure why I am not able to build solutions. I hope to learn from your videos.

@ankitbansal6 4 ай бұрын

Don't worry keep practicing

@TY-zl1vw 4 ай бұрын

Very informative, first time I heard about the DATE_TRUNC function, but it's not available for me to practice, since I'm using SQL Server 2018. Edit: Could DATEADD(DAY, 1,EOMONTH(price_date,0)) achieve the same ?

@vishnugottipati9373 3 ай бұрын

thank you

@apurvasaraf5828 4 ай бұрын

with cte as (select *,RANK() over(partition by sku_id,month(price_date) order by day(price_date) desc) as r from sku) select price_date,price from sku where day(price_date)=1 union all select datetrunc(MONTH,DATEADD(month,1,price_date)) as d, price from cte where r=1

@jay_rana 4 ай бұрын

In the 1 st attempt can't we use Union instead of Union All, this will remove the duplicate record with same price date value on 1st day of month cases ??

@DEwithDhairy 4 ай бұрын

PySpark Version of this problem : kzfaq.info/get/bejne/mZ9kmr1ps8qxeHU.html

@gautamigaikwad4549 4 ай бұрын

Please create a video on how to create calendar table 15:10

@grim_rreaperr 4 ай бұрын

working as intended in ms sql server, we can other attribute columns as well like month name, day name etc

@gautamigaikwad4549 4 ай бұрын

Thank you

@grim_rreaperr 4 ай бұрын

@@gautamigaikwad4549 🙏

@srinivasareddybandi983 4 ай бұрын

with cte1 as ( select *,row_number() over(partition by sku_id,extract(month from price_date) order by price_date desc) rn from sku ) ,cte2 as ( select sku_id,price_date,price,cast(date_trunc('month',price_date) as date) as dt,lag(price,1,price) over() as lp from cte1 where rn = 1 union all select sku_id,cast(price_date+interval '1 month' as date) as price_date,price, cast(date_trunc('month',price_date+interval '1 month') as date) as dt,price as lp from sku where price_date = (select max(price_date) from sku) ) select dt,new_price,new_price-lag(new_price,1,new_price) over () as diff from ( select dt,price_date, case when dt=price_date then price when dt

@amanpratapsingh8631 4 ай бұрын

Here is my solution in MySQL: with cte as( select *,row_number() over(order by sku_id) as m, year(price_date) as y from sku), cte2 as( select *,concat(y,"-",m,"-","01") as concat_date from cte), cte3 as( select sku_id,price_date,price,STR_TO_DATE(concat_date,"%Y-%m-%d") AS converted_date from cte2 order by converted_date), cte4 as( select *, lag(price) over(order by price) as lag_price, datediff(converted_date,price_date) as dd from cte3) select sku_id,converted_date, case when dd>=0 then price when dd

@user-zx1ii2cx2j 4 ай бұрын

with recursive cte1 as (Select min(price_date) pd from sku union all select date_add(pd,interval 1 day) pd from cte1 where pd

@hariikrishnan 4 ай бұрын

How many YOE candidates can expect such questions ? Found it quite hard as a fresher (< 1YoE)

@ankitbansal6 4 ай бұрын

It's a tough one ..4 plus YOE

@ritusantra8641 4 ай бұрын

with cte as (select *, cast (dateadd(mm,DATEDIFF(mm,0,price_date)+1,0) as date) as date ,rank() over(partition by year(price_date),month(price_date) order by day(price_date) desc) as rnk from sku) select sku_id, date, price from cte where rnk = 1 union all select sku_id, price_date, price from cte where day(price_date) = 1 and month(price_date) =1 order by date;

@atulsharma2789 3 ай бұрын

Ankit this solution is work or not ?? with cte as( select *, ROW_NUMBER() over(partition by sku_id,year(price_date),month(price_date) order by price_date desc) skudate from sku),cte2 as( select sku_id,price_date,DATEADD(Month,DATEDIFF(Month,1,price_date),0)nextofmonth,price from cte where skudate=1), cte3 as( select sku_id,price_date,price,isnull(lead(price_date) over(order by sku_id),'2023-05-01') nextmonth1 from cte2 ) select sku_id,price_date,price from sku where datepart(day,price_date)=1 union all select sku_id,DATEADD(Month,DATEDIFF(Month,1,nextmonth1),0),price from cte3

@rohitsharma-mg7hd 26 күн бұрын

my simple solution: with cte1 as (SELECT month::date FROM generate_series('2023-01-01', '2024-01-01', INTERVAL '1 Month') month), cte2 as (select *,lead(price_date,1,'2023-05-01') over() as prev_date from sku) select * from cte1 c1 join cte2 c2 on c1.month between c2.price_date and c2.prev_date

@saketarora 4 ай бұрын

Doing unions all and then not in?? Could have just done union?

@ankitbansal6 4 ай бұрын

The price can be different ..

@june17you 4 ай бұрын

Hi can anyone help me on what is the equivalent function of datetrunc in mysql

@ankitbansal6 4 ай бұрын

You need to use the extract function

@sammail96 4 ай бұрын

For oracle sql also extract function works same way as datetrunc@@ankitbansal6

@AmanRaj-uf7wx 4 ай бұрын

MYSQL: with cte as ( select *, row_number() over (order by sku_id) as mth, str_to_date((concat(year(price_date), "-", row_number() over (order by sku_id), "-", "01")),"%Y-%m-%d") as updated_date from sku ) ,cte2 as ( select sku_id, price_date, price,updated_date, lag(price,1,0) over (order by price ) as lag_price, datediff(updated_date, price_date) as dd from cte order by updated_date) select sku_id, updated_date, case when dd >= 0 then price when dd

@MITHUNKUMAR-nx8rd 4 ай бұрын

Thanks

@sourabhpatel3834 4 ай бұрын

WITH CTE1 AS (SELECT price_date, price, ROW_NUMBER() OVER (PARTITION BY MONTH(PRICE_DATE) ORDER BY PRICE_DATE DESC) RNK, DATEADD(DAY, 1, EOMONTH(price_date)) next_month FROM SKU), CTE2 AS (SELECT price_date, price, price_date AS next_month FROM sku WHERE DAY(price_date) = 1 UNION ALL SELECT price_date, price, next_month FROM CTE1 WHERE RNK = 1) SELECT *, price - LAG(price, 1, price) OVER (ORDER BY next_month) diff FROM CTE2;

@story_teller_Is Ай бұрын

sir aap tottle hn kya

@ankitbansal6 Ай бұрын

Haan m totla hoon. Mere papa bhi totle hain..Mera pura khandaan totla hai. Hum sab TA ko TA bolte hain

@story_teller_Is Ай бұрын

@@ankitbansal6 😃lagta h aap bhavuk hogye😁

@ankitbansal6 Ай бұрын

@@story_teller_Is haha just kidding 😂

@ManishChauhan-fb7uz 4 ай бұрын

select sku_id as SKU ,price_date as Date ,price ,prev_price-lag(prev_price,1,prev_price) over(order by price_date) as Diff from ( select * ,lag(price, 1, price) over(order by date_part('month',price_date)) as prev_price ,rank() over(partition by date_part('month', price_date) order by date_part('day',price_date)) as rnk from sku ) as t where rnk = 1

@sauravverma8355 4 ай бұрын

@ankit bansal select *,ifnull(price-lag(price,1) over(partition by sku_id),0) as Diff from (WITH cte AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY sku_id, YEAR(price_date), MONTH(price_date) ORDER BY price_date DESC) AS rn FROM sku) SELECT sku_id, STR_TO_DATE(DATE_FORMAT(price_date + INTERVAL 1 MONTH, '%Y-%m-01'), '%Y-%m-%d') AS 'date', price FROM cte WHERE rn = 1 UNION SELECT *FROM sku WHERE DAY(price_date) = 1 order by month(date) asc) as a; this one is simpler soln as compared to u

@ManpreetSingh-tv3rw 4 ай бұрын

Could this have been done by the recursive CTE ? Like expanding the rows from Jan 1 to Jan 30 , then feb 1 to feb 29 ? I am trying this approach not sure if it will work. @ankitbansal6

@sabesanj5509 4 ай бұрын

WITH cte AS ( SELECT SKU, DATE, PRICE ROW_NUMBER OVER (PARTITION BY SKU ORDER BY DATE) AS rnk FROM prices ) SELECT SKU, DATEFORMAT(DATE, '%y%M-01') AS start_of_month, PRICE FROM cte WHERE rnk = 1;