Streamlined solution for division on similar subqueries

Question

I have a table in PostgreSQL 13 that looks like this (modified for the purpose of this question):

SELECT * FROM visits.visitors_log;

   visitor_id |          day           |  source
--------------+------------------------+----------
            9 | 2019-12-30 12:10:10-05 | Twitter
            7 | 2019-12-14 22:10:26-04 | Netflix
            5 | 2019-12-13 15:21:04-05 | Netflix
            9 | 2019-12-22 23:34:47-05 | Twitter
            7 | 2019-12-22 00:10:26-04 | Netflix
            9 | 2019-12-22 13:20:42-04 | Twitter

After converting the times to another timezone, I want to calculate the percentage of visits on 2019-12-22 that came from a specific source.
There are 4 steps involved:

1. Convert timezones
2. Calculate how many total visits happened on that day
3. Calculate how many total visits happened on that day that came from source Netflix
4. Divide those 2 numbers to get percentage.

I wrote this code which works but seems repetitive and not very clean:

SELECT (SELECT COUNT(*) FROM (SELECT visitor_id, source, day AT TIME ZONE 'PST' FROM visits.visitors_log WHERE day::date = '2019-12-22') AS a
        WHERE day::date = '2019-12-22' AND source = 'Netflix') * 100.0
         /
       (SELECT COUNT(*) FROM (SELECT visitor_id, source, day AT TIME ZONE 'PST' FROM visits.visitors_log WHERE day::date = '2019-12-22') AS b
        WHERE day::date = '2019-12-22')
   AS visitors_percentage;

Can anyone suggest a neater way of answering this question?

Answer 1

Hmmm . . . You can use window functions to calculate the total:

SELECT source, COUNT(*) / SUM(COUNT(*)) OVER () as visitors_percentage
FROM visits.visitors_log
WHERE (day AT TIME ZONE 'PST')::date = '2019-12-22'
GROUP BY SOURCE