gistfile1.txt

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.project,
  dts.revision,
  dts.workerType,
  (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-01-01'
  AND dts.date<'2020-07-01'
  AND dts.owner not in ('[email protected]', '[email protected]', '[email protected]', '[email protected]')
  AND dts.project in ('try', 'mozilla-central', 'autoland')
  AND dts.date=cpw.date
  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