create_dataset.sql

WITH
  cumsum_table AS(
  SELECT
    account_name, year, month, day,
    ROUND(SUM(daily_cost) OVER (PARTITION BY account_name, year, month ORDER BY day )) AS cumsum,
    ROUND(AVG(daily_cost) OVER (PARTITION BY account_name, year, month ORDER BY day )) AS mean_daily_cost
  FROM
    `billing_dataset_example.billing_daily_monthly`
  ORDER BY  account_name, year, month, day),
  monthly_cost_table AS (
  SELECT
    account_name,
    EXTRACT(YEAR FROM   bill_datetime ) year,
    EXTRACT(MONTH FROM bill_datetime) month,
    ROUND(SUM(cost)) monthly_cost
  FROM
    `billing_dataset_example.account_billing_log`
  GROUP BY
    1, 2, 3 )
SELECT
  cumsum_table.*,
  monthly_cost_table.monthly_cost
FROM
  cumsum_table
LEFT JOIN
  monthly_cost_table
ON
  monthly_cost_table.account_name = cumsum_table.account_name
  AND monthly_cost_table.year = cumsum_table.year
  AND monthly_cost_table.month = cumsum_table.month