Home > Net >  Calculate number of watches by number of users in sql
Calculate number of watches by number of users in sql

Time:09-18

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

  •  Tags:  
  • sql
  • Related