title: how to create column based on other column in sql?
body: <pre><code>id  year
  1   2017
  1   2018
  1   2019
  2   2018
  2   2019
  3   2017
  3   2019
  8   2017   
  4   2018
  4   2019
  </code></pre>
  
  <p>I need to create column based on id and year column:</p>
  
  <ol>
  <li>if a id present in 2017 and 2018 (subsequent year) then mark 'P' against 2017.</li>
  <li>if a id present in 2018 and 2019 then mark 'P'  then mark 'P' against 2017.</li>
  <li>if a id present in 2017 but not in subsequent year then mark 'N' against 2017 </li>
  <li>If there is no data of subsequent year then mark 'N' in the previous year (2019)</li>
  </ol>
  
  <p>output :</p>
  
  <pre><code>id  year  mark
  1   2017  P
  1   2018  P
  1   2019  N
  2   2018  P
  2   2019  N
  3   2017  N
  3   2019  N
  8   2017  P
  4   2018  P
  4   2019  N
  </code></pre>