title: Count daily fidelity
body: <p>I have the below table and I would like to count, day by day, the number of distinct people who logged in everyday. For example, for day 1, everyone logged in, so it's 4. For day 4, there's just one person ID who logged in everyday since day 1, so the count would be 1.</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>DAY</th>
  <th>PERSON_ID</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>01</td>
  </tr>
  <tr>
  <td>1</td>
  <td>02</td>
  </tr>
  <tr>
  <td>1</td>
  <td>03</td>
  </tr>
  <tr>
  <td>1</td>
  <td>04</td>
  </tr>
  <tr>
  <td>2</td>
  <td>01</td>
  </tr>
  <tr>
  <td>2</td>
  <td>02</td>
  </tr>
  <tr>
  <td>2</td>
  <td>03</td>
  </tr>
  <tr>
  <td>3</td>
  <td>01</td>
  </tr>
  <tr>
  <td>4</td>
  <td>02</td>
  </tr>
  <tr>
  <td>4</td>
  <td>01</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Expected output.</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>DAY</th>
  <th>PEOPLE_LOGGED_EVERYDAY</th>
  <th>PEOPLE</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>4</td>
  <td>01, 02, 03, 04</td>
  </tr>
  <tr>
  <td>2</td>
  <td>3</td>
  <td>01, 02, 03</td>
  </tr>
  <tr>
  <td>3</td>
  <td>1</td>
  <td>01</td>
  </tr>
  <tr>
  <td>4</td>
  <td>1</td>
  <td>01</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>EDIT: the query should also work on the below data.</p>
  <pre><code>with t ( DAY, PERSON_ID ) AS(
      SELECT 10, '01' FROM DUAL UNION ALL
      SELECT 10, '02' FROM DUAL UNION ALL
      SELECT 10, '04' FROM DUAL UNION ALL
      SELECT 10, '04' FROM DUAL UNION ALL
      SELECT 12, '01' FROM DUAL UNION ALL
      SELECT 12, '02' FROM DUAL UNION ALL
      SELECT 12, '03' FROM DUAL UNION ALL
      SELECT 13, '04' FROM DUAL UNION ALL
      SELECT 13, '01' FROM DUAL UNION ALL
      SELECT 14, '02' FROM DUAL UNION ALL
      SELECT 14, '01' FROM DUAL)
  </code></pre>
  <p>Expected output:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>DAY</th>
  <th>PEOPLE_LOGGED_EVERYDAY</th>
  <th>PEOPLE</th>
  <th>EXPLANATION</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>10</td>
  <td>3</td>
  <td>01, 02, 04</td>
  <td>Three unique people in day 10</td>
  </tr>
  <tr>
  <td>12</td>
  <td>2</td>
  <td>01, 02</td>
  <td>Day 11 does not have values, so it's not included. From those in day 10, only 2 appear in day 12</td>
  </tr>
  <tr>
  <td>13</td>
  <td>1</td>
  <td>01</td>
  <td>From those in day 10 and 12, only 01 appears in day 13</td>
  </tr>
  <tr>
  <td>14</td>
  <td>1</td>
  <td>01</td>
  <td>From those in day 10, 12 and 13, only 01 appears in day 14</td>
  </tr>
  </tbody>
  </table>
  </div>