PostgreSQL ranking and related

Question

I have a table which stores userids and their points in different categories. The number of users will keep changing. The points will also keep changing (like the points in stackoverflow). So a user who logins in will see a dashboard which says, for each of the 3 categories - You have 850 points, there are 950 users ahead of you. This is the query I have now -

WITH USERS AS (
   SELECT COUNT(*) TOT 
   FROM user_pointS
) 
SELECT ' You have ' || points_cat1 ||' points and there are '|| tot-rnk || ' ahead of you '   
FROM (
    SELECT ID,  
           points_cat1, 
           rank() OVER (ORDER BY  points_cat1 DESC ) AS RNK 
    FROM user_pointS
  ) AS RANKED, 
    USERS
WHERE ID = 10

Is there a better way (performance-wise)? I will have to repeat this for the 3 columns?

Gordon Linoff · Accepted Answer · 2014-01-23T16:40:13.547

0

Well, you can do this without the CTE:

SELECT ' You have ' || points_cat1 ||' points and there are '|| tot-rnk || ' ahead of you '   
FROM (SELECT ID, points_cat1, 
              rank() OVER (ORDER BY points_cat1 DESC ) AS RNK ,
              count(*) over () as TOT
      FROM user_pointS
     ) RANKED
WHERE ID = 10;

You can do it for all three catagories at once:

SELECT ' You have ' || points_cat1 ||' points and there are '|| tot-rnk1 || ' ahead of you ',
       ' You have ' || points_cat2 ||' points and there are '|| tot-rnk2 || ' ahead of you ',
       ' You have ' || points_cat3 ||' points and there are '|| tot-rnk3 || ' ahead of you '

FROM (SELECT ID, points_cat1, points_cat2, points_cat3,
              rank() OVER (ORDER BY points_cat1 DESC ) AS RNK ,
              rank() OVER (ORDER BY points_cat2 DESC ) AS RNK1 ,
              rank() OVER (ORDER BY points_cat3 DESC ) AS RNK2 ,
              count(*) over () as TOT
      FROM user_pointS
     ) RANKED
WHERE ID = 10;

You can probably replace the tot-rnk with an inverse ranking:

 rank() OVER (ORDER BY points_cat1 ASC ) AS RNK

but you would want to test that to be sure it gives you the results you expect.

edited Jan 23 '14 at 16:40

answered Jan 23 '14 at 11:45

Gordon Linoff

1,242,037
58
646
786

The points_cat1,2,3 in the outer select should be rnk,rnk1,rnk2. Other than that, this works. Thanks. – Jayadevan Jan 23 '14 at 13:27
@Jayadevan . . . The text say you have so many points not anything about rank. The second part uses the `rnk` variables. However, it is your query so you can put in what you want. – Gordon Linoff Jan 23 '14 at 13:34
When I used the query as you posted, I got ERROR: column "points_cat2" does not exist , since we don't have that column in the inner SELECT. So I should add those columns also to the inner select. – Jayadevan Jan 23 '14 at 14:02
@Jayadevan . . . I wasn't even thinking about that part of the query. That's just because the subquery didn't select those columns from the original table. See the change I just made. – Gordon Linoff Jan 23 '14 at 16:40

PostgreSQL ranking and related

1 Answers1