Open ccerv1 opened 5 hours ago
First, we create a snapshot of relevant project contracts. We'll use a Google Sheet just to keep this simple to update.
Then, we prepare a filter for relevant traces from the 180 day time window we care about. That's effectively this query:
with txns as (
select
transaction_hash, to_address,from_address,block_timestamp, 'Base' as chain
from `opensource-observer.superchain.base_traces`
where
block_timestamp between '2024-03-01' and '2024-09-01'
and status = 1
and call_type = 'call'
union all
select
transaction_hash, to_address,from_address,block_timestamp, 'Optimism' as chain
from `opensource-observer.superchain.optimism_traces`
where
block_timestamp between '2024-03-01' and '2024-09-01'
and status = 1
and call_type = 'call'
union all
select
transaction_hash, to_address,from_address,block_timestamp, 'Zora' as chain
from `opensource-observer.superchain.zora_traces`
where
block_timestamp between '2024-03-01' and '2024-09-01'
and status = 1
and call_type = 'call'
union all
select
transaction_hash, to_address,from_address,block_timestamp, 'Mode' as chain
from `opensource-observer.superchain.mode_traces`
where
block_timestamp between '2024-03-01' and '2024-09-01'
and status = 1
and call_type = 'call'
union all
select
transaction_hash, to_address,from_address,block_timestamp, 'Frax' as chain
from `opensource-observer.superchain.frax_traces`
where
block_timestamp between '2024-03-01' and '2024-09-01'
and status = 1
and call_type = 'call'
union all
select
transaction_hash, to_address,from_address,block_timestamp, 'Metal' as chain
from `opensource-observer.superchain.metal_traces`
where
block_timestamp between '2024-03-01' and '2024-09-01'
and status = 1
)
select *
from txns
Unfortunately, this is a huge query so we need to store it temporarily in a new table.
Now, we apply the project filter to get a much smaller (50GB) table of just the traces we care about:
select *
from `static_data_sources.sunny_traces` traces
join `static_data_sources.sunny_project_apps` projects
on
traces.chain = projects.chain
and (traces.to_address = projects.address or traces.from_address = projects.address)
Finally, we derive a high level set of events that we can use for further metrics analysis.
select
projects.uuid,
case when traces.block_timestamp < '2024-06-01' then '180D' else '90D' end as time_window,
case when traces.to_address = projects.address then 'to_event' else 'from_event' end as event_type,
traces.chain,
traces.to_address,
traces.from_address,
count(distinct transaction_hash) as txns
from `static_data_sources.sunny_project_traces` traces
join `static_data_sources.sunny_project_apps` projects
on
traces.chain = projects.chain
and (traces.to_address = projects.address or traces.from_address = projects.address)
group by 1,2,3,4,5,6
What is it?
Many of the Sunny Awards projects have trace event data that we need to derive metrics. We need to union the relevant trace data from all supported chains and filter on the to/from events that include Sunny project contracts.