title: Aggregating (Non-running) Weekly Sums to Create a Wide Table (SQL)
body: <p>The goal of this project is to show the weekly aggregate (and individual) performance of specific groups of people as they perform in a certain program using three metrics.</p>
  <p>I am currently working with two tables. The first table contains all the individuals, what group they belong to, and their starting date in the program (we may assume all members of one group share a starting date)</p>
  <p>The second table also contains these individuals, but also stores daily entries of how the individual performed for a specific metric. i.e.</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>person_id</th>
  <th>metric</th>
  <th>score</th>
  <th>date</th>
  <th></th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1000</td>
  <td>Sales</td>
  <td>0.8</td>
  <td>2022-02-28</td>
  <td></td>
  </tr>
  <tr>
  <td>1000</td>
  <td>Sales</td>
  <td>0.9</td>
  <td>2022-02-30</td>
  <td></td>
  </tr>
  <tr>
  <td>1000</td>
  <td>Upsell</td>
  <td>12</td>
  <td>2022-03-12</td>
  <td></td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I want to create a 10 week table which shows the weekly performance of an individual and a specific metric beginning at that individuals starting date. I would also like to calculate a baseline performance which is defined as the performance 28 days prior to starting.
  i.e</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>person_id</th>
  <th>metric</th>
  <th>baseline</th>
  <th>w1</th>
  <th>w2...</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1000</td>
  <td>Sales</td>
  <td>0.5</td>
  <td>0.6</td>
  <td>0.9</td>
  </tr>
  <tr>
  <td>1000</td>
  <td>Upsell</td>
  <td>0.6</td>
  <td>0.7</td>
  <td>0.4</td>
  </tr>
  <tr>
  <td>1001</td>
  <td>Sales</td>
  <td>0.8</td>
  <td>0.8</td>
  <td>0.9</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>First, I attempted to use a window function to calculate each week's sum with date_trunc('week', date), but I am unable to calculate the proper ranges within prestoDB. I also was considering using normal aggregation and grouping, then trying to pivot the table, but I am quite new at SQL and could not figure it out.</p>
  <p>If anyone has any suggestions to do this or a better way to implement this, please let me know!</p>