gistfile1.txt

-- TODO extract month
select data.date, data.project, count(distinct data.revision) as pushes, sum(data.cost) as cost, sum(data.cost)/count(distinct data.revision) as cost_per_push from (
SELECT
  DATE_TRUNC(dts.date, MONTH) as date,
  dts.revision,
  dts.project,
  dts.workerType,
--  sum(cpw.cost) as cost
  (sum(dts.execution)/3600)*(sum(cpw.cost)/sum(cpw.hours)) as cost
FROM
  taskclusteretl.derived_task_summary as dts,
  taskclusteretl.derived_daily_cost_per_workertype as cpw
WHERE
  dts.date>='2020-04-01'
  AND dts.date<'2020-07-01'
  AND dts.project in ('autoland')
  and symbol='B' and groupSymbol is NULL and kind='build' and collection='opt'
  AND dts.date=cpw.date
  AND cpw.cluster='firefox'
  AND cpw.hours is not null
  AND cpw.workerType=dts.workerType
  AND cpw.provisionerId=dts.provisionerId
GROUP BY
  dts.date,
  dts.project,
  dts.revision,
  dts.workerType
ORDER BY
  dts.date,
  dts.workerType
  ASC
) as data
group by data.date, data.project
order by date asc