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!
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!
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.
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
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
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
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
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
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;
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
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
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 👍
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
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
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
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 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.
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
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;
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 ;
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;
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 ❤️❤️
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;
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
;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
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)
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
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
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
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 ;
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;
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.
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_ ;
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;
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
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.
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😃
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;
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.
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;
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 👍😊
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..
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
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 ..
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
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
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
(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🙌
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...
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
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
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
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 */
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
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
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
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
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
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
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
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;
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;
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
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;
Planning Same Kind of series for BigData frameworks as well .. What's your thought??
Please plan for more series on sql queries sir
Make
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.
Can you do a video on how to get a data analyst / data scientist jobs from India to Abroad for freshers
Please make same kind of videos
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!
can you explain the use of (rn) in your script @navneet singh
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!
impressive
@@navneetsingh6245 Neat!! 🙌👌👏
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.
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
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
This is the actual implementation intended for the question. Elegant !
***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.
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
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
well written bro in simple way. Great
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
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;
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
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
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 👍
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
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
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
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;
It's a good approach.
I want to know if it can work if we remove LEFT JOIN and the rest remains?
@@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.
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
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 ;
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;
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 ;
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;
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 ❤️❤️
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;
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
;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
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)
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
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
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
select date,
sum(case when date
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
;
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;
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.
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_
;
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;
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
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.
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😃
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;
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.
Sure, Noted.
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;
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 👍😊
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..
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
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 ..
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
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
What percent of total class you will be taking live, in ineuron big data bootcamp?
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 😍😍😍😍😍😍
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
this is awesome content. thank you for sharing
(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🙌
Very informative.. thank you... Can you please guide to learn about pyspark basic and Hadoop basic
Very nicely explained.
I felt, This was quiet easy one
Sir please make one video on how to optimize the SQL query.
Nice question
Hey shashank can you please give the link of scaler's program of data analyst. Can't find it anywhere!
please make video on bigdata framework interview question
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...
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
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
Sir among cloud computing what delvery model is important there for big data engineer
Sir r program is better for big data engineer
Thanks Sir 🙏🙏
nice
Amazing!
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
Can you please create a full course on SQL,Please
bro your boat headset model, which you use for interviews?
Hey shashank, could you please tell me how I can transition myself into bigdata? Not finding enough details which one to choose?
Join Ineuron course in which Shashank is teaching
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
*/
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
Hi sir do you provide classes on ineuron,I just saw your picture there.
Yes, course is starting from 13-Aug
Can you please provide any good institute name for data scientist
My approch:
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
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
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
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
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
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
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;
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;
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
SELECT
date,
COUNT(DISTINCT user_id) AS unique_visitors
FROM
user_activities
GROUP BY
date
ORDER BY
date;
may i know y this won't run ? this is way simpler
;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
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;