Get Distinct ID's in the last month grouped by date

Question

I have a table with two columns EventDate and ClientID ( both are not unique)

EventDate	ClientID
2022-02-28	A101
2022-02-28	A102
2022-02-28	A101
2022-02-28	A103
2022-02-27	A101
2022-02-01	A103

I am looking get an output showing the EventDate and Count Distinct ClientID in the last 30 days for each EventDate.

EventDate	User_Count
2022-02-28	3
2022-02-27	2
2022-02-01	1

I tried to do a self (left) join on the table, which works. But since the table is large (around 2Mn rows), the self join takes forever to complete.

    Select A.EventDate,
    Count (Distinct B.ClientID)
    from Table A
    left join Table B
    on B.EventDate between A.EventDate and DateAdd(DD, -30,A.EventDate)
    Group By A.EventDate   

Is there any other efficient way to achieve this?

Answer 1

I think this is what you're looking for. You should not need to join back to the same table.

Select EventDate, Count (Distinct ClientID)
from Table
where EventDate >= DateAdd(DD, -30,GETDATE())
Group By EventDate