Sum of points and rank according to points

Question

I am creating a game where I am calculating the user points on the basis of user activities. Everything is working fine - I got the sum of points and on the basis of points I calculated the rank. I have two columns for points calculation: pointsup (contains up points) and pointsdown (contains down points)

select sum(pointsup - pointsdown) as points 
from table1 t1
join table2 t2 
    on t2.Id = t1.id
where t1.ActivityTime BETWEEN '2015-01-01' AND '2016-01-01' 
group by t1.id 
order by points desc

Everything is fine, but the problem comes when I have to rank the users with same points. In this case, I have to consider the registration date: who registers first will come up first on the ranking. The registration date is in another table table3. I have tried to join this third table but the sum is behaving oddly, showing wrong sum calculation.

Can anyone help please?

Answer 1

Try this query

select a.*,b.points from table3 a
join(
select Sum(PointsUp-PointsDown) as points,t1.id
from table1 t1
  join table2 t2 on t2.Id = t1.id
where t1.ActivityTime BETWEEN '2015-01-01' AND '2016-01-01'
Group By t1.Id)b on a.id=b.id
order by b.points desc

Answer 2

Maybe I don't get your question right. But here is code for such a ranking. You may need to adapt it to your use-case.

-- Create demo data
CREATE TABLE #users(user_id int identity(1,1), points int, registration datetime)

INSERT INTO #users(registration)
VALUES  (GETDATE()), -- 1: first, because of highest points
        (GETDATE()), -- 2: fourth
        (GETDATE()), -- 3: third
        (DATEADD(day,-1,GETDATE())) -- 4: second, as first registration

CREATE TABLE #points(user_id int, points_up int, points_down int)

INSERT INTO #points(user_id, points_up, points_down)
VALUES(1,12,2),(2,5,2),(3,11,6),(4,5,0)

-- Your part
SELECT u.user_id, SUM(p.points_up-p.points_down) as points, u.registration, RANK() OVER(ORDER BY SUM(p.points_up-p.points_down) DESC, registration)
FROM #users as u
INNER JOIN #points as p
        ON u.user_id = p.user_id
GROUP BY u.user_id, u.registration

-- Cleanup
DROP TABLE #users
DROP TABLE #points

I just use the build in ranking function based on the achieved points and if the points are equal based on the registration date.

Answer 3

Like someone said before, use it in 2 queries.

select t1.id, sum(pointsup - pointsdown) as points 
INTO #points
from table1 t1
join table2 t2 
    on t2.Id = t1.id
where t1.ActivityTime BETWEEN '2015-01-01' AND '2016-01-01' 
group by t1.id ;

select 
DISTINCT
    p.*
FROM #points p
INNER JOIN joindate j ON p.id=j.id
ORDER BY p.points, j.joindate ;