Freshworks Data Analyst SQL Interview Problem | SQL For Data Analytics

[@ankitbansal6]

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

[@shipra959]

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

[@naveenbhandari5097]

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]

Glad to know that ☺️ keep rocking 💪

[@akashgoel601]

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

[@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=sku.price_date)
SELECT
month_date,
month_price
from t2
where price_rnk=1
ORDER by 1;

[@Amulya869]

This UA-cam channel is more useful.Give me some more like this

[@avi8016]

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

[@girishpv8193]

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

[@ankitbansal6]

Thanks a ton🙏

[@nidhisingh4973]

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

[@sachinn5503]

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;

[@kadagaladurgesh3691]

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]

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

[@sandhyakumari8204]

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

[@ankitbansal6]

Okay sure

[@DEwithDhairy]

PySpark Version of this problem :
ua-cam.com/video/c94gZ8NdMHA/v-deo.html

[@hairavyadav6579]

Nice explanation sir great.....

[@addhyasumitra90]

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

[@MubarakAli-qs9qq]

🎉Sir mast question h

[@gautamigaikwad4549]

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

[@grim_rreaperr]

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);

[@grim_rreaperr]

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

[@gautamigaikwad4549]

Thank you

[@grim_rreaperr]

@@gautamigaikwad4549 🙏

[@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

[@Dhanushts-g7x]

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

[@apna9656]

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

[@ankitbansal6]

Okay sure

[@vaibhavverma1340]

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

[@Aman-lv2ee]

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

[@vinil9212]

smart work handling the NULL, but you could have just used COALESCE

[@its_anky5382]

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

[@TY-zl1vw]

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]

thank you

[@ritusantra8641]

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;

[@arpanscreations6954]

Hi Ankit, I think we can add the condition in the cte "where day(price_date) 1" isn't it?

[@vinil9212]

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;

[@nash_life]

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]

Don't worry keep practicing

[@madhurimadas6260]

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

[@sobermenezes]

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?

[@rajkumarrajan8059]

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

[@grim_rreaperr]

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);

[@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;

[@SreemantaKesh]

great question

[@xiamojq621]

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

[@srikarrar261]

Sir, please Create Calendar Table video

[@zaravind4293]

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

[@amanpratapsingh8631]

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

[@apurvasaraf5828]

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

[@rohitsharma-mg7hd]

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

[@srinivasareddybandi983]

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

[@LakshmiPujitha-g7b]

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

[@jay_rana]

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 ??

[@ykirankumar4985]

with cte as (
select price_date,price, dateadd(day,1,EOMONTH(price_date,-1)) as mo_start,
dateadd(day,1,EOMONTH(price_date)) as next_mo_start,
first_value(price) over(partition by month(price_date) order by price_date desc) as mo_end_price
from sku),
prev_month as (
select mo_start,lag(mo_end_price,1,mo_end_price) over (order by mo_start) as prev from cte
),next_month as (
select next_mo_start,mo_end_price from cte
), final as
(
select distinct coalesce(mo_start,next_mo_start) as [Date], coalesce(prev,mo_end_price) as Price
from prev_month full join next_month
on prev_month.mo_start=next_month.next_mo_start)
select [Date], Price,Price-lag(Price,1,Price) over (order by [Date]) as diff
from final;

[@vikasvk9174]

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 ?

[@hariikrishnan]

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

[@ankitbansal6]

It's a tough one ..4 plus YOE

[@AmanRaj-uf7wx]

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]

Thanks

[@saketarora]

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

[@ankitbansal6]

The price can be different ..

[@atulsharma2789]

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

[@june17you]

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

[@ankitbansal6]

You need to use the extract function

[@sammail96]

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

[@sourabhpatel3834]

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;

[@sonalijain9024]

I USED THIS APPROACH SO IT WILL WORK FINE ALL POSIBILITIES. IT WILL BE A GENERIC SOLUTION .PLZ TELL ME
with cte as
(
select *,
ROW_NUMBER() over(partition by sku_id,year(price_date),month(price_date) order by price_date desc) rnk,
datetrunc(MONTH,DATEADD(month,1,price_date)) next_month
from sku
)
select sku_id,next_month,price from cte
where rnk =1
union all
select sku_id,price_date,price
from sku
where DATEPART(day,price_date) = 1
order by next_month asc

[@skkholiya]

with cte as(
select *,
lead(price_date) over(order by price_date) next_date,
lag(price) over(order by price_date) pre_price,
row_number() over(partition by sku_id) rn
from sku
),
cte_month as(
select *,date(concat(year(price_date),"-",rn,"-","1")) month_date from cte
)
select *,
coalesce(price-lag(price) over(order by month_date),0) dif
from (
select sku_id,
month_date,if(price_date

[@ManishChauhan-fb7uz]

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

[@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 😂

[@PraveenSinghRathore-df3td]

MYSQL solution:
with recursive cte as (
select min(price_date) as first_date, '2023-12-31' as end_date from sku
union all
select date_add(first_date,interval 1 day) as first_date, end_date from cte
where first_date < end_date
),
cte2 as (select first_date, day(first_date) as cal_day, month(first_date) as cal_month from cte)
select s.sku_id,c.first_date,s.price from cte2 c join (select sku_id,price_date,date_add(lead(price_date,1,date_add(price_date,interval 1 month))
over(partition by sku_id order by price_date),interval -1 day) as valid_till,price from sku) s on c.first_date
between s.price_date and s.valid_till
where cal_day = 1
order by first_date;

[@sauravverma8355]

@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]

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]

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;