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 ('reviewbot@noreply.mozilla.org', 'wptsync@mozilla.com', 'mozilla-taskcluster-maintenance@mozilla.com', 'release-mgmt-analysis@mozilla.com')
  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