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 ;
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;
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)
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
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
@TechTfq -I could see that you use window function effectively in most of the solutions. Just want to know if window function is good performancewise also... Thanks
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;
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
@@imyours777 You're COUNTING, so you don't need to order them. Of course, you need to order them but in function ROW_NUMBER() so it's gonna stay as it is: --using mysql WITH t AS ( select U.*, ROW_NUMBER() OVER (PARTITION BY username ORDER BY endDate DESC) as order_activity, COUNT(*) OVER (PARTITION BY username) as total_activities from useractivity AS U ) SELECT username, activity, startDate, endDate FROM t WHERE (order_activity = 1 AND total_activities = 1) OR (order_activity = 2 AND total_activities >= 2);
@@imyours777 with cte as ( select *, row_number() over(partition by username order by startdate) as rn, count(*) over(partition by username) as cnt from useractivity ) select username,activity,startdate,enddate from cte where rn = (case when cnt > 1 then cnt-1 else 1 end)
@techtfq,, why dont we use u this way based on rowid,,, select * from (select username,activity,startsate,count(*) over (partition by username) as cnt,row_number() over (partition by username order by rowid asc) rn from useractivity) where rn=2 or cnt=1;
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
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?
Respected sir I hope you are well .kinldy make a video for beginner to expert which course start for DB and which DBMS use. Which DBMS have a scope in future .plsease shear your experience in video .and tell step by step which course should first then second then third etc. And how we apply for job and which compney should apply through linked-in because each company required 2-3 year experiences.But we have don't experience. Kindly tell us a plate form or you tube channel link. Thanks a lot sir
I think simplest one select username,activity,startdate,enddate from (select *, row_number() over(partition by username order by startdate desc) as rn from Useractivity) where rn=2 union select username,activity,startdate,enddate from Useractivity group by username having count(username)=1
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 activity=second_most_recent_activty; Coalesce is used because for Joe null will be returned as it has only one row.
Thank you Tofiq, based on your explanation I can solve the problem as below, what do you think? Is it possible or? With cte_secondActivity As (Select *, row_number() over(partion by userName order by start date desc) as row_nr From user Activity) Select username, activity,startdate,start date, From cte_secondActivity Where row_nr=2
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)
Thanks for sharing TFQ, The question says the table does not contain Primary Key! Does that mean there could be two person with same name, there could be different records for same name, eg there could be two differrent person named Amy? Do we need to cosider this as an edge case?
Sir, Will you be able to provide solution for below question? We have two records and five columns. Actually there is Duplicate record in that two records but one column is having different name for two records. So, my question is how to write a SQL query to remove that duplicate record in the Dashboard?
Using rank instead of row_number select username, activity, startDate, endDate from (select *, case when max(rnk) over (partition by username) = 1 then 'x' when rnk = 2 then 'x' end as slct from (select *, rank() over (partition by username order by endDate desc) as rnk from UserActivity ) x ) y where y.slct is not null
Sir my solution in MySQL - select * from (SELECT uc.*, ROW_NUMBER() OVER(PARTITION BY username ORDER BY username,startDate) as row_count,count(*) OVER(PARTITION BY username) as total_count FROM `user_activity` uc) as x where x.row_count = 2 or (x.row_count = 1 and x.total_count = 1);
I'm getting the same results by running the following: if it's wrong, please let me know with cte as(select *,row_number() over(partition by username order by startdate desc) rn from activity1 qualify rn=2) select * exclude rn from cte union all select * from activity1 where username in (select username from activity1 group by username having count(*)=1);
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) )
with narsi as (select a.*,row_number()over(partition by username order by startdate desc) rn from activity1 a) select username,acitivity,startdate,enddate from narsi where rn=2 or username in(select username from narsi group by username having max(rn)=1);
Easier Solution: with cte as (select *, row_number() over (partition by username order by CURDATE() - endDate) as recent_day_number from activity_table) select * from cte where recent_day_number in (2) or username in (select username from activity_table group by username having count(*)
with cte as ( Select a.*, rank() over (partition by username order by startdate, enddate desc) as a_rank from activity1 a ) Select username,acitivity,startdate,enddate from cte where a_rank = 2 union all select username,acitivity,startdate,enddate from cte where username not in (Select username from cte where a_rank > 1);
with t2 as(select username, activity, startdate, enddate from(select *, rank() over (partition by username order by enddate desc) as recency from useractivity u) t1 where recency=2), t3 as (select * from useractivity u where username not in (select username from t2))
I use count and row_number logic : WITH t1 AS (SELECT *, Row_number() OVER( partition BY username ORDER BY enddate ) rn, Count(username) OVER( partition BY username) cc FROM activity1 ORDER BY username, enddate) SELECT * FROM t1 WHERE cc < 2 OR rn = 2
Sharing an alternative! with cte as (select username,activity,startDate,endDate,rank() over(partition by username order by startDate desc) as rnk from user_activity), table1 as (select username,activity,startDate,endDate,rnk,max(rnk) over(partition by username) as max_rnk from cte) select username,activity,startDate,endDate from table1 where rnk = 2 or (max_rnk = 1)
count(*) returns how many records are in the table, same for count(1), count(50), count(-1) doesn't matter the number, for count(number), it just counts total number of records in the table and assigns each record a number given in the count function. Count(column) returns total number of values in the column which are not null. Null is not included here.
for sql workbench users with cte as (SELECT concat(id,' ', name) as concat,ntile(4) over(order by id) as 'ntiles' FROM interviewquestion.emp) select group_concat(concat) as result from cte group by ntiles order by 1
Hello Thoufiq, Thanks for the video!! I have one question. In the problem statement it is written that a user cannot perform two activities at the same time. Suppose if table contains records with overlapping time periods, shouldn't that condition be checked as well and those records be discarded? Thanks Kamal
select * from (select *,row_number()over(partition by username order by (select 0) ) as rownum ,count (*) over (partition by username order by (select 0) ) as count from useractivity)useractivity where rownum= case when count =1 then 1 else count-1 end
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);
Solution - select * from (select username, activity , row_number() over(partition by username order by startdate desc) rn, count(*) over(partition by username order by startdate range between unbounded preceding and current row ) as c from user_activity ) X where rn=2 or c=rn
It's not necessary to complicate the COUNT function with RANGE: partitioning by username is adequate. The following query is simpler: WITH activity_recency AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY username ORDER BY startdate DESC) AS recency, COUNT(*) OVER (PARTITION BY username) AS activity_count FROM user_activity) SELECT username, activity, startdate, enddate FROM activity_recency WHERE recency = 2 OR activity_count = 1; This query can also easily be enhanced to return any nth most recent or the most recent activity if there aren't n activities.
@@jmhall1962 I mean without the RANGE clause, the most recent activity for each person would have activity_count = 1, so would those get returned even if they have more than one activity?
@@jcwynn4075 In my query, COUNT partitions by username but does include an ORDER BY clause. This approach results in each row having activity_count equal to the total number of activities for the associated user. This is simpler than adding a RANGE clause to undo the undesired behavior caused by including ORDER BY in the COUNT function.
If the database supports the LEAST function, then changing the WHERE clause to "WHERE recency = LEAST(2, activity_count)" permits querying for nth recency by changing a single number.
i am not getting why count - 1 because if i want second most value it should be (adam-Dancing Not Adam-singing) cnt - 1 is correct for amy but not for adam
I feel this is straight forward.. Kindly let me know if this approach has any drawbacks.... with intr as (select *,count(*) over (partition by username ) as cnt, rank() over (partition by username order by startDate desc ) as rnk from UserActivity), flag as (select *, case when cnt =1 then 'valid' when rnk =2 then 'valid' else 'invalid' end as flg from intr)
My solution to the problem -> 1. Get ranks based on start date partitioned by user name ordered by username and rank - store it as x, 2. select all rows from x that have have ranks < 3 and create a case when using lead() to see if the next row has the same username as current_row -> 1 if Yes, 2 if No - store it as X, 3. Select all rows from X with lead = 0
create table useractivity (username varchar(50), activity varchar(50), 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'); select Row_number() over(partition by username order by startdate asc) as row,* into #temp_table from useractivity select username,count(1) as count into #final from #temp_table group by username having count(1)>1 insert into #final select username,count(1) as count from #temp_table group by username having count(1)=1 select ff.username, ff.activity, ff.startdate, ff.enddate from #final f join #temp_table ff on f.username=ff.username and ff.row=1 and f.count=1 union all select ff.username, ff.activity, ff.startdate, ff.enddate from #final f join #temp_table ff on f.username=ff.username and ff.row=2 and f.count1
That is correct , question is mostly second recent that means from last 2nd record needs to be fetched , if we have count=2, second recent record is first record
select * from(select *, dense_rank() over(partition by username order by startDate desc) as rank, count(*) over(partition by username) as count from Google_UserActivity)x where rank=2 or count=1
more simple solution : WITH ranked_activities AS ( SELECT *, ROW_NUMBER() OVER(PARTITION BY username ORDER BY startdate desc) AS rn, COUNT(*) OVER(PARTITION BY username) AS count FROM UserActivity ) SELECT username, activity, startdate, enddate FROM ranked_activities WHERE rn=2 OR count=1
with cte as ( select *,LEAD(rn,1,0) over (partition by username order by (select null) ) as ld from ( select * from ( select *, row_number() over (partition by username order by (select null) ) as rn from useractivity) A)B where rn in (1,2)) select username,activity,startdate,enddate from cte where ld=0 /*please rate this query out of 10*/
Hi, Kindly review my code, improvement suggestions are highly solicited. Thank you. With master as ( Select *, Rank()over(partition by username order by endDate desc) as rank, Lead(endDate,1,0)over(partition by username order by endDate desc) as flag From UserActivity) Select Username, Activity, Startdate, Enddate From master Where rank =2 or flag = 0
There are at least two issues with this query. First is that 0 is not a date and the database (e.g., PostgreSQL) might not support casting it to a date. This could be addressed by allowing LEAD to default to NULL and checking for "flag IS NULL" instead of "flag = 0" in the WHERE clause. Second, even with this change, the query returns the wrong results. Both the first and second most recent activities satisfy the criteria for users with more than one activity.
@@jmhall1962 Thank you, I reckon i overlooked the conflict between 2nd most recent and 1st activity by user whilst using lead function. Herewith attached new iteration for review. I am finding my paces in the industry and improvement feedback is solicited for undertanding edge cases. With master as ( Select *, Rank()over(partition by username order by endDate desc) as rank, Count(*)over(partition by username) as flag From UserActivity) Select Username, Activity, Startdate, Enddate From master Where rank =2 or flag = 1
@@mithunkt1648 That query produces the correct results. Consider using more descriptive names. "Rank" and "flag" don't convey sufficient meaning. In another comment I posted a similar version of the query using "recency" and "activity_count" so that the meaning of the derived values can be easily understood by others.
@@jmhall1962 Thank you for the valuable inputs, I will bear in mind while writing queries now onwards. I am working on my data skills as I am working towards a pivot into the tech. Shout out to @techtfq team and taufeeq for this wonderful platform.
WITH cte1 AS ( SELECT *, RANK() OVER(PARTITION BY username ORDER BY startdate DESC) AS rnk, COUNT(*) OVER(PARTITION BY username ORDER BY startdate DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS total FROM activity) SELECT username, acitivity, startdate, enddate FROM cte1 WHERE rnk = 2 OR total = 1;
select username,ld as activity from( select *,ROW_NUMBER() over (partition by username order by startdate desc ) rnk FROM ( select *,lead(activity,1,activity) over (partition by username order by startdate desc) ld from [dbo].[UserActivity] )a) b where rnk =1
WITH cte AS(SELECT *, dense_rank()over (partition by username ORDER by enddate )rn, count(*) over (partition by username)cnt from useractivity) SELECT username,activity,startdate,enddate FROM cte WHERE rn = 2 or cnt =1
select username,activity,startdate,enddate from (select *,row_number() over(partition by username order by startdate desc) as rnk from useractivity) as z where rnk=2 union select * from useractivity where username in(select username from useractivity group by username having count(1)=1) solution given by SQL KING nikhil anand
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
;
IT'S ALWAYS THE UNDERRATED VID THAT'S LEGIT! THANK YOU!
Awesome mate👌🏻 God bless you
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;
i will go with this one much simpler
First Like... I'm eagerly waiting for your videos sir... Thanks 👍❤️
Thanks, TFQ
and love you boss the way you explain the query and solve
it's up to the mark ♥
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)
why count-1 not because there is one record with count 4 for this second most should be 2 but it is adam 3
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
Really appreciate for your sharing. 👍
Thank you so much this helped a lot!!!! You saved my life
Thank you, Thoufiq.
Thankyou very much sir for bringing this 🙏
Hey man, It works great and without any problems.
I need to work a lot of similar problems so partition and case becomes second nature. This is a great tutorial
Same if we mention DESC in order by clause of row number & then replace 'cnt-1' to '2'
Hii, your video are more useful, so please upload video about the topics like cluster, and indexex, thnq
Superb bro 👌 👏 👍
Very nice explanation
Fast download, thank you brother))
thanks for sharing.. love from india
Good to know about frame clause. Range one
Thank you sir
VERY INTERESTING
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
Awesome program
@TechTfq -I could see that you use window function effectively in most of the solutions. Just want to know if window function is good performancewise also... Thanks
super! cheatwas easily installed
Thanx
what application do you use to create the source code database?
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;
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
Continue.....
Thank you so much for sharing! In your solution, lines 4 & 5 could be simplified using:
, COUNT(*) OVER(partition by username) AS cnt
how would you order them then? by default ordering is asc
@@imyours777 You're COUNTING, so you don't need to order them. Of course, you need to order them but in function ROW_NUMBER() so it's gonna stay as it is:
--using mysql
WITH t AS (
select U.*,
ROW_NUMBER()
OVER (PARTITION BY username ORDER BY endDate DESC) as order_activity,
COUNT(*)
OVER (PARTITION BY username) as total_activities
from useractivity AS U
)
SELECT username, activity, startDate, endDate FROM t
WHERE (order_activity = 1 AND total_activities = 1)
OR (order_activity = 2 AND total_activities >= 2);
@@SantiagoZuluaga ya your logic doesnt need to order but in his logic we need to order to use cnt-1
great 👍
@@imyours777 with cte as (
select *,
row_number() over(partition by username order by startdate) as rn,
count(*) over(partition by username) as cnt
from useractivity )
select username,activity,startdate,enddate
from cte where rn = (case when cnt > 1 then cnt-1 else 1 end)
hi , I think for windows function: row_number(), it should be-> order by end_date desc.
@techtfq,, why dont we use u this way based on rowid,,,
select * from (select username,activity,startsate,count(*) over (partition by username) as cnt,row_number() over (partition by username order by rowid asc) rn from useractivity) where rn=2 or cnt=1;
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
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?
It can be..
yes
Yes. And then union those rows with count=1. 👍
Respected sir
I hope you are well .kinldy make a video for beginner to expert which course start for DB and which DBMS use. Which DBMS have a scope in future .plsease shear your experience in video .and tell step by step which course should first then second then third etc. And how we apply for job and which compney should apply through linked-in because each company required 2-3 year experiences.But we have don't experience. Kindly tell us a plate form or you tube channel link.
Thanks a lot sir
Hi, i have shared one query over mail .it is complex one can you please make a video on that one??
Thank you thoufiq
Thank you for alll the support Ahmed 🙏🏼
Appreciate it
@@techTFQ looking forward your upcoming python playlist InshaAllah
@@techTFQ thank you for sharing queries and explaining . Can you do a video on postgresql with json .
I think simplest one
select username,activity,startdate,enddate from (select *, row_number() over(partition by username order by startdate desc) as rn
from Useractivity)
where rn=2
union
select username,activity,startdate,enddate
from Useractivity
group by username
having count(username)=1
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 activity=second_most_recent_activty;
Coalesce is used because for Joe null will be returned as it has only one row.
Thank you Tofiq, based on your explanation I can solve the problem as below, what do you think? Is it possible or?
With cte_secondActivity
As
(Select *, row_number() over(partion by userName order by start date desc) as row_nr
From user Activity)
Select username, activity,startdate,start date,
From cte_secondActivity
Where row_nr=2
Sir.
Trigger, cursor aur user defined function ki video banayiye
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)
Hi.when it's 4 it has returned the third row but we still need only the second?
sir a kind request is that always share a data set so that we can also practice it
please make video on USER DEFINED FUNCTIONS in sql
Thanks for sharing TFQ, The question says the table does not contain Primary Key! Does that mean there could be two person with same name, there could be different records for same name, eg there could be two differrent person named Amy? Do we need to cosider this as an edge case?
I think that you could sniff out start date collisions.
Sir, Will you be able to provide solution for below question?
We have two records and five columns. Actually there is Duplicate record in that two records but one column is having different name for two records. So, my question is how to write a SQL query to remove that duplicate record in the Dashboard?
Can you make video about writing sql using github copilot
Please add the Data set for the practice
Using rank instead of row_number
select username, activity, startDate, endDate
from
(select *,
case
when max(rnk) over (partition by username) = 1 then 'x'
when rnk = 2 then 'x'
end as slct
from
(select *,
rank() over (partition by username order by endDate desc) as rnk
from UserActivity
) x
) y
where y.slct is not null
group by/having/count/limit/offset - probably 3 times shorter :D
Sir my solution in MySQL -
select * from
(SELECT uc.*, ROW_NUMBER() OVER(PARTITION BY username ORDER BY username,startDate) as row_count,count(*) OVER(PARTITION BY username) as total_count FROM `user_activity` uc) as x
where x.row_count = 2 or (x.row_count = 1 and x.total_count = 1);
I'm getting the same results by running the following:
if it's wrong, please let me know
with cte as(select *,row_number() over(partition by username order by startdate desc) rn from activity1
qualify rn=2)
select * exclude rn from cte
union all
select * from activity1 where username in (select username from activity1 group by username having count(*)=1);
Brother , sorting family members problem could u slove in MS SQL server.
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)
)
Hi Taufeeq,
I think if we go directly with original question then we can achieve result by using only row_number () , Right?
No. You still need count of records per username.
with narsi as (select a.*,row_number()over(partition by username order by startdate desc) rn from activity1 a)
select username,acitivity,startdate,enddate from narsi where rn=2 or
username in(select username from narsi group by username having max(rn)=1);
Easier Solution:
with cte as (select *, row_number() over (partition by username order by CURDATE() - endDate) as recent_day_number from activity_table)
select * from cte where recent_day_number in (2) or username in
(select username from activity_table group by username having count(*)
One more thing I added one row for Joe having recent activity than the given one. To make the given activity second recent.
with cte as (
Select a.*, rank() over (partition by username order by startdate, enddate desc) as a_rank from activity1 a
)
Select username,acitivity,startdate,enddate from cte where a_rank = 2
union all
select username,acitivity,startdate,enddate from cte where username not in (Select username from cte where a_rank > 1);
I think if one user has only 2 records. we need to consider another case also. cnt =2
with t2 as(select username, activity, startdate, enddate
from(select *, rank() over (partition by username order by enddate desc) as recency
from useractivity u) t1
where recency=2),
t3 as (select *
from useractivity u
where username not in (select username
from t2))
select *
from t2,t3
I use count and row_number logic :
WITH t1
AS (SELECT *,
Row_number()
OVER(
partition BY username
ORDER BY enddate ) rn,
Count(username)
OVER(
partition BY username) cc
FROM activity1
ORDER BY username,
enddate)
SELECT *
FROM t1
WHERE cc < 2
OR rn = 2
Sharing an alternative!
with cte as
(select username,activity,startDate,endDate,rank() over(partition by username order by startDate desc) as rnk
from user_activity),
table1 as
(select username,activity,startDate,endDate,rnk,max(rnk) over(partition by username) as max_rnk
from cte)
select username,activity,startDate,endDate
from table1
where rnk = 2 or (max_rnk = 1)
I think simple or clause would have done the job, like 'where rn=2 OR cnt=1'
Hi TFQ
May i know
What is the difference b/w count (*) and count(1) and count (column)?
count(*) returns how many records are in the table, same for count(1), count(50), count(-1) doesn't matter the number, for count(number), it just counts total number of records in the table and assigns each record a number given in the count function.
Count(column) returns total number of values in the column which are not null. Null is not included here.
for sql workbench users
with cte as
(SELECT concat(id,' ', name) as concat,ntile(4) over(order by id) as 'ntiles' FROM interviewquestion.emp)
select group_concat(concat) as result
from cte
group by ntiles
order by 1
I enrolled for the classes.. I haven’t got back anything yet
Hello Thoufiq,
Thanks for the video!!
I have one question.
In the problem statement it is written that a user cannot perform two activities at the same time. Suppose if table contains records with overlapping time periods, shouldn't that condition be checked as well and those records be discarded?
Thanks
Kamal
select * from
(select *,row_number()over(partition by username order by (select 0) ) as rownum
,count (*) over (partition by username order by (select 0) ) as count from useractivity)useractivity
where rownum= case when count =1 then 1 else count-1 end
Hi TFQ, i do have 6 years of experience in SQL ,which role is good ? DATA science or Analyst ,kindly make video on how to find remote jobs for SQL
Refer me any SQL related job. I am from Chennai
Data Engineer!
i didnt understand wat is this cte
For once, the software is actually really useful
👍🏼
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);
Solution -
select * from
(select username, activity , row_number()
over(partition by username order by startdate desc) rn,
count(*) over(partition by username order by startdate
range between unbounded preceding and current row ) as c
from user_activity
) X
where rn=2 or c=rn
It's not necessary to complicate the COUNT function with RANGE: partitioning by username is adequate. The following query is simpler:
WITH activity_recency AS
(SELECT *,
ROW_NUMBER() OVER (PARTITION BY username ORDER BY startdate DESC) AS recency,
COUNT(*) OVER (PARTITION BY username) AS activity_count
FROM user_activity)
SELECT username, activity, startdate, enddate
FROM activity_recency
WHERE recency = 2
OR activity_count = 1;
This query can also easily be enhanced to return any nth most recent or the most recent activity if there aren't n activities.
Would this return extra records though? For example where activity_count is 1 and recency is also 1
@@jcwynn4075, the WHERE clause filters existing rows, it can never create addition rows.
@@jmhall1962 I mean without the RANGE clause, the most recent activity for each person would have activity_count = 1, so would those get returned even if they have more than one activity?
@@jcwynn4075 In my query, COUNT partitions by username but does include an ORDER BY clause. This approach results in each row having activity_count equal to the total number of activities for the associated user. This is simpler than adding a RANGE clause to undo the undesired behavior caused by including ORDER BY in the COUNT function.
If the database supports the LEAST function, then changing the WHERE clause to "WHERE recency = LEAST(2, activity_count)" permits querying for nth recency by changing a single number.
i am not getting why count - 1 because if i want second most value it should be (adam-Dancing Not Adam-singing) cnt - 1 is correct for amy but not for adam
I feel this is straight forward.. Kindly let me know if this approach has any drawbacks....
with intr as (select *,count(*) over (partition by username ) as cnt,
rank() over (partition by username order by startDate desc ) as rnk
from UserActivity),
flag as (select *, case when cnt =1 then 'valid' when rnk =2 then 'valid' else 'invalid' end as flg from intr)
select * from flag where flg='valid'
please provide ddl scripts in video so that we can practise from ourself before watching your solution
liked
My solution to the problem -> 1. Get ranks based on start date partitioned by user name ordered by username and rank - store it as x, 2. select all rows from x that have have ranks < 3 and create a case when using lead() to see if the next row has the same username as current_row -> 1 if Yes, 2 if No - store it as X, 3. Select all rows from X with lead = 0
create table useractivity (username varchar(50), activity varchar(50), 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');
select Row_number() over(partition by username order by startdate asc) as row,* into #temp_table from useractivity
select username,count(1) as count into #final from #temp_table group by username
having count(1)>1
insert into #final
select username,count(1) as count from #temp_table group by username
having count(1)=1
select ff.username, ff.activity, ff.startdate, ff.enddate from #final f
join #temp_table ff on f.username=ff.username and ff.row=1 and f.count=1
union all
select ff.username, ff.activity, ff.startdate, ff.enddate from #final f
join #temp_table ff on f.username=ff.username and ff.row=2 and f.count1
Thanks much!
If count = 2, then cnt-1 would return the wrong value !!!, so you need to write a case for that one too. Pls reply
That is correct , question is mostly second recent that means from last 2nd record needs to be fetched , if we have count=2, second recent record is first record
If there are only 2 records, then the first record is the second most recent. How's it wrong?
Ohh okay!!!... I understand, thanks❤
select *
from(select *,
dense_rank() over(partition by username order by startDate desc) as rank,
count(*) over(partition by username) as count
from Google_UserActivity)x
where rank=2 or count=1
I think for adam 's second most activity is dancing right??
I have the same doubt,
Second most RECENT is singing. Same as saying second to last
more simple solution :
WITH ranked_activities AS (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY username ORDER BY startdate desc) AS rn,
COUNT(*) OVER(PARTITION BY username) AS count
FROM UserActivity
)
SELECT username, activity, startdate, enddate
FROM ranked_activities
WHERE rn=2 OR count=1
This seems like way too much work for a solution thah can be much simpler than this. Overcomplicating it into
with cte as (
select *,LEAD(rn,1,0) over (partition by username order by (select null) ) as ld from (
select * from (
select *, row_number() over (partition by username order by (select null) ) as rn
from useractivity) A)B
where rn in (1,2))
select username,activity,startdate,enddate from cte
where ld=0
/*please rate this query out of 10*/
Your output has not matched. Please check it once properly.
Hi,
Kindly review my code, improvement suggestions are highly solicited. Thank you.
With master as (
Select
*,
Rank()over(partition by username order by endDate desc) as rank,
Lead(endDate,1,0)over(partition by username order by endDate desc) as flag
From UserActivity)
Select
Username,
Activity,
Startdate,
Enddate
From master
Where rank =2 or flag = 0
There are at least two issues with this query. First is that 0 is not a date and the database (e.g., PostgreSQL) might not support casting it to a date. This could be addressed by allowing LEAD to default to NULL and checking for "flag IS NULL" instead of "flag = 0" in the WHERE clause. Second, even with this change, the query returns the wrong results. Both the first and second most recent activities satisfy the criteria for users with more than one activity.
@@jmhall1962 Thank you, I reckon i overlooked the conflict between 2nd most recent and 1st activity by user whilst using lead function.
Herewith attached new iteration for review. I am finding my paces in the industry and improvement feedback is solicited for undertanding edge cases.
With master as (
Select
*,
Rank()over(partition by username order by endDate desc) as rank,
Count(*)over(partition by username) as flag
From UserActivity)
Select
Username,
Activity,
Startdate,
Enddate
From master
Where rank =2 or flag = 1
@@mithunkt1648 That query produces the correct results. Consider using more descriptive names. "Rank" and "flag" don't convey sufficient meaning. In another comment I posted a similar version of the query using "recency" and "activity_count" so that the meaning of the derived values can be easily understood by others.
@@jmhall1962 Thank you for the valuable inputs, I will bear in mind while writing queries now onwards. I am working on my data skills as I am working towards a pivot into the tech.
Shout out to @techtfq team and taufeeq for this wonderful platform.
WITH cte1 AS (
SELECT *, RANK() OVER(PARTITION BY username ORDER BY startdate DESC) AS rnk,
COUNT(*) OVER(PARTITION BY username ORDER BY startdate DESC
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS total
FROM activity)
SELECT username, acitivity, startdate, enddate
FROM cte1
WHERE rnk = 2 OR total = 1;
select username,ld as activity from(
select *,ROW_NUMBER() over (partition by username order by startdate desc ) rnk
FROM (
select *,lead(activity,1,activity) over (partition by username order by startdate desc) ld
from [dbo].[UserActivity] )a) b
where rnk =1
WITH cte AS(SELECT *,
dense_rank()over (partition by username ORDER by enddate )rn,
count(*) over (partition by username)cnt
from useractivity)
SELECT username,activity,startdate,enddate FROM cte
WHERE rn = 2 or cnt =1
google sql query docs public
select username,activity,startdate,enddate from
(select *,row_number() over(partition by username order by startdate desc) as rnk
from useractivity) as z
where rnk=2
union
select * from useractivity where username in(select username from useractivity group by username having count(1)=1)
solution given by SQL KING nikhil anand
man I missed this kind of tutorials lol. Great work here, thanks!!!