multisig-revenue-2021.sql

SELECT
  FORMAT_DATE("%b %Y", dt) AS month,
  FORMAT("%.4f", eth_revenue) AS eth,
  FORMAT("%.2f", usd_revenue) AS usd
FROM (
  SELECT
    DATE_TRUNC(EXTRACT(DATE
      FROM
        event_timestamp AT TIME ZONE "Asia/Singapore"), month) AS dt,
    SUM(cost) AS eth_revenue,
    SUM(cost * ether_price) AS usd_revenue
  FROM
    `ens-manager.registrations.registration_periods`
  WHERE
    event_timestamp >= timestamp '2021-01-01 00:00:00 Asia/Singapore'
    AND event_timestamp <= timestamp '2021-11-29 19:21:50 UTC'
  GROUP BY
    dt)
ORDER BY
  dt