Counting the number of medals in weekly tournaments

Question

I have a table with weekly player counts:

# select * from pref_money limit 5; id | money | yw ----------------+-------+--------- OK32378280203 | -27 | 2010-44 OK274037315447 | -56 | 2010-44 OK19644992852 | 8 | 2010-44 OK21807961329 | 114 | 2010-44 FB1845091917 | 774 | 2010-44 (5 rows) 

This SQL statement gets me weekly winners and how many times each player won:

 # select x.id, count(x.id) from ( select id, row_number() over(partition by yw order by money desc) as ranking from pref_money ) x where x.ranking = 1 group by x.id; id | count ------------------------+------- OK9521784953 | 1 OK356310219480 | 1 MR797911753357391363 | 1 OK135366127143 | 1 OK314685454941 | 1 OK308121034308 | 1 OK4087658302 | 5 OK452217781481 | 6 .... 

I would like to keep the last number in the medals column of the players table:

 # \d pref_users; Table "public.pref_users" Column | Type | Modifiers ------------+-----------------------------+-------------------- id | character varying(32) | not null first_name | character varying(64) | last_name | character varying(64) | city | character varying(64) | medals | integer | not null default 0 

How to do this, please? I can only think about using a temporary table, but there should be an easier way ... Thanks.

UPDATE:

The query suggested by Clodoaldo works, but now my cronjob sometimes fails:

 /* reset and then update medals count */ update pref_users set medals = 0; psql:/home/afarber/bin/clean-database.sql:63: ERROR: deadlock detected DETAIL: Process 31072 waits for ShareLock on transaction 124735679; blocked by process 30368. Process 30368 waits for ShareLock on transaction 124735675; blocked by process 31072. HINT: See server log for query details. update pref_users u set medals = s.medals from ( select id, count(id) medals from ( select id, row_number() over(partition by yw order by money desc) as ranking from pref_money where yw <> to_char(CURRENT_TIMESTAMP, 'IYYY-IW') ) x where ranking = 1 group by id ) s where u.id = s.id; 

Answer 1

 update pref_users u set medals = s.medals from ( select id, count(id) medals from ( select id, row_number() over(partition by yw order by money desc) as ranking from pref_money ) x where ranking = 1 group by id ) s where u.id = s.id 

Answer 2

You can create a view that uses your “medal of choice” and connects it to the actual data:

 CREATE VIEW pref_money_medals AS SELECT * FROM pref_money JOIN (SELECT count(x.id) FROM (SELECT id, row_number() OVER(PARTITION BY yw ORDER BY money DESC) AS ranking FROM pref_money ) x WHERE x.ranking = 1 group by x.id) medals ON pref_money.id = medals.id;