Time Series Data SQL Example in PostgreSQL

index.sql

-- ------------------------------------------------
-- Weekly
-- ------------------------------------------------

-- Revenue for week range
SELECT
  DATE(weeks) AS week,
  COALESCE(SUM(order_contents.quantity * products.price), 0) AS amount
  FROM GENERATE_SERIES(
    DATE_TRUNC('WEEK', DATE('2015-9-1')),
    DATE('2015-10-1'), '1 WEEK'::INTERVAL) weeks
  LEFT JOIN orders ON DATE(DATE_TRUNC('WEEK', orders.checkout_date)) = weeks
  LEFT JOIN order_contents ON orders.id = order_contents.order_id
  LEFT JOIN products ON products.id = order_contents.product_id
  GROUP BY weeks
  ORDER BY weeks DESC
;

-- Revenue by weeks
SELECT
  DATE(weeks) AS week,
  COALESCE(SUM(order_contents.quantity * products.price), 0) AS amount
  FROM GENERATE_SERIES((
    SELECT DATE(DATE_TRUNC('WEEK', MIN(checkout_date))) FROM orders)
  , CURRENT_DATE, '1 WEEK'::INTERVAL) weeks
  LEFT JOIN orders ON DATE(DATE_TRUNC('WEEK', orders.checkout_date)) = weeks
  LEFT JOIN order_contents ON orders.id = order_contents.order_id
  LEFT JOIN products ON products.id = order_contents.product_id
  GROUP BY weeks
  ORDER BY weeks DESC
;

-- Count for week range
SELECT
  DATE(weeks) AS week,
  COALESCE(COUNT(orders.*), 0) AS num_orders
  FROM GENERATE_SERIES(
    DATE_TRUNC('WEEK', DATE('2015-9-1')),
    DATE('2015-10-1'), '1 WEEK'::INTERVAL) weeks
  LEFT JOIN orders ON DATE(DATE_TRUNC('WEEK', orders.checkout_date)) = weeks
  GROUP BY weeks
  ORDER BY weeks DESC
;

-- Count by weeks
SELECT
  DATE(weeks) AS week,
  COALESCE(COUNT(orders.*), 0) AS num_orders
  FROM GENERATE_SERIES((
    SELECT DATE(DATE_TRUNC('WEEK', MIN(checkout_date))) FROM orders)
  , CURRENT_DATE, '1 WEEK'::INTERVAL) weeks
  LEFT JOIN orders ON DATE(DATE_TRUNC('WEEK', orders.checkout_date)) = weeks
  GROUP BY weeks
  ORDER BY weeks DESC
;

-- Revenue by week (with time gaps)
SELECT
  DATE(DATE_TRUNC('WEEK', checkout_date)) AS week,
  SUM(order_contents.quantity * products.price) AS amount
  FROM orders
  JOIN order_contents ON orders.id = order_contents.order_id
  JOIN products ON products.id = order_contents.product_id
  WHERE checkout_date IS NOT NULL
  GROUP BY week
  ORDER BY week DESC
;

-- Count by week (with time gaps)
SELECT
  DATE(DATE_TRUNC('WEEK', checkout_date)) AS week,
  COUNT(*) AS num_orders
  FROM orders
  WHERE checkout_date IS NOT NULL
  GROUP BY week
  ORDER BY week DESC
;

-- Generate weeks list
SELECT DATE(weeks) AS week
  FROM GENERATE_SERIES((
    SELECT DATE(DATE_TRUNC('WEEK', MIN(checkout_date))) FROM orders)
  , CURRENT_DATE, '1 WEEK'::INTERVAL) weeks
  ORDER BY weeks DESC
;

-- ------------------------------------------------
-- Daily
-- ------------------------------------------------

-- Revenue for day range
SELECT
  DATE(days) AS day,
  COALESCE(SUM(order_contents.quantity * products.price), 0) AS amount
  FROM GENERATE_SERIES(
    DATE('2015-9-1'),
    DATE('2015-10-1'), '1 DAY'::INTERVAL) days
  LEFT JOIN orders ON DATE(DATE_TRUNC('DAY', orders.checkout_date)) = days
  LEFT JOIN order_contents ON orders.id = order_contents.order_id
  LEFT JOIN products ON products.id = order_contents.product_id
  GROUP BY days
  ORDER BY days DESC
;

-- Revenue by day
SELECT
  DATE(days) AS day,
  COALESCE(SUM(order_contents.quantity * products.price), 0) AS amount
  FROM GENERATE_SERIES((
    SELECT DATE(MIN(checkout_date)) FROM orders
  ), CURRENT_DATE, '1 DAY'::INTERVAL) days
  LEFT JOIN orders ON DATE(orders.checkout_date) = days
  LEFT JOIN order_contents ON orders.id = order_contents.order_id
  LEFT JOIN products ON products.id = order_contents.product_id
  GROUP BY days
  ORDER BY days DESC
;

-- Count for day range
SELECT
  DATE(days) AS day,
  COALESCE(COUNT(orders.*), 0) AS num_orders
  FROM GENERATE_SERIES(
    DATE('2015-9-1'),
    DATE('2015-10-1'), '1 DAY'::INTERVAL) days
  LEFT JOIN orders ON DATE(DATE_TRUNC('DAY', orders.checkout_date)) = days
  GROUP BY days
  ORDER BY days DESC
;

-- Count by day
SELECT
  DATE(days) AS day,
  COUNT(orders.*) AS num_orders
  FROM GENERATE_SERIES((
    SELECT DATE(MIN(checkout_date)) FROM orders
  ), CURRENT_DATE, '1 DAY'::INTERVAL) days
  LEFT JOIN orders ON DATE(orders.checkout_date) = days
  GROUP BY days
  ORDER BY days DESC
;

-- Revenue by day (with time gaps)
SELECT
  DATE(DATE_TRUNC('DAY', checkout_date)) AS day,
  SUM(order_contents.quantity * products.price) AS amount
  FROM orders
  JOIN order_contents ON orders.id = order_contents.order_id
  JOIN products ON products.id = order_contents.product_id
  WHERE checkout_date IS NOT NULL
  GROUP BY day
  ORDER BY day DESC
;

-- Count by day (with time gaps)
SELECT
  DATE(DATE_TRUNC('DAY', checkout_date)) AS day,
  COUNT(*) AS num_orders
  FROM orders
  WHERE checkout_date IS NOT NULL
  GROUP BY day
  ORDER BY day DESC
;

-- Generate days list
SELECT DATE(days) AS day
  FROM GENERATE_SERIES((
    SELECT DATE(MIN(checkout_date)) FROM orders)
  , CURRENT_DATE, '1 DAY'::INTERVAL) days
  ORDER BY days DESC
;

-- ------------------------------------------------
-- Debugging/Testing Queries
-- ------------------------------------------------

-- Order IDs, quantities, prices and sub totals
SELECT
  DATE(orders.checkout_date) AS day,
  order_contents.quantity * products.price AS product_total
  FROM orders
  JOIN order_contents ON orders.id = order_contents.order_id
  JOIN products ON products.id = order_contents.product_id
  WHERE orders.checkout_date IS NOT NULL
  ORDER BY day DESC
;

-- Order IDs, quantities, prices and sub totals
SELECT
  DATE(orders.checkout_date) AS day,
  orders.id,
  order_contents.quantity,
  products.price,
  order_contents.quantity * products.price AS product_total
  FROM orders
  JOIN order_contents ON orders.id = order_contents.order_id
  JOIN products ON products.id = order_contents.product_id
  WHERE orders.checkout_date IS NOT NULL
  ORDER BY day DESC
;