Freshworks Data Analyst SQL Interview Problem | SQL For Data Analytics

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:

Most Asked Join Based Interview Question:

Solving 4 Trick SQL problems:

Data Analyst Spotify Case Study:

Top 10 SQL interview Questions:

Interview Question based on FULL OUTER JOIN:

Playlist to master SQL :

Rank, Dense_Rank and Row_Number:

#sql #dataanalytics #freshworks

[ankitbansal]

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

[naveenbhandari]

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.

[KisaanTuber]

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<(select max(price_date) from sku)
),
t2 as
(SELECT
t1.month_date,
sku.price_date,
price as month_price,
rank() over(PARTITION by t1.month_date ORDER by sku.price_date desc) as price_rnk
from t1 left join sku
on
SELECT
month_date,
month_price
from t2
where price_rnk=1
ORDER by 1;

[akashgoel]

thanks for this, posting my sol. little different approach(actually it similar to your second soln, after watching complete video i realised):

with cte as (
select price_date, price,
lead(DATEADD(day, -1, price_date), 1, DATEADD(month, 1, price_date)) OVER(order by price_date) as lead
from sku
), cte_2 as (
SELECT price_date
, DATEADD(DAY, 1, EOMONTH(price_date, 0)) as frst_Date
from sku
)
select price_date, price from sku where DATEPART(day, price_date)=1
union all
select distinct s.frst_Date, a.price
from cte a join cte_2 s
on s.frst_Date BETWEEN a.price_date and a.lead

[avi]

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

[girishpv]

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

[Amulya]

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

[nidhisingh]

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

[sachinn]

WITH CTE AS(
SELECT *,
ROW_NUMBER() OVER(PARTITION BY MONTH(PRICE_DATE)) D,
LAG(PRICE) OVER(ORDER BY PRICE) S
FROM SKU)
SELECT PRICE_DATE, PRICE, (PRICE-S) AS DIFF FROM CTE
WHERE D=1;

[Dhanushts-gx]

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<=(select date_add(max(price_date), interval 1 month) from sku)
), cte2 as
(select *, lead(price_date, 1, "2023-12-31") over(order by price_date) pd2 from sku)

SELECT sku_id, pd price_date, price, price-LAG(price, 1, price) over(order by price_date) diff
FROM cte1 join cte2 on pd between
price_date and pd2 WHERE dayofmonth(pd)=1 order by pd

solution using recursive

[kadagaladurgesh]

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

[srikarrar]

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

[vinil]

with cte as (
select sku_id, price_date, price, ROW_NUMBER() OVER(PARTITION BY month(price_date)
order by price_date desc) AS rnk FROM sku),

cte2 as(
SELECT sku_id, DATETRUNC(month, DATEADD(month, 1, price_date)) AS next_month, price FROM cte WHERE
rnk=1
UNION ALL
SELECT * FROM sku WHERE DATEPART(day, price_date)=1
)

SELECT *, coalesce(price-LAG(price) OVER ( ORDER BY next_month), 0) AS price_diff FROM cte2 ORDER BY next_month;

[addhyasumitra]

my approach: following 1st method with lead-lag
with CTE as (
select *,
ROW_NUMBER() OVER(partition by sku_id, month(price_date) order by price_date desc) as rn
from SKU)
select sku_id, price_date, price from SKU where DATEPART(DAY, price_date)=1
UNION ALL
select sku_id,
datetrunc(month, isnull(LEAD(price_date) OVER(partition by sku_id order by price_date), DATEADD(month, 1, price_date)))
as next_month, price
from CTE where rn=1

[karangupta_DE]

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 <= '2023-05-01'
-- (select max(price_date) from sku)
),
cte1 as (
select sku_id, price, price_date, lead(price_date, 1, '2023-05-01')over(partition by sku_id order by price_date) as next_price_date
from sku
),
cte2 as (
select sku_id, price, all_dates
from cte c
join
cte1 c1
on
all_dates between price_date and next_price_date
),
cte3 as (
select sku_id, price, all_dates
from cte2
where true
and day(all_dates) = 1
)
select sku_id, all_dates, price - lag(price, 1, price)over(partition by sku_id order by all_dates) as difference
from cte3;

[sandhyakumari]

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

[vaibhavverma]

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

[gautamigaikwad]

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

[its_anky]

with cte as (
Select *, row_number() over(partition by sku_id, year(price_date), month(price_date)
order by sku_id, month(price_date) asc, price_date desc) rnk from sku )

select sku_id, case when day(price_date)=1 then price_date else DATETRUNC(month, price_date) end as start_date,
isnull(lag(price) over(order by price_date), price) price_at_month_start from cte
where rnk=1

[LakshmiPujitha-gb]

Hi Bhai,
My answer is
;WITH CTE AS(
select MIN(PRICE_DATE) dt, 1 as cnt,
datepart(MONTH, max(PRICE_DATE)) CNTDT
from SKU
union all
select DATEADD(MONTH, 1, dt) dt, cnt+1 cnt, CNTDT
FROM CTE where CNTDT>=CNT
), skct as(
select PRICE_DATE, lead(price_date, 1, DATEADD(month, 1, price_date))
over(ORDER BY price_date) NXTDT,
price
FROM sku
)
select *, price, ABS(PRICE-
lag(price, 1, price)
over(ORDER BY price_date))
FROM cte left join skct
on 1=1 and dt between price_date and nxtdt