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
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
The text was updated successfully, but these errors were encountered:
Optimism Onchain All the time Metrics
Tags
Brief description
I used the classic sql selection from the optimism_traces table with sum, count functions & some conditions
SQL code block
The text was updated successfully, but these errors were encountered: