hyoeisemanAverage Optimism Portfolio_5
    Updated 2022-11-10
    with table1 as (SELECT optimism.core.dim_labels.project_name as the_project, count(DISTINCT origin_from_address) AS count_users,
    count(distinct tx_hash) AS count_transactions
    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 block_timestamp >= '{{Start_Date}}'and block_timestamp <= CURRENT_DATE - 1
    GROUP BY 1
    ORDER BY 2 desc
    LIMIT 10),
    table2 as (SELECT optimism.core.dim_labels.project_name as top_project, count(DISTINCT origin_from_address) AS count_users,
    count(distinct tx_hash) AS count_transactions
    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 block_timestamp >= '{{Start_Date}}'and block_timestamp <= CURRENT_DATE - 1
    GROUP BY 1 order by 3 desc limit 10),
    table3 as (SELECT optimism.core.dim_labels.project_name as 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 top_project, table2.count_users, table2.count_transactions, amount_OP,
    table2.count_transactions/table2.count_users as number_transaction_per_user,
    amount_OP/table2.count_transactions as amount_per_transaction
    from table1 join table2 on top_project = the_project
    join table3 on table3.project = top_project


    Run a query to Download Data