title: snowflake query to compare a row with all the previous row
body: <p>I have a dataset like below.</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>ID</th>
  <th>Date</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>100</td>
  <td>2022-03-01</td>
  </tr>
  <tr>
  <td>100</td>
  <td>2022-02-10</td>
  </tr>
  <tr>
  <td>100</td>
  <td>2021-12-15</td>
  </tr>
  <tr>
  <td>100</td>
  <td>2021-11-30</td>
  </tr>
  <tr>
  <td>200</td>
  <td>2021-08-05</td>
  </tr>
  <tr>
  <td>200</td>
  <td>2021-07-10</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>For a particular ID, I need to compare the previous rows to check if they exist in the previous months. So my output table should like below:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>ID</th>
  <th>Date</th>
  <th>1MonthAgo</th>
  <th>2MonthAgo</th>
  <th>3MonthAgo</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>100</td>
  <td>2022-03-01</td>
  <td>1</td>
  <td>0</td>
  <td>1</td>
  </tr>
  <tr>
  <td>100</td>
  <td>2022-02-10</td>
  <td>0</td>
  <td>1</td>
  <td>1</td>
  </tr>
  <tr>
  <td>100</td>
  <td>2021-12-15</td>
  <td>1</td>
  <td>0</td>
  <td>0</td>
  </tr>
  <tr>
  <td>100</td>
  <td>2021-11-30</td>
  <td>0</td>
  <td>0</td>
  <td>1</td>
  </tr>
  <tr>
  <td>200</td>
  <td>2021-08-05</td>
  <td>1</td>
  <td>0</td>
  <td>0</td>
  </tr>
  <tr>
  <td>200</td>
  <td>2021-07-10</td>
  <td>0</td>
  <td>0</td>
  <td>0</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I can use the LAG function but I think that one only works for one row before. For a particular ID, I need to look back 3 months if the ID exist or not. Don't need to do any days calculation just month is fine.</p>
  <p>Is there any way I can achieve this using sql?
  Appreciate any help on this.
  Thanks.</p>