title: Counting number of medals in weekly tournaments
body: <p>I have a table holding weekly scores of players:</p>
  
  <pre><code># 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)
  </code></pre>
  
  <p>This SQL statement gets me the weekly winners and how many times each player has won:</p>
  
  <pre><code># 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
  ....
  </code></pre>
  
  <p>I would like to save the latter number in the <code>medals</code> column of the players table:</p>
  
  <pre><code># \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
  </code></pre>
  
  <p>How to do this please? I can only think of using a temporary table, but there must be an easier way... Thank you</p>
  
  <p><strong>UPDATE:</strong></p>
  
  <p>The query suggested by Clodoaldo works, but now my cronjob occasionally fails with:</p>
  
  <pre><code>/* 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 &lt;&gt; to_char(CURRENT_TIMESTAMP, 'IYYY-IW')
      ) x
      where ranking = 1
      group by id
  ) s
  where u.id = s.id;
  </code></pre>