Consolidate trace data for Sunny Awards projects

[ccerv1]

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.

[ccerv1]

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