Affine Technologies SQL Interview question - Find 2nd Wednesday of current month.

[@snehsparsh7954]

This is was the most interesting question, till now in this playlist. -- The key is always to find the first occurrence and then add the appropriate offset. -- Adjust the DOW value and the offset calculation accordingly. -- Find the 3rd Monday of the current month. -- For example, if CURRENT_DATE is '2025-02-01', the query returns '2025-02-17'. -- In PostgreSQL, Monday is represented by day-of-week (DOW) 1. WITH FirstMonday AS ( SELECT DATE_TRUNC('month', CURRENT_DATE) + CASE WHEN EXTRACT(DOW FROM DATE_TRUNC('month', CURRENT_DATE)) = 1 THEN INTERVAL '0 day' -- First day is already Monday WHEN EXTRACT(DOW FROM DATE_TRUNC('month', CURRENT_DATE)) < 1 THEN (1 - EXTRACT(DOW FROM DATE_TRUNC('month', CURRENT_DATE))) * INTERVAL '1 day' -- First day is before Monday ELSE (7 + 1 - EXTRACT(DOW FROM DATE_TRUNC('month', CURRENT_DATE))) * INTERVAL '1 day' -- First day is after Monday END AS first_monday ) SELECT (first_monday + INTERVAL '2 week')::DATE AS third_monday -- Add 2 weeks and cast to DATE FROM FirstMonday;

[@montudeb8060]

loved the challenge , here is my approach with cte as ( select datetrunc(MONTH,cast(GETDATE() as date)) as dates union all select DATEADD(day,1,dates) as dates from cte where dates < DATEADD(day,30,datetrunc(MONTH,cast(GETDATE() as date))) ), cte2 as( select *, DATENAME(weekday, dates) as day_name from cte ), cte3 as( select *, ROW_NUMBER() over(order by dates) as rn from cte2 where day_name = 'Wednesday' ) select dates, day_name from cte3 where rn = 2 ;

[@ishanshubham8355]

i tried this using MYSQL with recursive cte as ( select "2024-07-01" as first_day union select first_day+interval 1 day from cte where first_day < last_day(curdate())), cte2 as ( select *,row_number() over(order by first_day) as rn from cte where dayname(first_day) ="wednesday") select first_day as dates from cte2 where rn = 2

[@GowthamR-ro2pt]

Great Challenging Question !!!! 🤗 Thank you

[@CloudChallengers]

@GowthamR-ro2pt, Thanks for the feedback.

[@hairavyadav6579]

One of the best question

[@hairavyadav6579]

Love this question i am solving in mysql i face may difficulty but i enjoy mysql solution with recursive num as(select date_format(curdate(),"%Y-%m-01") as date union all select date_add(date, interval 01 day) from num where month(date_add(date, interval 01 day)) = month(curdate())), cte as(select *,date_format(date,"%W")as weekday from num where date_format(date,"%W") = "Wednesday") select date as 2nd_wednesday from (select *, row_number() over(order by date) rnk from cte) sal where rnk =2;

[@MusicalShorts-hn1px]

Plz keep on posting more such problems

[@CloudChallengers]

@MusicalShorts-hn1px, Thanks for the comment. I will keep add such kind of interview questions.

[@ncrcottonbalesseeds118]

By using case statement. Select case when to_ char(trunc(sysdate,'mm'),'d') =4 then trunc(sysdate,'mm')+7 else next_day(next_day(trunc(sysdate, 'mm'),'wed'),'wed') end as 2-nd - Wednesday from dual;

[@shashank_1180]

another approach as i am comfortable with EOMONTH() with dates(datee) as( select dateadd(day,1,EOMONTH(dateadd(month,-1,getdate()))) as datee union all select dateadd(day,1,datee) as datee from dates where datee

[@CloudChallengers]

@shashank_1180, Thanks for sharing the different approach.

[@HARSHRAJ-gp6ve]

with cte as( select NOW() as date1 ),cte1 as( select CONCAT(YEAR(date1),'-','0',MONTH(date1),'-01') as t1 FROM cte ) select ADDDATE(t1,(7-(DAYOFWEEK(t1)-1))+3) as 2nd_wed FROM cte1;

[@himanshushorts7143]

with recursive cte as ( select 1 as n union all select n+1 from cte where n < day(last_day(current_date())) ), cte1 as (select date(concat(year(current_date()),'-',month(current_date()),'-',n)) dates, dayname(concat(year(current_date()),'-',month(current_date()),'-',n)) days from cte), cte2 as (select *,row_number() over(order by dates) rn from cte1 where days = 'wednesday') select dates,days from cte2 where rn=2;

[@harshitsalecha221]

WITH RECURSIVE DateSequence AS ( SELECT '2024-07-01' AS date UNION ALL SELECT DATE_ADD(date, INTERVAL 1 DAY) FROM DateSequence WHERE date BETWEEN '2024-07-01' AND '2024-07-31' ) SELECT date FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY date) as rn FROM datesequence WHERE dayname(date)='Wednesday') as a WHERE a.rn=2;

[@theinsightminer08]

My approach WITH RECURSIVE cte AS (SELECT DATE(CONCAT(YEAR(CURRENT_DATE),'-', MONTH(CURRENT_DATE),'-',01)) as date UNION ALL SELECT date + INTERVAL 1 DAY FROM cte WHERE date < CURRENT_DATE()) SELECT date FROM cte WHERE WEEKDAY(date) = 2 ORDER BY date LIMIT 1;

[@yashmathur5609]

Hi, Was this asked for 2 years experience or more?

[@CloudChallengers]

Asked for 4+ years experienced candidate.

[@atharvjoshi9959]

dataset?

[@velagambetipoojitha]

there is no specific dataset we have to generate it using recursive method as shown in the video

[@CloudChallengers]

@atharvjoshi9959, Go through the video and understand the question.

[@shaikrahaman6411]

declare @month table ( monthdate date ) ------------ declare @SOM date = dateadd(month,-1,dateadd(day,1,eomonth(getdate()))) declare @eom date = eomonth(getdate()) ---select @SOM,@eom while @SOM

[@pradeepvadlakonda007]

july month 7 but you entered as 9

[@CloudChallengers]

yes, you are correct.

[@pallavimohapatra2697]

I tried using Postgresql :- WITH RECURSIVE cte AS ( SELECT DATE_TRUNC('month', CURRENT_DATE)::date AS date UNION ALL SELECT (date + INTERVAL '1 day')::date FROM cte WHERE (date + INTERVAL '1 day')::date < DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month' ), cte1 as(SELECT date, TO_CHAR(date, 'Day') AS day_name, row_number() over(order by date asc) as rn FROM cte where TO_CHAR(date, 'Day') = 'Wednesday' ORDER BY date) select date from cte1 where rn = 2