Calculate number of watches by number of users in sql-CodePudding

I want to write 1 query that calculates the frequency buckets for each Ad. The frequency buckets would be the number of users who watched the Ad 1 time, 2 times, 3 times, etc. The table is:

AdId	UserID	Watch Date
123	A	1/22
123	B	1/13
123	C	1/22
123	A	1/24
123	A	1/11
123	B	1/10
123	D	1/14
123	E	1/20

The output I'm looking for is this: 3 users(C, D, E) watched the ad once, 1 user(B) watched it twice and 1 user(A) watched it 3 times.

AdId	# of Times Watched	# of Users
1	1	3
1	2	1
1	3	1

I have tried using count and group by but that does not give me this result

CodePudding user response：

select   max(AdId)              as AdId
        ,cnt                    as '# of Times Watched'
        ,count(distinct UserID) as '# of Users'
from     (
          select  *
                 ,count(*) over(partition by AdId, UserID) as cnt
          from    t
         ) t
group by cnt

AdId	# of Times Watched	# of Users
123	1	3
123	2	1
123	3	1

Fiddle