How to count distinct a field cumulatively using recursive cte or other method in SQL?

Question

Using example below, Day 1 will have 1,3,3 distinct name(s) for A,B,C respectively. When calculating distinct name(s) for each house on Day 2, data up to Day 2 is used. When calculating distinct name(s) for each house on Day 3, data up to Day 3 is used.

Can recursive cte be used?

Data:

Day	House	Name
1	A	Jack
1	B	Pop
1	C	Anna
1	C	Dew
1	C	Franco
2	A	Jon
2	B	May
2	C	Anna
3	A	Jon
3	B	Ken
3	C	Dew
3	C	Dew

Result:

Day	House	Distinct names
1	A	1
1	B	1
1	C	3
2	A	2 (jack and jon)
2	B	2
2	C	3
3	A	2 (jack and jon)
3	B	3
3	C	3

Tag your question with the database you are using. – Gordon Linoff Jul 14 '21 at 18:21 — Gordon Linoff, Jul 14 '21 at 18:21

score 2 · Answer 1 · answered Jul 14 '21 at 18:20

2

There is no need for a recursive CTE. Just mark the first time a name is seen in a house and use a cumulative sum:

select day, house,
       sum(sum(case when seqnum = 1 then 1 else 0 end)) over (partition by house order by day) as num_unique_names
from (select t.*,
             row_number() over (partition by house, name order by day) as seqnum
      from t
     ) t
group by day, house

answered Jul 14 '21 at 18:20

Gordon Linoff

1,242,037
58
646
786

Turns out that my use case is a little different and use both of Sai Puli's and yours :) upvoted yours – jasonlcy91 Jul 16 '21 at 11:53
@jasonlcy91 . . . This should be, by far, the more performant solution. – Gordon Linoff Jul 16 '21 at 12:10

Sai Puli · Accepted Answer · 2021-07-14T22:28:48.000

2

Without knowing the need and size of data it'll be hard to give an ideal/optimal solution. Assuming a small dataset needing a quick and dirty way to calculate, just use sub query like this...

SELECT p.[Day]
    , p.House
    , (SELECT COUNT(DISTINCT([Name]))
        FROM #Bing
        WHERE [Day]<= p.[Day] AND House = p.House) DistinctNames
FROM #Bing p
GROUP BY [Day], House
ORDER BY 1

edited Jul 14 '21 at 22:28

answered Jul 14 '21 at 19:01

Sai Puli

951
8
12

How to count distinct a field cumulatively using recursive cte or other method in SQL?

2 Answers2