SQL - get summary of differences vs previous month

Question

I have a table similar to this one:

| id | store | BOMdate    |
| 1  |  A    | 01/10/2018 |
| 1  |  B    | 01/10/2018 |
| 1  |  C    | 01/10/2018 |
|... |  ...  |    ...     |
| 1  |  A    | 01/11/2018 |
| 1  |  C    | 01/11/2018 |
| 1  |  D    | 01/11/2018 |
|... |  ...  |    ...     |
| 1  |  B    | 01/12/2018 |
| 1  |  C    | 01/12/2018 |
| 1  |  E    | 01/12/2018 |

It contains the stores that are active at BOM (beginning of month).

How do I query it to get the amount of stores that are new that month - those that where not active the previous month?

The output should be this:

| BOMdate    | #newstores |
| 01/10/2018 |     3      | * no stores on previous month
| 01/11/2018 |     1      | * D is the only new active store
| 01/12/2018 |     2      | * store B was not active on November, E is new

I now how to count the first time that each store is active (nested select, taking the MIN(BOMdate) and then counting). But I have no idea how to check each month vs its previous month.

I use SQL Server, but I am interested in the differences in other platforms if there are any.

Thanks

Answer 1

How do I query it to get the amount of stores that are new that month - those that where not active the previous month?

One option uses not exists:

select bomdate, count(*) cnt_new_stores
from mytable t
where not exists (
    select 1 
    from mytable t1 
    where t1.store = t.store and t1.bomdate = dateadd(month, -1, t.bomdate)
)
group by bomdate

You can also use window functions:

select bomdate, count(*) cnt_new_stores
from (
    select t.*, lag(bomdate) over(partition by store order by bomdate) lag_bomdate
    from mytable t
) t
where bomdate <> dateadd(month, 1, lag_bomdate) or lag_bomdate is null
group by bomdate