SQL statement to query new buyers on rolling basis

I currently have a order table that looks like this:

purchase_date | item_id | item_name | user_id
2/3/2020      | 123     | abc       | 987
3/3/2020      | 123     | abc       | 987
3/3/2020      | 123     | abc       | 876
3/3/2020      | 234     | bcd       | 987
...

I have been trying to create an SQL statement that will return something like this by doing a count(distinct user_id) :

purchase_date | item_id | item_name | number of buyers new to item
2/3/2020      | 123     | abc       | 1 
3/3/2020      | 123     | abc       | 1
3/3/2020      | 234     | bcd       | 1

Of course, there will be multiple item_ids in the order table.

What I’m trying to achieve is to obtain the rolling number of buyers that have never bought that specific item before on each day.

For example, based on the above order table example, there is 1 buyer one 2/3/2020, and 2 buyers on 3/3/2020. However, User_id 987 has bought the same item on 2/3/2020, therefore I do not want to count this user, thus end result for 3/3/2020 should only be 1 user.

User_id 987 will still count for item_id 234 as this user has not bought this item before in the orders table.

Items in the orders table will get refreshed every Monday, so I’m trying to build a query that will daily and return me the rolling numbers of buyers that new to each specific item (i.e. have not bought it before), from Monday till Sunday.

Is this a concept of using CROSS JOIN, or temporary tables in some way or another, or is this a way more complicated idea than I expected, to execute it in a SQL query format?

Thank you!

Answer

Use two levels of aggregation:

select first_pd, item_id, item_name, count(*) as num_first_purchases
from (select user_id, item_id, item_name, min(purchase_date) as first_pd
      from t
      group by user_id, item_id, item_name
     ) ui
group by first_pd, item_id, item_name
order by first_pd, item_id, item_name;