SQL count distinct over partition by cumulatively

I am using AWS Athena (Presto based) and I have this table named base:

I would like a query that counts the distinct values of the categories per id cumulatively per month and year but retaining the original columns, so I would like:

I’ve tried doing the following but they aren’t quite right:

SELECT id, category, year, month, COUNT(category) 
OVER (PARTITION BY id, ORDER BY year, month) AS sumC FROM base;

This results in 1, 2, 3, 1, 2, 3 which is not what I want; what I really need is count(distinct) however, that’s not supported in window function; so I also tried the dense_rank trick:

DENSE_RANK() OVER (PARTITION BY id ORDER BY category) 
+ DENSE_RANK() OVER (PARTITION BY id ORDER BY category) 
- 1 as sumC

but because there is no ordering of year and month, then it just results in 2, 2, 2, 2, 2, 2

appreciate any help, thanks!!

>Solution :

One option is

• creating a new column that will contain when each "category" is seen for the first time (partitioning on "id", "category" and ordering on "year", "month")
• computing a running sum over this column, with the same partition

WITH cte AS (
    SELECT *, 
           CASE WHEN ROW_NUMBER() OVER(
                         PARTITION BY id, category
                         ORDER     BY year, month) = 1
                THEN 1 
                ELSE 0 
           END AS rn1
    FROM base
    ORDER BY id, 
             year_, 
             month_
)
SELECT id,
       category,
       year_,
       month_,
       SUM(rn1) OVER(
            PARTITION BY id
            ORDER     BY year, month 
       ) AS sumC
FROM cte

Does it work for you?