Shivam Sharma Shivam Sharma - 9 months ago 54
SQL Question

Reward points by looking at the already created fields

I have a table here :
enter image description here

I want to reward a gold and a silver(i.e a value of 1 wherever applicable,else 0 )to top 2 persons by looking at pointsRewarded field.

I already have the first table created.I want a new table with the two new fields i.e the gold and silver fields.

i want the output to be something like this:
enter image description here

Please help me with the query or give me some suggestions on how to proceed.

Thanks a lot.


I think you want to use dense_rank() for this:

select t.*,
       (case when rnk = 1 then 1 else 0 end) as gold,
       (case when rnk = 2 then 1 else 0 end) as silver
from (select t.*,
             dense_rank() over (partition by week order by pointsrewarded) as rnk
      from t
     ) t;

dense_rank() will handle the case when there are ties. In that case, multiple "gold" and "silver" values will be assigned.

I should also note that the subquery is not necessary. You can repeat the dense_rank() in the outer query. I just think it is easier to follow the logic this way.