date-partition-example.sql

SELECT
  o_history._PARTITIONTIME AS report_date,
  o.facility_name AS facility_name,
  o.facility_departure_date AS ship_date,
  sum(o.number_of_boxes) AS num_boxes
FROM orders o
LEFT JOIN order_history o_history ON o.order_id = o_history.order_id
WHERE
  o_history._PARTITIONTIME > timestamp('2016-11-01')
  AND o_history.shipment_status IN ('Pending', 'Shipped')
  AND o.facility_departure_date BETWEEN date('2016-12-01')
  AND date('2017-01-01')
GROUP BY 1,2
ORDER BY 2,1