title: distinct id from current month to previous month & missing id from previous month to current month
body: <p>I am trying to get the distinct ID's from current month compared to previous month and distinct missing ID's from previous month to current month. I have a table, where i have two fields namely, snapshotname and assetsrcsysid. I have loaded this with sample data for 201901, 201902 and 201812. Ideally, I have this data going back to 201401, but for trials i am doing this with 3 months of data.</p>
  
  <p>Please suggest how I can get this query improved such that it includes all months going back to 201401.</p>
  
  <p>I have used below query to get the snapshotname, incoming_sysid( which are new in current month that are not present in previous month) and outgoing_sysid( which are missing from previous month in current month) as below. I ran this query using PostgreSQL on windows 7 64 bit platform.</p>
  
  <pre><code>WITH incoming AS
    (SELECT snapshotname,
            count(DISTINCT assetsrcsysid) AS incoming_sysid
     FROM public.sampleassetsrcsysid
     WHERE snapshotname = '201901'
       AND assetsrcsysid NOT IN
         (SELECT assetsrcsysid
          FROM public.sampleassetsrcsysid
          WHERE snapshotname = '201812' )
     GROUP BY snapshotname),
  
       outgoing AS
    (SELECT snapshotname,
            count(DISTINCT assetsrcsysid) AS outgoing_sysid
     FROM public.sampleassetsrcsysid
     WHERE snapshotname = '201812'
       AND assetsrcsysid NOT IN
         (SELECT assetsrcsysid
          FROM public.sampleassetsrcsysid
          WHERE snapshotname = '201901' )
     GROUP BY snapshotname)
  
  SELECT incoming.snapshotname,
         incoming.incoming_sysid,
         outgoing.outgoing_sysid
  FROM incoming,
       outgoing
  WHERE 1=1;
  </code></pre>
  
  <p>expected results:</p>
  
  <pre><code>snapshotname  incoming_sysid  outgoing_sysid
  201902          4               3
  201901          3               5
  </code></pre>
  
  <p>Dummy Data:</p>
  
  <pre><code>snapshotname    assetsrcsysid
  201901  s1
  201901  s2
  201901  s3
  201901  s4
  201901  s5
  201901  s6
  201901  s15
  201812  s1
  201812  s2
  201812  s3
  201812  s4
  201812  s7
  201812  s9
  201812  s10
  201812  s11
  201812  s12
  201902  s1
  201902  s2
  201902  s3
  201902  s13
  201902  s17
  201902  s19
  201902  s20
  201902  s5
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/NKNAL.png" rel="nofollow noreferrer">enter image description here</a></p>