query.sql

WITH q_geocodes(name, id) AS (VALUES ('SF', 6096070),('NYC', 6164533))

SELECT
  q_geocodes.name,
  AVG(CAST(end_time + (time_zone * INTERVAL '1 sec') AS time)) AS mean_wake_time,

  COUNT(events.*) AS counts,
  histogram(EXTRACT(epoch FROM CAST(end_time + (time_zone * INTERVAL '1 sec') AS time)) :: real, EXTRACT(epoch FROM '04:00'::time) :: real, EXTRACT(epoch FROM '12:00'::time) :: real, 16) AS hist,

  EXTRACT(DOW FROM (end_time + time_zone * INTERVAL '1 sec' )) AS dow

  FROM q_geocodes
    INNER JOIN places ON q_geocodes.id = places.geocode_id
    INNER JOIN events AS events
      ON events.place_id = places.id
      AND events.category_id = 8
      AND ((EXTRACT(EPOCH FROM end_time) - EXTRACT(EPOCH FROM start_time)) BETWEEN (4*3600) AND (12*3600)
      AND EXTRACT(HOUR FROM (end_time + time_zone * INTERVAL '1 sec' )) BETWEEN 4 AND 12)
  WHERE places.category_id = 2 AND (place_events_count > 2)
  GROUP BY q_geocodes.name, dow
  ORDER BY q_geocodes.name, dow;

I'd like to substitute:

array(select x::float8 / (sum(x) over ()) from unnest(somearray) u(x)

in on line 8.