[Impact Metric Submission]: Optimism Onchain 6 Months Metrics

[BenraouaneSoufiane]

Optimism Onchain 6 Months Metrics

Tags

• OP
• OPTIMISTIC
• OPTIMISM

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, then I sum the records of last 6 months using sum function

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
WHERE DATE(TIMESTAMP_TRUNC(block_timestamp, MONTH))  >= DATE_ADD(CURRENT_DATE(), INTERVAL -6 MONTH) 
),
new_txs_or_users_6_months AS (
SELECT COUNT(DISTINCT from_address) AS new_txs_or_users_6_months,
FROM traces
WHERE DATE(TIMESTAMP_TRUNC(block_timestamp, MONTH)) >= DATE_ADD(CURRENT_DATE(), INTERVAL -6 MONTH)
),
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 AND user_base_per_month.months >= DATE_ADD(CURRENT_DATE(), INTERVAL -6 MONTH) ),
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  AND user_base_per_month.months >= DATE_ADD(CURRENT_DATE(), INTERVAL -6 MONTH) 
),
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 AND user_base_per_month.months >= DATE_ADD(CURRENT_DATE(), INTERVAL -6 MONTH) 
),
blocks_6_months AS (
SELECT COUNT(DISTINCT block_number) AS blocks_6_months,
FROM traces
WHERE DATE(TIMESTAMP_TRUNC(block_timestamp, MONTH)) >= DATE_ADD(CURRENT_DATE(), INTERVAL -6 MONTH)

),
gas_6_months AS (
SELECT SUM(gas) AS gas_6_months,
FROM traces
WHERE DATE(TIMESTAMP_TRUNC(block_timestamp, MONTH)) >= DATE_ADD(CURRENT_DATE(), INTERVAL -6 MONTH)
),
gas_used_6_months AS (
SELECT SUM(gas_used) AS gas_used_6_months,
FROM traces
WHERE DATE(TIMESTAMP_TRUNC(block_timestamp, MONTH)) >= DATE_ADD(CURRENT_DATE(), INTERVAL -6 MONTH)
),
amount_6_months AS (
SELECT SUM(value) AS amount_6_months,
FROM traces
WHERE DATE(TIMESTAMP_TRUNC(block_timestamp, MONTH)) >= DATE_ADD(CURRENT_DATE(), INTERVAL -6 MONTH)
)
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