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

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

[@GowthamR-ro2pt]

Great Challenging Question !!!! 🤗
Thank you

[@CloudChallengers]

@GowthamR-ro2pt, Thanks for the feedback.

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

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

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

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

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

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

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

[@yashmathur5609]

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

[@CloudChallengers]

Asked for 4+ years experienced candidate.

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

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

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

[@pradeepvadlakonda007]

july month 7 but you entered as 9

[@CloudChallengers]

yes, you are correct.

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

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