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

[BenraouaneSoufiane]

Summary Optimism Onchain Metrics by month

Tags

• Optimism
• l2
• OP
• Optimistic

Brief description

This metric calculated using the superchain subproject on the opensource-observer directory/repository (optimism_traces 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

WITH data AS (SELECT *,
FROM `opensource-observer.superchain.optimism_traces`
),
txs_or_users AS (
SELECT COUNT(from_address) AS txs_or_users,
DATE (TIMESTAMP_TRUNC(block_timestamp, MONTH)) AS months
FROM data
GROUP BY months ORDER BY months ASC
),
new_txs_or_users AS (
SELECT COUNT(DISTINCT from_address) AS new_txs_or_users,
DATE (TIMESTAMP_TRUNC(block_timestamp, MONTH)) AS months
FROM data
GROUP BY months ORDER BY months ASC
),
less_active_users as (WITH user_base_per_month AS (SELECT DATE (TIMESTAMP_TRUNC(block_timestamp, MONTH)) AS months
  ,from_address,COUNT(from_address) AS number_of_txs
FROM data
GROUP BY months, from_address ORDER BY months ASC)
SELECT user_base_per_month.months, COUNT(user_base_per_month.number_of_txs) AS less_active_users
FROM user_base_per_month
WHERE user_base_per_month.number_of_txs < 10
GROUP BY user_base_per_month.months ORDER BY user_base_per_month.months ASC
),
more_active_users as (WITH user_base_per_month AS (SELECT DATE (TIMESTAMP_TRUNC(block_timestamp, MONTH)) AS months
  ,from_address,COUNT(from_address) AS number_of_txs
FROM data
GROUP BY months, from_address ORDER BY months ASC)
SELECT user_base_per_month.months, COUNT(user_base_per_month.number_of_txs) AS more_active_users
FROM user_base_per_month
WHERE user_base_per_month.number_of_txs >= 10
GROUP BY user_base_per_month.months ORDER BY user_base_per_month.months ASC
),
high_frequency_users as (WITH user_base_per_month AS (SELECT DATE (TIMESTAMP_TRUNC(block_timestamp, MONTH)) AS months
  ,from_address,COUNT(from_address) AS number_of_txs
FROM data
GROUP BY months, from_address ORDER BY months ASC)
SELECT user_base_per_month.months, COUNT(user_base_per_month.number_of_txs) AS high_frequency_users
FROM user_base_per_month
WHERE user_base_per_month.number_of_txs >= 1000
GROUP BY user_base_per_month.months ORDER BY user_base_per_month.months ASC
),
blocks AS (
SELECT COUNT(DISTINCT block_number) AS blocks,
DATE (TIMESTAMP_TRUNC(block_timestamp, MONTH)) AS months
FROM data
GROUP BY months ORDER BY months ASC
),
gas AS (
SELECT SUM(gas) AS gas,
DATE (TIMESTAMP_TRUNC(block_timestamp, MONTH)) AS months
FROM data
GROUP BY months ORDER BY months ASC
),
gas_used AS (
SELECT SUM(gas_used) AS gas_used,
DATE (TIMESTAMP_TRUNC(block_timestamp, MONTH)) AS months
FROM data
GROUP BY months ORDER BY months ASC
),
amount AS (
SELECT SUM(value) AS amount,
DATE (TIMESTAMP_TRUNC(block_timestamp, MONTH)) AS months
FROM data
GROUP BY months ORDER BY months ASC
)
SELECT t.months,
t.txs_or_users,
nt.new_txs_or_users,
lu.less_active_users,
mu.more_active_users,
hu.high_frequency_users,
tb.blocks,
g.gas,
gu.gas_used,
a.amount
FROM txs_or_users AS t
LEFT JOIN blocks AS tb
ON tb.months = t.months
LEFT JOIN new_txs_or_users as nt
ON nt.months = tb.months
LEFT JOIN less_active_users as lu
ON lu.months = nt.months
LEFT JOIN more_active_users as mu
ON mu.months = lu.months
LEFT JOIN high_frequency_users as hu
ON hu.months = mu.months
LEFT JOIN gas AS g
ON g.months = t.months
LEFT JOIN gas_used AS gu
ON gu.months = g.months
LEFT JOIN amount AS a
ON a.months = gu.months
ORDER BY t.months ASC

[ccerv1]

• Focus Area: Network Growth
• Query Executes in BigQuery? Yes
• Query Meets Challenge Spec? Yes
• Complexity Score:
• Utility Score: 🌟
• Total Points: 0.5
• Comments: Good metrics, but same as Base model