Google SQL Interview Problem | Solving SQL Interview Query

In this video, let's solve an SQL Interview Problem asked during an Google interview. This problem was shared with me by someone who attended a Google Interview recently.

We shall first understand the problem statement and then come up with an approach to solve this problem and then finally write the SQL query to solve the problem.

FTC disclaimer: This video was sponsored by Skillshare.

🔴 My Recommended courses 👇

🔴 WATCH MORE VIDEOS HERE 👇

✅ SQL Tutorial - Basic concepts:

✅ SQL Tutorial - Intermediate concepts:

✅ SQL Tutorial - Advance concepts:

✅ Practice Solving Basic SQL Queries:

✅ Practice Solving Intermediate SQL Queries:

✅ Practice Solving Complex SQL Queries:

✅ Data Analytics Career guidance:

✅ SQL Course, SQL Training Platform Recommendations:

✅ Python Tutorial:

✅ Git and GitHub Tutorial:

✅ Data Analytics Projects:

THANK YOU,
Thoufiq

[faitusjeline]

The below simplified query worked for me in mysql

with cte as
(select *,
row_number() over (partition by username order by startdate) as rn,
count(*) over (partition by username) as ct
from useractivity)
select * from cte
where rn = case when ct = 1 then ct else ct - 1 end
;

[hardas]

I'm getting the same results by running the following:

with cte as
(select *,
row_number() over (partition by username order by startdate) as rn,
count(1) over (partition by username) as cnt
from useractivity)
select * from cte
where cnt = 1 or rn = cnt - 1;

[dwaipayansaha]

My solution with the script:-
create table activity1(
username varchar(20),
acitivity varchar(20),
startdate date,
enddate date);

insert into activity1 (username, acitivity, startdate, enddate)
values
('Amy', 'Travel', '2020-02-12', '2020-02-20'),
('Amy', 'Dancing', '2020-02-21', '2020-02-23'),
('Amy', 'Travel', '2020-02-24', '2020-02-28'),
('Joe', 'Travel', '2020-02-11', '2020-02-18'),
('Adam', 'Travel', '2020-02-12', '2020-02-20'),
('Adam', 'Dancing', '2020-02-21', '2020-02-23'),
('Adam', 'Singing', '2020-02-24', '2020-02-28'),
('Adam', 'Travel', '2020-03-01', '2020-03-28');

with t1 as(select username, activity, startdate, enddate, row_number() over(partition by username order by enddate desc) sorted_date,
count(enddate) over(partition by username) count from activity1)
select username, activity, startdate, enddate from t1
where (sorted_date=2 and count >1) or (sorted_date=1 and count=1)

[AdinaAdina-mgbz]

IT'S ALWAYS THE UNDERRATED VID THAT'S LEGIT! THANK YOU! <3

[RaviKumar-pnuw]

First Like... I'm eagerly waiting for your videos sir... Thanks 👍❤️

[hilarylomotey]

Genius as always. Thanks for sharing. Am sure u are overwhelmed with so many emails now. I am sure mine is missing in your inbox. Anyway love your videos. Wish you could locate my mail tho 😂😂😂. Keep it up and we love you

[Sharmasurajlive]

My attempt with CTE and Window functions :-

sample data:
create table useractivity (username text, activity text, startdate date, enddate date);

insert into useractivity values ('Amy', 'Travel', '2020-02-12', '2020-02-20');
insert into useractivity values ('Amy', 'Dancing', '2020-02-21', '2020-02-23');
insert into useractivity values ('Amy', 'Travel', '2020-02-24', '2020-02-28');
insert into useractivity values ('Joe', 'Travel', '2020-02-11', '2020-02-18');
insert into useractivity values ('Adam', 'Travel', '2020-02-12', '2020-02-20');
insert into useractivity values ('Adam', 'Dancing', '2020-02-21', '2020-02-23');
insert into useractivity values ('Adam', 'Singing', '2020-02-24', '2020-02-28');
insert into useractivity values ('Adam', 'Travel', '2020-03-01', '2020-03-28');

solution :
with cte as
(select *, rank() over (partition by username order by startdate desc) as rnk from useractivity),


cte2 as
(select username, activity, startdate, enddate, rank() over (partition by username order by startdate) as rnk from cte where rnk <= 2)


select username, activity, startdate, enddate from cte2 where rnk =1;

Thanks @techTFQ for taking such efforts 👍

[dasoumya]

Hello! This is my simple solution:
With cte as(Select *, row_number()over(partition by username order by startdate desc) as rn, count(*)over(partition by username ) as total_count
From User activity)
Select username, activity, startdate, enddate
From cte
Where rn=2 or total_count=1

[sayantabarik]

with cte as
(
select *,
row_number() over(partition by username order by enddate) as rn,
count(*) over(partition by username order by username) as cnt
from activity1)

select username, acitivity, startdate, enddate from cte where rn= case when cnt=1 then 1 else cnt-1 end

[kanwalhemant]

Same if we mention DESC in order by clause of row number & then replace 'cnt-1' to '2'

[arslansohail]

Thanks, TFQ
and love you boss the way you explain the query and solve
it's up to the mark ♥

[venkatareddykunduru]

easiest solution:

with cte as
(select *,
row_number() over(partition by username order by startdate desc) rn,
count() over(partition by username) as cnt
from activity1)

select * from cte
where (rn=1 and cnt=1) or (rn=2 and cnt>1)

[SantiagoZuluaga]

Thank you so much for sharing! In your solution, lines 4 & 5 could be simplified using:
, COUNT(*) OVER(partition by username) AS cnt

[gnataeee]

Thanks TFQ. Very helpful
I have a basic question

Leaving the rows with count =1 aside for now, would it be correct to sort the partition by DESCENDING order and then select the rows with row number rn=2 instead of the cnt-1?

[FaisalAli-psth]

I don't understand why do we need to have count at all when the question concerns about activity and time sorting. We could just sort the data by start_date and use nth_value() to get the second most recent activity. My solution is below:

with cte as (
select *,
coalesce(nth_value(activity, 2) over (partition by username order by start_date desc range between unbounded preceding and unbounded following), activity) as second_most_recent_activty
from activity1)
select username, activity, start_date, end_date from cte where

Coalesce is used because for Joe null will be returned as it has only one row.

[pavanareddy]

Thanks T! heading to learn the frame clause next. I tried out using your logic just ordered the result by start date in descending order.

with cte as
(select *
, row_number() over (partition by username order by startdate desc) rn
, count(*) over (partition by username order by startdate desc
range between unbounded preceding and unbounded following) cnt
from useractivity
order by username, startdate desc)
select username, activity, startdate, enddate
from cte
where rn= case when rn=cnt then 1 else 2 end;

[akiemcameron]

I need to work a lot of similar problems so partition and case becomes second nature. This is a great tutorial

[monasanthosh]

MYSQL Solutions for Freshers

With CTE as
(Select *, dense_rank() over (Partition by Username order by StartDate) as Rn from Activity1),
CTE1 as
(Select *,
Case When RN=2 then 1 Else 0 end as TRn from CTE),
CTE2 as
(Select *, Sum(TRN) over (Partition by Username) as TRN_Sum from CTE1)
Select Username, Acitivity, Startdate, enddate from CTE2
Where (Case When TRN_SUM=1 then RN=2 Else RN=1 end);

[maheshmmr]

Hii, your video are more useful, so please upload video about the topics like cluster, and indexex, thnq

[rishabhgupta]

have solved by :
select *
from (
select *,
row_number()over(PARTITION by username order by endDate desc) as rn,
count(*) over (PARTITION by username) as total_records
from Table_1
)
where (
(rn = 1 and total_records =1)
or
(rn = 2 and total_records <>1)
)