🧗 Find out the energy reduction

The Merge promised to bring

• reduction in energy consumption.
• reduction in ETH issuance.

Energy consumption is affected by a variety of factors, including device hardware, number of nodes, and so on. It becomes difficult to predict without reliable information. Nonetheless, a substantial amount of research has been conducted on this subject.

We will use the Digiconomist energy index for this task. Digiconomist has released an API that provides energy consumption metrics at a more granular level (per gas unit).

We've prepared the data using this snippet.

Can you find out change in energy consumption?

WITH
  energy_metrics(dt, gasunit_wh, gasunit_gco2) AS (
    SELECT
      *
    FROM
      (
        values
          ('2022-08-01', 2.35668, 1.31446),
          ...
      )
  )
SELECT
  DATE_TRUNC('day', `time`) AS day,
  --1. Multiply gas_used with gas_unit_wh, divide by 1000 to convert to kwH
  --2. If value not available, use 0.0003 as median estimate after merge i.e. COALESCE(gasunit_wh,0.0003)
  SUM(gas_used * COALESCE(gasunit_wh,0.0003)/1000) AS daily_energy_kwh
FROM
  ethereum.blocks eb
  LEFT JOIN energy_metrics em ON DATE(eb.time) = em.dt
WHERE
  `time` >= DATE_TRUNC('week', '2022-08-01') --The data is fetched only from August
GROUP BY day
ORDER BY day
--3. Use reference dates as 09-14 , 09-16 to compute the change in energy consumption

Click here to solve the quest.