hyoeisemanAverage Optimism Portfolio_8
    Updated 2022-11-11
    with table1 as (SELECT optimism.core.dim_labels.project_name as the_project, ifnull(SUM(event_inputs:value/1e18),0) as amount_OP
    FROM optimism.core.fact_event_logs
    join optimism.core.dim_labels
    on optimism.core.fact_event_logs.origin_to_address = optimism.core.dim_labels.address
    WHERE tx_status ilike 'SUCCESS' AND event_inputs:value/1e18 < 1e8
    AND contract_address ilike '0x4200000000000000000000000000000000000042' -- OP'S CONTRACT ADDRESS
    AND event_name = 'Transfer'
    and block_timestamp >= '{{Start_Date}}'and block_timestamp <= CURRENT_DATE - 1
    GROUP BY 1
    ORDER BY 2 DESC
    LIMIT 10)
    SELECT date_trunc( 'month',block_timestamp) as month, project_name, ifnull(SUM(event_inputs:value/1e18),0) as amount_OP
    FROM optimism.core.fact_event_logs
    join optimism.core.dim_labels
    on optimism.core.fact_event_logs.origin_to_address = optimism.core.dim_labels.address
    WHERE tx_status ilike 'SUCCESS' AND event_inputs:value/1e18 < 1e8
    AND contract_address ilike '0x4200000000000000000000000000000000000042' -- OP'S CONTRACT ADDRESS
    AND event_name = 'Transfer'
    and block_timestamp >= '{{Start_Date}}'and block_timestamp <= CURRENT_DATE - 1
    and project_name in (select the_project from table1) AND event_inputs:value/1e18 < 1e8
    group by 1,2
    Run a query to Download Data