PostgreSQL: creating OHLC or "candlestick" output from quote/tick raw material

As a quick note to self:

(jdbc/with-db-connection [conn -db-conn-]
  (let [t (time.coerce/from-string "2018-02-01")]
    (time
     (dotimes [i 50]
       (let [ts-start (time/plus t (time/hours i))
             ts-end (time/plus t (time/hours (inc i)))]
         (time (println {:data (jdbc/query conn ["SELECT
        first_value(bid_price) OVER w AS first_bid_price,
        first_value(ask_price) OVER w AS first_ask_price,
        MAX(bid_price) OVER w AS max_bid_price,
        MAX(ask_price) OVER w AS max_ask_price,
        MIN(bid_price) OVER w AS min_bid_price,
        MIN(ask_price) OVER w AS min_ask_price,
        last_value(bid_price) OVER w AS last_bid_price,
        last_value(ask_price) OVER w AS last_ask_price
  FROM bitmex_quote
  WHERE timestamp >= ? AND timestamp < ? AND symbol = 'XBTUSD'
  WINDOW w AS (ORDER BY timestamp ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
  ORDER BY timestamp ASC
  LIMIT 1;"
                                                 (time.coerce/to-sql-time ts-start) (time.coerce/to-sql-time ts-end)])
                         :timestamp (str ts-end)})))))))


...for this to perform reasonably you'll need to do indexing.