--DAY 6
--#15DAYSCHALLENGE

--Second most recent activity ,if there is only one activity is there it should also reflect 

--Letscode QUESTION
create table UserActivity
(
username      varchar(20) ,
activity      varchar(20),
startDate     Date   ,
endDate      Date
);
insert into UserActivity values 
('Alice','Travel','2020-02-12','2020-02-20')
,('Alice','Dancing','2020-02-21','2020-02-23')
,('Alice','Travel','2020-02-24','2020-02-28')
,('Bob','Travel','2020-02-11','2020-02-18');

------------------------------------SOLUTION-----------------------------------------------------

SELECT REPLICATE('*',150) AS 'INPUT_TABLE'

SELECT * FROM UserActivity

SELECT REPLICATE('*',150) AS 'SOLUTION START'

SELECT A.username,A.activity,A.startDate,A.endDate  FROM 
(
SELECT  U.*,DENSE_RANK () OVER (PARTITION BY U.username ORDER BY U.startDate ) as 'Activity_RANK'  FROM UserActivity U
)A
WHERE A.Activity_RANK=2 
UNION ALL
SELECT B.username,B.activity,B.startDate,B.endDate FROM UserActivity B WHERE B.username IN 
(
SELECT Z.username as 'Activity_COUNT_Z'  FROM UserActivity Z
GROUP BY Z.username
HAVING COUNT (1)=1

)

SELECT REPLICATE('*',150) AS 'END OF SOLUTION'