[Impact Metric Submission]: Summary Base Onchain Metrics by month

[BenraouaneSoufiane]

Base Onchain Metrics by month

Tags

• Base
• l2

Brief description

This metric calculated using the superchain subproject on the opensource-observer directory/repository (base_trace table). I classified records by month since project inception & order them to humain-readable form, so you can plot them easily with axies

SQL code block

Insert the SQL that we should run to replicate your impact metric:

WITH data AS (SELECT *,
FROM `opensource-observer.superchain.base_traces`
),
total_of_txs AS (
SELECT COUNT(*) AS total_of_txs,
DATE (TIMESTAMP_TRUNC(block_timestamp, MONTH)) AS months
FROM data
GROUP BY months ORDER BY months ASC
),
total_of_blocks AS (
SELECT COUNT(DISTINCT block_number) AS total_of_blocks,
DATE (TIMESTAMP_TRUNC(block_timestamp, MONTH)) AS months
FROM data
GROUP BY months ORDER BY months ASC
),
total_of_gas AS (
SELECT SUM(gas) AS total_of_gas,
DATE (TIMESTAMP_TRUNC(block_timestamp, MONTH)) AS months
FROM data
GROUP BY months ORDER BY months ASC
),
total_of_gas_used AS (
SELECT SUM(gas_used) AS total_of_gas_used,
DATE (TIMESTAMP_TRUNC(block_timestamp, MONTH)) AS months
FROM data
GROUP BY months ORDER BY months ASC
),
total_amount AS (
SELECT SUM(value) AS total_amount,
DATE (TIMESTAMP_TRUNC(block_timestamp, MONTH)) AS months
FROM data
GROUP BY months ORDER BY months ASC
)
SELECT t.months,
tb.total_of_blocks,
t.total_of_txs,
g.total_of_gas,
gu.total_of_gas_used,
a.total_amount
FROM total_of_blocks AS tb
LEFT JOIN total_of_txs AS t
ON t.months = tb.months
LEFT JOIN total_of_gas AS g
ON g.months = t.months
LEFT JOIN total_of_gas_used AS gu
ON gu.months = g.months
LEFT JOIN total_amount AS a
ON a.months = gu.months
ORDER BY t.months ASC