You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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
The text was updated successfully, but these errors were encountered:
Optimism Onchain 6 Months Metrics
Tags
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
The text was updated successfully, but these errors were encountered: