Roku's streaming analytics team wants to measure channel breadth across accounts. Write a PostgreSQL query that calculates the average number of distinct Roku channels streamed per eligible account during June 2020.
An eligible account is one with at least one June streaming event linked to a valid Roku channel. Do not count duplicate streams of the same channel more than once, and exclude events outside June or events with missing account or channel references.
Requirements
- Filter streaming events to the half-open interval from
2020-06-01 00:00:00 through, but not including, 2020-07-01 00:00:00.
- Count distinct channels for each eligible account.
- Return one value named
avg_channels_per_account, rounded to three decimal places.
