CODEWITHSUNDEEP
phpmysqlsql
user1542894
user1542894

Reputation: 105

Calculating score based on rank position

I have a table jackpot with columns uid for user ID and nright for number of right answers.

I manage to SELECT and rank users by right answers, but what next?

SELECT
    a1.uid,
    a1.nright,
    COUNT(a2.nright) AS rank
FROM
    jackpot a1,
    jackpot a2
WHERE
    a1.nright < a2.nright
OR (
    a1.nright = a2.nright
    AND a1.uid = a2.uid
)
GROUP BY
    a1.uid,
    a1.nright
ORDER BY
    a1.nright DESC,
    a1.uid DESC

I need to calculate the amount of points to give to each user depending on his position.

Only users with top 3 MAX nright receive points.

The total amount of points = the number of users*20.

First position gets 70% of the total, 2nd - 20%, 3rd - 10%.

In case of equal right answers between users, the points are split evenly (50/50, 33/33/33...).

SQL Fiddle

Upvotes: 0

Views: 626

Answers (1)

Daniel E.
Daniel E.

Reputation: 2480

You need to decompose what you want.

1st step : You want the top 3 scores.

SELECT nright 
FROM jackpot
ORDER BY nright DESC
LIMIT 3

2nd step : The user id who gets this 3 first scores

SELECT j.uid
FROM jackpot j
INNER JOIN (
   SELECT nright 
   FROM jackpot
   ORDER BY nright DESC
   LIMIT 3 ) AS t ON t.nright = j.nright

3rd step: the total amount of point

SELECT COUNT(uid)*20 AS lot FROM jackpot

4th step: the rank and the number of person

Here we need to use a variable, as you are in php, you can't use set @var:= X; , so the trick is to do a Select @var:= X , this variable will not work because of the aggregate functions. So you need to do this :

SELECT  @rank := @rank+1 as rank,T1.nright,T1.nb,T1.lot
FROM(
    SELECT nright,
          COUNT(uid) as nb,
         (SELECT COUNT(uid)*20 FROM jackpot) as lot
   FROM jackpot
   GROUP BY nright
   ORDER BY nright DESC
   LIMIT 3
   )T1, (SELECT @rank:= 0) r

5th step: The lots distribution

 SELECT j.uid,
  CASE 
  WHEN t.rank = 1 THEN (t.lot*0.7)/t.nb
  WHEN t.rank = 2 THEN (t.lot*0.2)/t.nb
  WHEN t.rank = 3 THEN (t.lot*0.1)/t.nb
  END as lot

 FROM jackpot j
 INNER JOIN 
(SELECT  @rank := @rank+1 as rank,T1.nright,T1.nb,T1.lot
FROM(
    SELECT nright,
          COUNT(uid) as nb,
         (SELECT COUNT(uid)*20 FROM jackpot) as lot
   FROM jackpot
   GROUP BY nright
   ORDER BY nright DESC
   LIMIT 3
   )T1, (SELECT @rank:= 0) r) t ON t.nright = j.nright

Upvotes: 2

Related Questions