Querying an SQL table to get all new values in a column according to filter in another column

Sorry I couldn’t make the question clearer, let me explain it here.

I have a table that has two columns:

year, ID

------------

2013, 01

2013, 27

2013, 33

2014, 22

2014, 33

2014, 01

2014, 13

2015, 45

2015, 13

2015, 22

What I want to do is the following: check all new IDs that appeared in 2014 from 2013, and all new IDs that appeared in 2015 from 2014, and so on and so forth…

So, from the example above, the expected answer to my query should be a table like:

year, new
-------------

2014, 2

2015, 1

Since in 2014 there are two new IDs in relation to 2013 (22 and 13), and in 2015 there is one new ID in relation to 2014 (45).

Sorry for the horrible formatting, I’m new to this site. Any help is appreciated, thanks.

Answer

If I understand correctly, you want lag():

select year, count(*)
from (select t.*, lag(year) over (partition by id order by year) as prev_year
      from t
     ) t
where prev_year is null or prev_year < year - 1
group by year;