title: SQL Sorted Count
body: <p>I have the following table sorted by date:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: center;">date</th>
  <th style="text-align: center;">id</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: center;">9/1/20</td>
  <td style="text-align: center;">1</td>
  </tr>
  <tr>
  <td style="text-align: center;">9/1/20</td>
  <td style="text-align: center;">2</td>
  </tr>
  <tr>
  <td style="text-align: center;">9/3/20</td>
  <td style="text-align: center;">1</td>
  </tr>
  <tr>
  <td style="text-align: center;">9/4/20</td>
  <td style="text-align: center;">3</td>
  </tr>
  <tr>
  <td style="text-align: center;">9/4/20</td>
  <td style="text-align: center;">2</td>
  </tr>
  <tr>
  <td style="text-align: center;">9/6/20</td>
  <td style="text-align: center;">1</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I'd like to add a count column for each id so that the first count for each id is the earliest date and latest date would receive the highest count for each id:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: center;">date</th>
  <th style="text-align: center;">id</th>
  <th style="text-align: center;">count</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: center;">9/1/20</td>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">1</td>
  </tr>
  <tr>
  <td style="text-align: center;">9/1/20</td>
  <td style="text-align: center;">2</td>
  <td style="text-align: center;">1</td>
  </tr>
  <tr>
  <td style="text-align: center;">9/3/20</td>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">2</td>
  </tr>
  <tr>
  <td style="text-align: center;">9/4/20</td>
  <td style="text-align: center;">3</td>
  <td style="text-align: center;">1</td>
  </tr>
  <tr>
  <td style="text-align: center;">9/4/20</td>
  <td style="text-align: center;">2</td>
  <td style="text-align: center;">2</td>
  </tr>
  <tr>
  <td style="text-align: center;">9/6/20</td>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">3</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>How can I structure my Postgresql query to assemble this count column?</p>