3

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?

  • It's difficult to say anything in particular of cardinality without knowing the keys for the tables involved. Can you add details about that to your question? – Lennart Jul 2 '15 at 10:27
  • The 3rd join is probably creating duplicates. Remove the sum and group by and you will be able to see what is causing the incorrect sums – James Anderson Jul 2 '15 at 10:29
  • @JamesAnderson Yes thisrd joing of table is creating dupliactes but i have to get one field from third table i.e. registeration date of user, in case of same points i have to order by date of registeration – Kulbir Singh Jul 2 '15 at 10:32
  • 2
    Do you really call your tables table1 and so on? Anyway, showing some example data and the wrong output (with the wrong query) could help a lot in getting an answer. Otherwise, it's just guesswork (already done by @JamesAnderson) to figure out what the odd behaviour can mean. – dezso Jul 2 '15 at 10:35
  • 1
    Can you join to the third table in a statement after your 1st statement above? – James Anderson Jul 2 '15 at 10:35
4

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
3

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.

  • thanks but i have third table too that is from where i have to get a date column that i will use for ordering in case of same points – Kulbir Singh Jul 3 '15 at 13:12
  • Well you can add the third table too, if needed. But It depends on the table design. I don't know why your having a third one. :-) – Ionic Jul 3 '15 at 13:27
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 ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.