crosstab_query.sql

-- Given orders, which are placed by a user at a given location and have a created_at time,
-- I want to know the number of orders users that visit a location placed at each hour of the day.

SELECT
  row_name[1] AS user_id,
  row_name[2] AS location_id,
  COALESCE(created_at_hour_0, 0) AS created_at_hour_0,
  COALESCE(created_at_hour_1, 0) AS created_at_hour_1,
  COALESCE(created_at_hour_2, 0) AS created_at_hour_2,
  COALESCE(created_at_hour_3, 0) AS created_at_hour_3,
  COALESCE(created_at_hour_4, 0) AS created_at_hour_4,
  COALESCE(created_at_hour_5, 0) AS created_at_hour_5,
  COALESCE(created_at_hour_6, 0) AS created_at_hour_6,
  COALESCE(created_at_hour_7, 0) AS created_at_hour_7,
  COALESCE(created_at_hour_8, 0) AS created_at_hour_8,
  COALESCE(created_at_hour_9, 0) AS created_at_hour_9,
  COALESCE(created_at_hour_10, 0) AS created_at_hour_10,
  COALESCE(created_at_hour_11, 0) AS created_at_hour_11,
  COALESCE(created_at_hour_12, 0) AS created_at_hour_12,
  COALESCE(created_at_hour_13, 0) AS created_at_hour_13,
  COALESCE(created_at_hour_14, 0) AS created_at_hour_14,
  COALESCE(created_at_hour_15, 0) AS created_at_hour_15,
  COALESCE(created_at_hour_16, 0) AS created_at_hour_16,
  COALESCE(created_at_hour_17, 0) AS created_at_hour_17,
  COALESCE(created_at_hour_18, 0) AS created_at_hour_18,
  COALESCE(created_at_hour_19, 0) AS created_at_hour_19,
  COALESCE(created_at_hour_20, 0) AS created_at_hour_20,
  COALESCE(created_at_hour_21, 0) AS created_at_hour_21,
  COALESCE(created_at_hour_22, 0) AS created_at_hour_22,
  COALESCE(created_at_hour_23, 0) AS created_at_hour_23
FROM crosstab($$
  SELECT
    -- crosstab can only pivot on one column. Any "extra" columns
    -- are assumed to be the same across all rows. I combined
    -- user_id and location_id into a single column to give crosstab
    -- a single unique column for the pivot.
    ARRAY[orders.user_id, orders.location_id] AS row_name,
    'created_at_hour_' || EXTRACT(
      HOUR
      FROM orders.created_at
        AT TIME ZONE 'UTC'
        AT TIME ZONE locations.time_zone
    ) AS local_hour_created_at,
    count(*) AS number_of_orders
  FROM orders
  INNER JOIN locations ON locations.id = orders.location_id
  WHERE orders.state = 'completed'
  GROUP BY user_id, location_id, local_hour_created_at
  ORDER BY 1, 2, 3$$,

  $$VALUES
    ('created_at_hour_0'::text),
    ('created_at_hour_1'),
    ('created_at_hour_2'),
    ('created_at_hour_3'),
    ('created_at_hour_4'),
    ('created_at_hour_5'),
    ('created_at_hour_6'),
    ('created_at_hour_7'),
    ('created_at_hour_8'),
    ('created_at_hour_9'),
    ('created_at_hour_10'),
    ('created_at_hour_11'),
    ('created_at_hour_12'),
    ('created_at_hour_13'),
    ('created_at_hour_14'),
    ('created_at_hour_15'),
    ('created_at_hour_16'),
    ('created_at_hour_17'),
    ('created_at_hour_18'),
    ('created_at_hour_19'),
    ('created_at_hour_20'),
    ('created_at_hour_21'),
    ('created_at_hour_22'),
    ('created_at_hour_23')$$
) AS ct(
  row_name int[],
  created_at_hour_0 int,
  created_at_hour_1 int,
  created_at_hour_2 int,
  created_at_hour_3 int,
  created_at_hour_4 int,
  created_at_hour_5 int,
  created_at_hour_6 int,
  created_at_hour_7 int,
  created_at_hour_8 int,
  created_at_hour_9 int,
  created_at_hour_10 int,
  created_at_hour_11 int,
  created_at_hour_12 int,
  created_at_hour_13 int,
  created_at_hour_14 int,
  created_at_hour_15 int,
  created_at_hour_16 int,
  created_at_hour_17 int,
  created_at_hour_18 int,
  created_at_hour_19 int,
  created_at_hour_20 int,
  created_at_hour_21 int,
  created_at_hour_22 int,
  created_at_hour_23 int
)