Hourly sales

hourly_sales.sql

SELECT
  TIMESTAMP_TRUNC(invoiced_at, HOUR) AS timestamp
  , DATETIME_TRUNC(local_invoiced_at, HOUR) AS date_hour
  , FORMAT_DATETIME("%A", local_invoiced_at) AS day
  , MOD(CAST(FORMAT_DATETIME("%u", local_invoiced_at) AS int64), 7) AS day_num
  , FORMAT_DATETIME("%H", local_invoiced_at) AS hour
  , shop_name
  , COUNT(*) AS transactions
  , SUM(customers) AS customers
  , SUM(sales) AS sales
  , SUM(tax) AS tax
  , SUM(cost) AS cost
  , SUM(discount) AS discount
  , SUM(sales - tax) AS net
FROM
  PROJECT.DATASET.transactions
GROUP BY
  shop_name, timestamp, date_hour, day, day_num, hour
ORDER BY
  date_hour DESC
;