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?

dezso
  • 28,436
  • 12
  • 89
  • 132
Singh
  • 151
  • 5
  • 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 02 '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 02 '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 – Singh Jul 02 '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 02 '15 at 10:35
  • 1
    Can you join to the third table in a statement after your 1st statement above? – James Anderson Jul 02 '15 at 10:35
  • Why didn't you saved the `registration` date in the user table? And if it's like this can you post your table configuration and some dataset from it? – oNare Jul 02 '15 at 10:47
  • @oNare thanks dear. registeration date is saved in a old table. The table I am working on is new table so only option for me to get the date from that table by making relationship – Singh Jul 03 '15 at 13:10

3 Answers3

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.

Ionic
  • 2,141
  • 12
  • 19
  • 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 – Singh Jul 03 '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 03 '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 ;
ypercubeᵀᴹ
  • 92,106
  • 13
  • 189
  • 284
Josh Simar
  • 425
  • 2
  • 12