How to get the top 10 members based nooftweets reported and also the logged in user rank

  CREATE PROCEDURE [dbo].[SP_GETLEADERBOARD]

 @UserId INT

 AS

 BEGIN

 

DECLARE @IsExist Int

IF EXISTS(Select * from OnGoingReports where UserId=@UserId)

BEGIN

SET @IsExist=1

END

ELSE

BEGIN

SET @IsExist=0

END

--gets the user name,tweetsreported,rank of top 10 users from ongoingreports table

select Top 10 UserId,Name,UserReportedTweetCount,ROW_NUMBER() OVER(ORDER BY UserReportedTweetCount desc) AS Rank 

from

(select ogr.UserId,Name,Count(*) UserReportedTweetCount from UserCredentials uc

inner join OnGoingReports ogr

on

ogr.UserId=uc.UserId 

where uc.IsActive=1

GROUP BY ogr.UserId,uc.Name) TopList  

UNION

   --if user reported then the if block executes and calculates rank based on tweets reported

select UserId,Name,UserReportedTweetCount,Rank from (select UserId,Name,UserReportedTweetCount,ROW_NUMBER() OVER(ORDER BY UserReportedTweetCount desc) AS Rank 

from

(select ogr.UserId,Name,Count(*) UserReportedTweetCount from UserCredentials uc

inner join OnGoingReports ogr

on

ogr.UserId=uc.UserId 

where uc.IsActive=1

GROUP BY ogr.UserId,uc.Name) as TopList) as UserRank where UserId=@UserId AND @IsExist=1

 

UNION

   --if user not reported any tweets then  we by default assigning 0 reported tweets and getting the rank of user

select UserId,Name,UserReportedTweetCount,Rank 

from(select  UserId,Name,UserReportedTweetCount,ROW_NUMBER() OVER(ORDER BY UserReportedTweetCount desc) AS Rank 

from

(select ogr.UserId,Name,Count(*) UserReportedTweetCount from UserCredentials uc

inner join OnGoingReports ogr

on

ogr.UserId=uc.UserId 

where uc.IsActive=1

GROUP BY ogr.UserId,uc.Name,uc.UserName

union

select uc.UserId,Name,0 UserReportedTweetCount from UserCredentials uc 

where UserId=@UserId and uc.IsActive=1

GROUP BY uc.UserId,uc.Name,uc.UserName 

) TopList)as UserRank  where UserId=@UserId AND @IsExist=0

 

END