SQL Question Asked in a FAANG Interview | Complex SQL 4

In this video we will discuss a question which was asked in one of the FAANG SQL interview. Do try yourself and let me know if you have a better solution.

[pmohantymohanty]

For BEGINNERS (MYSQL)

Prerequisites:

1. dayofweek() : gives the day of the week in integers- sunday(1), Monday (2), Tuesday (3) .... Saturday (7)

Example : select dayofweek('2024-05-16') ; output is 5 which is Thursday.

2. date_add() : this is used to get dates when one date is known the other is at a certain interval.

Example select date_add('2024-05-16', Interval 5 day) this adds 5 days to the given date and returns a date. Keep in mind it requires an Interval value in day/month/year.


Now:
Let's assume : given date is 2024-05-16 - thursday, weekday no 5.
To reach nth sunday lets say 3rd sunday.

We will have to add to the date, 3 days (1st Sunday) + 7 days (2nd sunday) + 7 days (3rd sunday)

Why 3 days?
Because given day is thursday
Then friday,
Then saturday
Then sunday(1st sunday)
So 3 + 7 * (n-1) days
=> 3 + 7*2 days

Or we can get 3 days by

Select 7 - dayofweek(given_date) + 1
7-> saturday's number
Dayofweek(given_date) = 5
Total = 3 days.

Now we have to add them together

date_add(given_date, INTERVAL(7- dayofweek(given_date) + 1 + 7 * (n-1)) Day)


- follow the synatx given above


Now let's put them together.


-- Step 1: Declare and initialize variables

SET @given_date = '2024-05-16'; -- The starting date
SET @n = 3; -- The nth occurrence of Sunday we want to find

-- Step 2: Calculate the nth Sunday from the given date

SELECT DATE_ADD(
@given_date,
) AS nth_sunday;


HAPPY LEARNING :) 😊

[priyankaduggal]

Love the way you keep the solutions so simple and crisp.Just started watching and I feel addicted to watching more n more of your videos

[dineshgudla]

It was bit confusing at start but I watched it twice again, now the concept is very clear..

[florincopaci]

I have some lost fights in the past with something like that.this is not easy not even in excel.Thank you for this video!

[anupampurkait]

Thanks ankit for this helpful video. Here's my solution with MySQL:

create function sunday_occur(today_date date, n int) returns date DETERMINISTIC
return date_add(date_add(today_date, interval (select case when 6 - weekday(today_date) = 0 then 7 else 6 - weekday(today_date) end) day), interval n-1 week);

select sunday_occur('2022-08-07', 1);

[suryasiram]

Thanks Ankit for the tutorials

DECLARE input_n INT64;
DECLARE input_date DATE;
DECLARE no_days INT64;
SET input_n = 3;
SET input_date = DATE(2023, 03, 02);


SET no_days = 7 - EXTRACT(DAYOFWEEK FROM input_date) +1;
SELECT DATE_ADD(input_date,
INTERVAL
(input_n -1) * 7 + no_days
DAY);

[DilipKumar-ofjk]

Here is the Oracle version of it.

Considered, dt as the input date and n1 as the nth Sunday that we are looking for.

with tbl as
(select to_date('08/18/2022', 'mm/dd/yyyy') as dt, 2 as n1 from dual)

select dt, n1,
dt+
(8-decode(trim(TO_CHAR(dt, 'DAY')), 'SUNDAY', 1, 'MONDAY', 2, 'TUESDAY', 3, 'WEDNESDAY', 4, 'THURSDAY', 5, 'FRIDAY', 6, 'SATURDAY', 7))
+(7*(n1-1))
as final_nth_sun_date

from tbl;



Thanks for sharing the complex questions. It is really helpful.

[poojasikdar]

/* to enhance clarity */

no. of days to be added = 7 - datepart(weekday, @input_date) + "Weekday of the required day"

/* in our case since we are trying to identify a date based on Sunday, the value for the 3rd argument is 1, incase it was a Wednesday we will pass 4 instead of 1, and likewise for Monday(2), Tuesday(3), Thursday(5), Friday(6), and Saturday(7) */

[tejaskifamily]

Hi Ankit, You're doing great work. Requesting you to also create a playlist for database design and data modeling if possible.

[SaurabhAhalawat]

Good work Ankit bhai i start learning how logic work in functions from you love from NOIDA

[gauravbhatia]

Thank You Ankit for explaining the concept clearly.
Since many functions used here are not valid in MySQL/SQLyog. I have provided a solution in MySQL/SQLyog:
SET @today_date='2022-05-18';
SET @n=3;
-- First execute above 3 lines


Note: Weekday function in mysql returns 6 for sunday, 0 for monday, 1 for tuesday, 2 for wednesday and so on. Hence had to use 6 - weekday(date) to reach nearest Sunday

[smit]

Thank you Ankit! You have inspired me to do same for power BI ! Thank you!

[gagansingh]

Awesome Amit ji keep posting More interview questions

[ajmalroshan]

intresting
Took little time to understand but intresting
keep it up

[abhinabjha]

For postgres using extract -- write a query to provide the nth occurence of sunday from a given date.

with given_date as
(
select cast('2024-06-14' as date) as input_date, 2 as n
) select (input_date + (6-cast(extract(DOW from input_date) as integer) ) +1) + (n-1)*7 as n_th_sunday from given_date;

[anthonychilaka]

Day 4 - 47

Datepart function use case 2.

Thank you Ankit for helping my learning.

[biplabroy]

Is it important to remember these functions, syntax for Interview ? There are so many databases with different syntax.

[oorjamathur]

Thanks for the very informative videos Ankit.
The below snippet would give results for n = 0 as well: -

declare @today_date date = getdate();
declare @n int = 2;
declare @dayofweek int = datepart(WEEKDAY, @today_date);
declare @daystillsunday int = case
when @dayofweek = 1 then 0
else 7 - @dayofweek
end;

[akp-]

we can do like this also:
select dateadd(day, (7-datepart(dw, '2024-04-28')), '2024-04-28')

explain:
datepart(dw, '2024-04-28'))---This will give you current day of week(starting sunday 1 end Saturday 7) example 28th april is Sunday so O/P will be 1

(7-datepart(dw, '2024-04-28')) ---this will subtract current day from 7
Example: 7-1 =6
dateadd(day, (7-datepart(dw, '2024-04-28')), '2024-04-28') –it will add subtracted result in given date and find next Sunday
example: 6+28th April so next is 5th May which is Sunday

[mrx]

Good explanation Ankit. Can you also try to provide equivalent queries supported in other database systems like Oracle, Postgres, MySQL etc. I know we can get the same using ChatGPT but sometimes it's not very accurate. Anyways kudos to your knowledge. Also what are your other recommended sources to improve on SQL knowledge including performance tuning.