[Impact Metric Submission]: Optimism Onchain All the time Metrics

[BenraouaneSoufiane]

Optimism Onchain All the time Metrics

Tags

• OP
• OPTIMISM
• OPTIMISTIC
• L2

Brief description

I used the classic sql selection from the optimism_traces table with sum, count functions & some conditions

SQL code block

WITH traces AS (SELECT *,
FROM `opensource-observer.superchain.optimism_traces`
),
txs_or_users_6_months AS (
SELECT  COUNT(from_address) AS txs_or_users_6_months,
FROM traces
),
new_txs_or_users_6_months AS (
SELECT COUNT(DISTINCT from_address) AS new_txs_or_users_6_months,
FROM traces
),
user_base_per_month AS (SELECT DATE (TIMESTAMP_TRUNC(block_timestamp, MONTH)) AS months
  ,from_address,COUNT(from_address) AS number_of_txs
FROM traces
GROUP BY months, from_address ORDER BY months ASC),
less_active_users_6_months AS (

SELECT COUNT(user_base_per_month.from_address) AS less_active_users_6_months
FROM user_base_per_month
WHERE user_base_per_month.number_of_txs < 10 ),
more_active_users_6_months AS (
SELECT COUNT(user_base_per_month.number_of_txs) AS more_active_users_6_months
FROM user_base_per_month
WHERE user_base_per_month.number_of_txs >= 10   
),
high_frequency_users_6_months AS (
SELECT COUNT(user_base_per_month.number_of_txs) AS high_frequency_users_6_months
FROM user_base_per_month
WHERE user_base_per_month.number_of_txs >= 1000 
),
blocks_6_months AS (
SELECT COUNT(DISTINCT block_number) AS blocks_6_months,
FROM traces

),
gas_6_months AS (
SELECT SUM(gas) AS gas_6_months,
FROM traces
),
gas_used_6_months AS (
SELECT SUM(gas_used) AS gas_used_6_months,
FROM traces
),
amount_6_months AS (
SELECT SUM(value) AS amount_6_months,
FROM traces
)
SELECT a.txs_or_users_6_months, b.new_txs_or_users_6_months, c.less_active_users_6_months, d.more_active_users_6_months, e.high_frequency_users_6_months, f.blocks_6_months, g.gas_6_months, h.gas_used_6_months, i.amount_6_months
FROM txs_or_users_6_months a, new_txs_or_users_6_months b, less_active_users_6_months c, more_active_users_6_months d, high_frequency_users_6_months e, blocks_6_months f, gas_6_months g, gas_used_6_months h, amount_6_months i