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

[@shashank_mishra]

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

[@sandeepsandy3786]

Please plan for more series on sql queries sir

[@pankajbharati]

Make

[@svs9610]

i dont get it. the world is getting rid of sql based databases and google asks sql?? i thought no sql database are the future.. please correct me.

[@rose9466]

Can you do a video on how to get a data analyst / data scientist jobs from India to Abroad for freshers

[@hiteshramrakhiani2473]

Please make same kind of videos

[@anjalim942]

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.

[@navneetsingh6245]

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!

[@rohansharma1046]

can you explain the use of (rn) in your script @navneet singh

[@navneetsingh6245]

Sure, @@rohansharma1046! (rn) is an alias that I gave to the column I created using ROW_NUMBER and then I used (rn) in the case statement to ensure that when a user is performing their *first activity* only then that user is being counted as a unique user.
I hope I have answered your doubt!

[@gopinathg318]

impressive

[@sankuM]

@@navneetsingh6245 Neat!! 🙌👌👏

[@NaveenKumar-gp1wu]

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

[@sailearner5058]

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

[@Rafa-nn8tl]

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 ❤️❤️

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

[@channeladventure9434]

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

[@akhil7069]

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

[@krishnachaitanyareddy2781]

well written bro in simple way. Great

[@Reacher1998]

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;

[@kravioli]

this is awesome content. thank you for sharing

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

[@saranyabalaji7905]

This is the actual implementation intended for the question. Elegant !

[@ShyamSharma-kl6bd]

Love you Shashank Sir... bcz of you i got to know that Data Eng has good career.. and bcz of you i joined Big data course on ineuron.. You will be the revolutionary person in Data Eng field ..

[@SalesforceCloudEngineering]

What percent of total class you will be taking live, in ineuron big data bootcamp?

[@abhisheksinghmahra446]

sir i am in 1sem of my mca and you're a great inspiration for me... keep uploading such videos and more free data engineering courses 😍😍😍😍😍😍

[@ajinkyahatolkar294]

Very nicely explained.

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

[@harshpradhan9217]

It's a good approach.

[@harshpradhan9217]

I want to know if it can work if we remove LEFT JOIN and the rest remains?

[@BismarckWangkhem]

@@harshpradhan9217 Hi, we need all the dates. date even where there is no unique users. try running just the inner query. hope it should get the clarification.

[@DilipKumar-of3jk]

Thanks for the video!! Here is my solution.
select dateq, sum(decode (rn,1,1,0)) as unique_users from (
select dateq, row_number() over (partition by user_id order by dateq asc) as rn from user_activity)
group by dateq
order by 1 ;

[@arijitsaha5499]

Thank you for making these videos. I used your hint and solved using window function, cte and aggregation before looking at the solution. My query was:
with cte as(
select date, user_id, ROW_NUMBER() over(PARTITION by user_id order by date) as rn
from user_activity ua)
SELECT date, count(case when rn = 1 then user_id end) as unique_user_count
from cte
GROUP by 1
order by 1

[@harishkanta3711]

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

[@prajjwaljaiswal3419]

I think you are missing one key aspect which Ankit Bansal have utilized brilliantly. He runs each small subqueries sequentially and showing output. That you can also think to use..

[@RoamingWithAbhijeet]

with tab as
(
select *,ROW_NUMBER() over(order by date,user_id asc) as Rn
from user_activity as T1 Join (
select min(date) as mn ,user_id AS mn_ID from user_activity group by user_id
) as T2 on T1.user_id=T2.mn_ID
)
select date, Count ( New_User_Id) as Cnt from
(
select *,case when date=mn then USER_ID end as New_User_Id from tab
)as T3 group by date
/*----------------------------------------------------------OR-------------------------------------------------------------------------*/
with tab as (
select *,ROW_NUMBER() OVER(ORDER BY date asc) as RN
from user_activity
)
select date,Count(New_User) as CNT from
(
select *,Case when user_id NOT IN (Select Distinct user_id from tab as T2 WHERE T2.RN

[@anshumansrivastava2801]

Hello Shashank bhai,
This is my approach.
select u5.date,count(case when u3.user_id is null then 1 else null end) as count
from user_activity u5 left join
(
select distinct u1.date,u1.user_id from user_activity u1 inner join user_activity u2
on u1.date>u2.date
and u1.user_id=u2.user_id) u3
on u5.date=u3.date
and u5.user_id=u3.user_id
group by u5.date
order by 1

[@ThePragyashree]

Very informative.. thank you... Can you please guide to learn about pyspark basic and Hadoop basic

[@sushanthsai2078]

my solution,
with cte1 as (select *, row_number() over (partition by user_id order by date1 asc) as newcol1
from user_activity1)
select date1, sum(case when newcol1 = 1 then 1
else 0 end) as distinctusercol from cte1
group by date1;

[@rushikeshn9715]

Thanks Sir 🙏🙏

[@wearevenom3645]

Hey shashank can you please give the link of scaler's program of data analyst. Can't find it anywhere!

[@dheemanjain8205]

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

[@stat_life]

with cte as(
select
date,
user_id,
rank() over(partition by user_id order by date) as rnk
from user_activity
)
select date, sum(case when rnk=1 then 1 else 0 end) as unique_user_count
from cte
group by date

[@rashmidutta7151]

With cte2 as (With cte as ( select *, min(date) over(partition by userid) from users as minm)
Select * from cte where minm=date)
Select date, count(userid) from cte2
Group by date;

[@vishalsharma-jm9nw]

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

[@maanshaab3482]

Sir, your videos are very helpful for us.Sir,can you please make a video on how as a fresher we get a data analytics job.what are the basic skills are required.I know sir there are many courses but sir they are paid.Please suggest some course which are free for those which are not able to buy paid course.

[@shashank_mishra]

Sure, Noted.

[@vijaygupta7059]

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

[@Sarthak631]

Amazing!

[@btv978]

bro your boat headset model, which you use for interviews?

[@dhananjayghumre2205]

Sir among cloud computing what delvery model is important there for big data engineer

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

[@srinivas9925]

;with cte1 as(
select distinct min(date)over(partition by user_id order by date) as dt,user_id from user_activity),
dates as (select distinct date from user_activity)
select u.date,count(c.user_id) from dates u left join cte1 c on u.date = c.dt
group by u.date

[@SwayamRath22]

We can directly use "group by user_id" inside the with clause to get our derived table. group by will do the same thing that Sashank did using window function. My query was as below;
with user_login as(
select *
from user_activity
group by user_id
)
select ua.date,
case
when ul.date is NULL then 0
else ul.unique_user_id
end as unique_user_count
from(select distinct(date)
from user_activity) as ua
left join(select date, count(*) as unique_user_id from user_login group by date) as ul
on ua.date = ul.date;
Please correct me if I am wrong.

[@renganathanmutthiah9150]

A solution without using windowing would be...
select
a.activity_date
,coalesce(b.new_user_count,0) as new_user_count
from
(select distinct activity_date from user_activity) a
left join
(select activity_date
,count(distinct user_id) as new_user_count
from
user_activity a
where
user_id not in ( select user_id from user_activity b where b.activity_date < a.activity_date)
group by
activity_date
) b
on a.activity_date = b.activity_date
order by
activity_date

[@samar1900]

I felt, This was quiet easy one

[@tanmayakumarmund6874]

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

[@surajbais7533]

Query output:- with t as (Select date,count(userid) unique_user_count from user_activity group by date)
select date,
case when date='2022-02-20' then 2 when date='2022-02-22' then 1
else 0 end as unique_user_count
from t
OR
select date, count(userid) unique_user_count
from user_activity
where date='2022-02-20'
group by date
union
select date, count(userid) unique_user_count
from user_activity
where userid not in (select userid from user_activity where date='2022-02-20') and date='2022-02-22'
group by date
union
select date,Replace(count(userid),3,0) unique_user_count
from user_activity
where date='2022-02-24'
group by date
Output:-
/*
date unique_user_count
2022-02-20 2
2022-02-22 1
2022-02-24 0
*/

[@davidkr7348]

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

[@sreejitchakraborty6575]

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;

[@manjuc1059]

In Bigquery
with cte as (
SELECT date,user_id,
dense_rank() over (partition by date order by user_id) as uniqu3_user_account
FROM )

select date,max(uniqu3_user_account) as uniqu3_user_account from cte
group by date
order by date ;

[@gangadharareddy605]

My Solution:
WITH cte AS(
select date, user_id, MIN(date) OVER(Partition by user_id ORDER BY DATE) AS min_date
from user_activity
)
select date, SUM(CASE WHEN date=min_date THEN 1 ELSE 0 END) AS unique_user_count from cte
GROUP BY date;

[@divyanshigarg4299]

I want my friend podcast to be posted on your channel as he has transition from a service based org. Like to product based org like Qualcomm, J.P. Morgan etc.

[@adityapratapsingh7649]

***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:
Account Curr_Score Prev_score Date_gap Alert_needed
A1 10 5 2 N
A2 0 10 4 Y
Let me know you approaches.

[@005aditya]

select date,
sum(case when date

[@abhinavsayankar5309]

Hey shashank, could you please tell me how I can transition myself into bigdata? Not finding enough details which one to choose?

[@_k_a_m_a_l.]

Join Ineuron course in which Shashank is teaching

[@hasanmougharbel8030]

Hey there,
Kudos to your great efforts.
I have a general enquiry as i am a totally sql noob.
I want to design a form that would be connected to a database server.
Where shall this form template be designed? Visual studio, sql management studio..
Is their any form builder tool that you would recommend me to use..
Thanks a lot...

[@nesbeerpn]

please make video on bigdata framework interview question

[@rajanverma97]

with rownum as(
select *,case
when row_number() OVER(partition by user_id order by date)>1 then 'NU' else 'U' end as uniqidentify
from user_activity
)
select date,sum(case when uniqidentify='U' then 1 else 0 end) as unique_user_count from rownum
group by date

[@adityasingh-ef9nj]

with cte as
(select date, user_id, row_number() over(partition by user_id order by date ) as rw from user_activity),
cte_2 as (select distinct date from user_activity),
cte_3 as (select date ,count(user_id) as cnt from cte where rw=1 group by date order by date )
select d.date ,coalesce(cnt,0)from cte_3 c right join cte_2 d on
c.date=d.date group by d.date order by d.date

[@akshitsinghal8590]

Sir please make one video on how to optimize the SQL query.

[@puneetnaik8719]

with rank_date as (
SELECT * , dense_rank() over (partition by user_id order by date) as ds
from user_activity),
unique_id as (
select date,sum(ds) as sum_ds from rank_date where ds=1 group by date )
select distinct user_activity.date,coalesce(sum_ds,0) as unique_usr_count from user_activity left join unique_id on unique_id.date=user_activity.date

[@nirmalbisht9710]

My solution:
with cte as(select A.date,count(*) as record from
(select date,user_id,rank() over(partition by user_id order by date) as rr from user_activity)
A where A.rr=1 group by A.date)
select date, case when record is null then 0 else record end as record_count
from(select u.date,record from cte c right join user_activity u on c.date=u.date group by u.date,record)

[@ananthram8062]

select * from user_activity
with cte as (
select *, row_number() over(partition by user_id order by date ) as rnk from user_activity
)
select date, sum(case when rnk = 1 then 1 else 0 end ) as unique_user_count from cte
group by date

[@boyinanagendrababu7860]

select dt "Date",sum(num) Distinct_user_count
from (
select a.*,case when row_number() over(partition by user_id order by dt )=1 then 1 else 0 end num
from active_users a
)
group by dt order by dt
;

[@tasaduqriyaz-ho3pj]

with in_result as
(select * from
(select *,row_number() over(partition by user_id order by dated asc) as sequence_no
from user_identity) x
where sequence_no =1),
t1 as
(select distinct dated from user_identity
)
select t1.dated,count(in_result.user_id)
from t1 left join in_result on t1.dated=in_result.dated
group by t1.dated
order by t1.dated;

[@jatinsingh7928]

Nice question

[@shivarajhalageri2513]

(Assign rank partition by user id order by date keep this in cte) then take out rank 1 from this cte and keep this in cte 2 , select dates from original table which are not in cte 2 and append unique user as 0 for each date here using case when and keep it in cte 3 then take cte 2 gp by date and count the users keep this in cte 4 now join cte 4 with cte 3 with union all that gives you the solution. Correct me if I am wrong🙌

[@sourabhsharma9985]

Hi sir do you provide classes on ineuron,I just saw your picture there.

[@shashank_mishra]

Yes, course is starting from 13-Aug

[@robinhoodgaming3114]

I solved the question before seeing your logic, my solution is using correlated subquery with not exists in where clause.
For dates of 0 count i used the same logic left join of distinct dates.
I got confidence on my skills. Thanks for this video 👍😊

[@bhujithmadav1481]

I have reframed the question: Number of new users who have joined every day: The solution is:
with joining_dates as(
select user_id,
min(date) as joining_date
from user_activity
group by user_id)
select joining_date,
count(user_id) as unique_user_count
from joining_dates
group by joining_date
I don't know whether this is the correct way. Please tell if I am missing anything.

[@rethinavelyogesh8886]

with new_table as (select *,row_number() over(partition by user_id order by date) as new from
user_activity)
select date,sum(ficol) from (select *,case when new =1 then 1 else 0 end as ficol from new_table) x
group by date
if this query correct give like sir😃

[@musicloverr547]

WITH cte as(
SELECT
activity,
date,
user_id,
COUNT(user_id) OVER (PARTITION BY date) AS ns
FROM
user_activity)
select DISTINCT
date,
ns
from cte ;

[@dhananjayghumre2205]

Sir r program is better for big data engineer

[@varunmahanot5766]

My solution:
with occurences as (
select
u.user_id,
u.date,
row_number() over(
partition by u.user_id
order by u.date
) occ
from
user_activity u
),
first_occurence as (
select
user_id,
date
from occurences
where occ = 1
), dates as (
select distinct date
from
user_activity
),
new_users_by_day as (
select
d.date,
f.user_id
from dates d
left join first_occurence f on d.date = f.date
), is_new_user as (
select
*,
case
when user_id is null then 0
else 1
end as new
from new_users_by_day
)
select
date,
sum(new) as new_users
from is_new_user
group by date;

[@dibakarmandal2148]

Here is my solution:
with cte AS
(
select *
, ROW_NUMBER() OVER(partition by userid order by date_) as rn
from user_activity
)
select date_
, sum(case when rn=1 then 1 else 0 end) as count
from cte
group by date_
order by date_
;

[@sohailshaiwale7338]

Can you please create a full course on SQL,Please

[@vijaychilkewar4239]

Simple approach using Left join :
select u1.date, count(distinct(case when u2.user_id is null then u1.user_id end)) from user_activity u1 left join user_activity u2 on
u1.user_id=u2.user_id and u1.date> u2.date
group by u1.date

[@ankurshrivastava2911]

Can you please provide any good institute name for data scientist

[@innominatesoloist1597]

nice

[@niteshanwani6584]

My solution
select a.date,COUNT(b.user_id)
from
(select distinct date from user_activity) as a
LEFT JOIN
(select user_id,min(date) as date from user_activity group by user_id) as b
ON a.date = b.date
GROUP BY a.date

[@VeerendraGopi]

Hi Shashank! Can we use this statement instead of ranking? if not please mention why?
select min_date, count(user_id) from (select MIN(date) min_date, user_id from user_activity group by user_id) tb group by min_date

[@ishansanganeria7]

In this case, your result wont have dates on which you had 0 users. You need to do a join with a table which has all the dates to make sure your final result as dates too

[@RajatAhuja-bs2nz]

Here's my solution without using a join -
WITH CTE AS(
SELECT date, user_id, activity,
ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY date) as visit
FROM User_activity)
SELECT date,
SUM(CASE WHEN visit = 1 THEN 1 ELSE 0 END) as unique_user_count
FROM CTE
GROUP BY date

[@Amarjeet-fb3lk]

My approch:

[@Amarjeet-fb3lk]

with data as (
select '2022-02-20' as Date,1 as uid,"abc" as activity UNION ALL
select '2022-02-20',2,"xyz" UNION ALL
select '2022-02-20',4,"xyz" UNION ALL
select '2022-02-22',1,"xyz" UNION ALL
select '2022-02-22',3,"klm" UNION ALL
select '2022-02-24',1,"abc" UNION ALL
select '2022-02-24',2,"abc" UNION ALL
select '2022-02-24',3,"abc" UNION ALL
select '2022-02-24',4,"abc"
)
,
d1 as (select Date,sum(r) as cnt from(
select *,row_number() over (partition by uid order by uid ) as r from data
qualify r=1
order by 2
)
group by 1
)
select date,0 as cnt from (
select date from data except distinct
select date from d1
) UNION DISTINCT
select * from d1

[@geesview1717]

Hi bro, whatever u guys r providing paid promotions for all kinds of IT related institutions are legitimate or not.
pls,check and provide. we know for the sake of money u r doing, but for us this is life as well as hard-earned money and time. I got suffered from one of the institute,which u referred through one of ur video.
we subscribes blindly believing u, whatever u will provide information will be 100% should be true and pls, loyal to ur subscribers
Thanks

[@shashank_mishra]

I don't promote anything without checks, verification and trust. Even I talk to the founder & co-founders to know more about the product, it's authenticity and results.. then I prmote it

[@adityapratapsingh7649]

with table1 as (select date, user_id, row_number() over (partition by user_id order by date) as rk
from user_activity)
select t1.date, case when count1 is null then '0' else count1 end as new_member from (select distinct date from table1) t1 left join (select date,count(*) as count1 from table1 where rk=1 group by date) t2 on t1.date=t2.date

[@parvejparvej4822]

You talk more than you do

[@shashank_mishra]

@@parvejparvej4822 Use better explain karna nahi bolte hai??

[@manishdogra250]

select u1.date, count(distinct case when u2.user_id is null then u1.user_id end) as new
from user_activity u1 left join user_activity u2
on u1.date > u2.date and u1.user_id = u2.user_id
group by u1.date

[@raghu7174]

Alternate approach using rank() and countif() functions:
SELECT
date,
COUNTIF(rank_ = 1) AS unique_user_count
FROM (
SELECT *,
RANK() OVER(PARTITION BY user_id ORDER BY date) AS rank_
FROM `big-query.user_activity`
)
GROUP BY date

[@EditAndGrind]

SELECT
date,
COUNT(DISTINCT user_id) AS unique_visitors
FROM
user_activities
GROUP BY
date
ORDER BY
date;

[@EditAndGrind]

may i know y this won't run ? this is way simpler

[@HARSHRAJ-wz2rp]

with cte as(
select user_id,MIN(date) as date1 FROM user_activity GROUP BY user_id
),cte1 as(
select user_activity.*,date1 FROM user_activity JOIN cte ON user_activity.user_id=cte.user_id
)
select date,
COALESCE(COUNT(CASE when date=date1 THEN 1 ELSE NULL END),0) AS unique_user_count
FROM cte1 GROUP BY date;

[@rahulkiroriwal8779]

select l1.date,count( case when l2.user_id is NULL then l1.user_id end) as user_count from User_activity l1
left join User_activity l2 on l1.user_id=l2.user_id and l1.date>l2.date
group by l1.date;

[@angadjitsingh6144]

WITH users_cte AS (
SELECT
user_id u_id,
MIN(a_date) min_date
from user_activity
GROUP BY user_id
)
SELECT
a_date,
COUNT(
CASE
WHEN a_date = (
SELECT
users_cte.min_date
from
users_cte
WHERE
user_id = users_cte.u_id
) THEN user_id
END
)
from user_activity
GROUP BY a_date;

[@gopalreddy9897]

;WITH X AS(
SELECT DATE,COUNT(1) CNT FROM #user_activity A WHERE NOT EXISTS(SELECT 1 FROM #user_activity B WHERE B.user_id=A.user_id AND B.date