need help with this query

Question

This will be in MySQL. Table consists of following fields:

Date, ID, blah, blahblah, blahblahblah

I believe the primary key needs to be Date & ID.

The query I want to run is for every Date, count all the IDs but only if this is the last date the ID shows up.

So if the ID shows up in the database on 9/9/16 but NOT on 9/10/16 then we would say that it disappeared and therefore add it to the total count for date 9/9/16.

If that same ID later reappears on 9/11/16, when we run the query again that ID will no longer be added to the count as of 9/9/16. If on 9/12/16 that ID disappears again then it would be added to the count as of 9/11/16.

Here's some sample data. I did not put any data in for the "blahs" since it is not relevant for the query (I believe):

Date,   ID, blah,   blahblah,   blahblahblah
9/8/2016,   1           
9/8/2016,   2           
9/8/2016,   3           
9/8/2016,   4           
9/8/2016,   5           
9/8/2016,   6           
9/8/2016,   7           
9/8/2016,   8           
9/9/2016,   1           
9/9/2016,   2           
9/9/2016,   3           
9/9/2016,   4           
9/9/2016,   5           
9/9/2016,   9           
9/9/2016,   10          
9/9/2016,   11          
9/10/2016,  2           
9/10/2016,  4           
9/10/2016,  6           
9/10/2016,  11          
9/10/2016,  12          
9/10/2016,  13          

The expected output run on 9/9/16 would be:

Date, Total IDs Last Day
9/8/16,3
9/9/16,8

The expected output run on 9/10/16 would be:

Date, Total IDs Last Day
9/8/16,2
9/9/16,4
9/10/16,6

Note that when the query is run on 9/10/16, the total for 9/8/16 is different from when it was run on 9/9/16 because an ID (number 6) disappeared but then re-appeared.

Answer 1

One option would be to use a not exists to filter out any rows that appear for a later date.

select date, count(*)
  from your_table a
 where not exists( select 1
                     from your_table b
                    where a.id = b.id
                      and b.date > a.date )
 group by date