Get the running unique count of items till a give date, similar to running total but instead a running unique count

Question

I have a table with user shopping data as shown below

I want an output similar to running total but instead I want the running total of the count of unique categories that the user has shopped for by date.

I know I have to make use of ROWS PRECEDING AND FOLLOWING in the count function but I am not able to user count(distinct category) in a window function

Dt  category    userId
4/10/2022   Grocery 123
4/11/2022   Grocery 123
4/12/2022   MISC    123
4/13/2022   SERVICES    123
4/14/2022   RETAIl  123
4/15/2022   TRANSP  123
4/20/2022   GROCERY 123

Desired output

Dt  userID  number of unique categories
4/10/2022   123 1
4/11/2022   123 1
4/12/2022   123 2
4/13/2022   123 3
4/14/2022   123 4
4/15/2022   123 5
4/20/2022   123 5

Answer 1

Consider below approach

select Dt, userId, 
  ( select count(distinct category)
    from t.categories as category
  ) number_of_unique_categories
from (
select *, array_agg(lower(category)) over(partition by userId order by Dt) categories
from your_table
) t               

if applied to sample data in your question - output is