GOOGLE - SQL Interview Question🔥! Most Asked Question with SOLUTION 🔥 Data Analyst - Data Scientists

𝗝𝗼𝗶𝗻 𝗺𝗲 𝗼𝗻 𝗦𝗼𝗰𝗶𝗮𝗹 𝗠𝗲𝗱𝗶𝗮:🔥
🔅Telegram Group - LearningBridge
🔴Join for 1x1 Mentorship, Mock Interviews, Career Guidance, Resume Building, Interview roadmap and resources to crack tech, BigData/Data Engineering tutorials from scratch :

𝗗𝗶𝘀𝗰𝘂𝘀𝘀𝗲𝗱 𝗤𝘂𝗲𝘀𝘁𝗶𝗼𝗻𝘀:

✅ 0:00 -- Inside This Video
✅ 0:50 -- Problem Statement for SQL Question
✅ 4:19 -- Job Guaranteed SDE Courses
✅ 6:11 -- Solution explanation for SQL Question
✅ 10:54 -- SQL Query Solution
✅ 16:15 --Query Execution on MySQL Server

===========

create table user_activity(date date,user_id int,activity varchar(50));

insert into user_activity values('2022-02-20',1,"abc");
insert into user_activity values('2022-02-20',2,"xyz");
insert into user_activity values('2022-02-22',1,"xyz");
insert into user_activity values('2022-02-22',3,"klm");
insert into user_activity values('2022-02-24',1,"abc");
insert into user_activity values('2022-02-24',2,"abc");
insert into user_activity values('2022-02-24',3,"abc");

#google #sql #datascience

[shashank_mishra]

Planning Same Kind of series for BigData frameworks as well .. What's your thought??

[navneetsingh]

I solved this using a window function, subquery, and a case statement and my code is :

SELECT date, SUM(CASE WHEN rn = 1 THEN 1 else 0 END) unique_user_count
FROM(
SELECT *, ROW_NUMBER() OVER(partition by user_id order by date) rn
FROM user_activity)x
GROUP BY date

I also want to thank you for doing these SQL Interview questions videos, they are very helpful!

[anjalim]

select date, sum(case when rnum=1 then 1 else 0 end ) from (
select date, user_id, row_number() over(partition by user_id order by date) as rnum
from user_activity)
group by 1;

Thank you for this video!! Really appreciate your effort and willingness to share the knowledge. Looking for more such videos.

[NaveenKumar-gpwu]

Another Approach:
with user_details as
(
select user_id, min(activity_date) as activity_date
from
user_activity
group by user_id
)


select
u1.activity_date,
sum(case when u2.user_id is null then 0 else 1 end) as unique_user_count
from
user_activity u1
left join
user_details u2
on
u1.activity_date = u2.activity_date
group by u1.activity_date

[adityapratapsingh]

***QUESTION ASKED TO ME IN A PRODUCT BASE***
SOURCE:
Account score Load_date
A1 0 01-Jan-21
A1 0 02-Jan-21
A1 0 03-Jan-21
A1 5 04-Jan-21
A1 5 05-Jan-21
A1 5 06-Jan-21
A1 10 08-Jan-21
A1 10 09-Jan-21
A2 10 05-Jan-21
A2 10 06-Jan-21
A2 0 07-Jan-21
A2 0 08-Jan-21
A2 0 09-Jan-21
A2 0 10-Jan-21

EXPECTED OUTPUT:

A1 10 5 2 N
A2 0 10 4 Y

Let me know you approaches.

[KoushikT]

My Approach on this using LAG window function

WITH user_details
AS (SELECT *,
Lag(user_id)
OVER (
PARTITION BY user_id ROWS BETWEEN unbounded preceding AND
CURRENT
ROW) AS
prev_activity
FROM user_activity
ORDER BY DATE)
SELECT DATE,
Sum(CASE
WHEN prev_activity IS NULL THEN 1
ELSE 0
END) AS unique_user_count
FROM user_details
GROUP BY DATE

[sailearner]

Hi
I have been following you for the past 2 months, my UG B.Sc biotechnology and I finished my MBA in 2011, present i am working in hospital as admin... Now i want to switch my career to IT...

By your inspiring stories i have learnt MySQL.... Still practicing in it...

Is it possible to get the job in IT field....is my age (34)and educational background will affect my chances of getting job

Pls suggest me

[akhil]

Thanks a ton for your practicalQ videos!, alternate approach while trying:

select u.date, count(distinct x.user_id) unique_user_count from
(select user_id, min(date) date from user_activity group by user_id) x
right join user_activity u on u.date=x.date
group by u.date

[channeladventure]

Hi Shashank, thanks for simple and intuitive solution

Here is another simple solution using running sum with window function:

with cte1 as (select date, user_id, count(user_id) as pr from user_activity group by date, user_id),
cte2 as (select date, user_id, sum(pr) over(partition by user_id order by date) as user_sum from cte1),
cte3 as (select *, case when user_sum=1 then 1 else 0 end as user_count_new from cte2)
select date, sum(user_count_new) as unique_user_count from cte3 group by date

[harishkanta]

Try this method, this is simpler:

with cte as
(select user_id, min(date) as date from user_activity
group by user_id),
cte2 as (select a.date,
case when a.date=b.date then a.user_id else null end as user_id
from user_activity a
join cte b
on a.user_id=b.user_id
)
select date, count(user_id)
from cte2
group by a.date

[BismarckWangkhem]

Hi thank you for sharing. Here is my approach :
with cte as
(select distinct u.date, t.user_id from user_activity u
left join (select
min(date) date, user_id from user_activity
group by user_id)t
on u.date = t.date)

select date, count(user_id) from cte
group by date
order by date asc;

[dheemanjain]

with cte1 as
(select *, row_number() over(partition by user_id order by date) as rn from user_activity),
cte2 as
(select count(distinct user_id) as cou, date as c from cte1 where rn=1 group by date),
cte3 as
(select distinct date as d from user_activity),
cte4 as
(select d, cou from cte3 left join cte2 on cte3.d=cte2.c)
select d as date, case when cou is null then 0 else cou end as unique_user_count from cte4

[sobhiksaha]

Thanks for the problem.. Here my take on this:

select a.date,
sum(case when b.date is Null then 1 else 0 end) as unique_user_count
from user_activity a left join user_activity b
on a.date > b.date and a.user_id = b.user_id
group by 1
order by 1;

[Rafa-nntl]

Yes nice video. ❤️❤️Please do more sql interview questions and Please do more Hadoop spark hive kafka for interview questions and real world scenarios.

Thanks alot ❤️❤️

[vishalsharma-jmnw]

with tbl as (select *,
rank() over (partition by user_id order by date) as min
from new
order by date)

select date,
sum(case when min = 1 then 1 else 0 end )as unique_user_count
from tbl
group by 1

[vijaygupta]

my solution in MSSQL DB :
with cte as
(
Select *
, case when user_id in (select user_id from user_activity where u.date>date) then 0 else 1 end as new_user_id
from user_activity as u
)
Select date, sum(new_user_id) as unique_user_count
from cte
group by date

[Sharmasurajlive]

Solution using correlated query, cte and left join:

with cte as(
select u.date,
count(user_id) as "unique_user_count"
from user_activity u
where
u.user_id not in (select distinct user_id from user_activity where date < u.date)
group by 1)

select distinct u.date, coalesce(c.unique_user_count, 0) as "unique_user_count"
from user_activity u
left join cte c using (date);

Thanks for sharing this such helpful questions !!
Kudos 👍

[harshuvj]

with cte1 as
(select *,
row_number() over (partition by user_id order by date) rank
from user_activity
order by date),
cte2 as
(select * from cte1 where rank = 1),
cte3 as
(
select a.*, case
when b.rank is null then 0 else b.RANK end rank1 from user_activity a left join cte2 b on a.date = b.date and a.user_id = b.user_id

)

select date, sum(rank1) as unique_cust
from cte3
group by date

[davidkr]

create table user_act(tra_dt date, user_id int, activity varchar(20));

insert into user_act values ('2022-02-20', 1, 'abc');
insert into user_act values ('2022-02-20', 2, 'abc');
insert into user_act values ('2022-02-22', 1, 'abc');
insert into user_act values ('2022-02-22', 3, 'abc');
insert into user_act values ('2022-02-24', 1, 'abc');
insert into user_act values ('2022-02-24', 2, 'abc');
insert into user_act values ('2022-02-24', 3, 'abc');

with user_log as (
select min(tra_dt) as tra_dt,
user_id
from user_act
group by user_id
)

select distinct(a.tra_dt), coalesce(b.user_id, 0) from user_act a
left join (select tra_dt, count(user_id ) as user_id
from user_log group by tra_dt) b
on a.tra_dt=b.tra_dt

[sreejitchakraborty]

My solution:

with cte as(
select q1.first_activity, u.*, case when date1=first_activity then 1 else 0 end as flag from
(select min(date1) as first_activity, user_id from user_activity group by user_id) q1
right join
user_activity u
on (q1.user_id=u.user_id))
select date1, sum(flag) as unique_users from cte group by date1 order by date1;