Flipside TeamTop Projects by Revenue
    Updated 2024-11-14
    with t1 as (
    select
    block_timestamp,
    tx_hash,
    tx_receiver as contract_address,
    tx_signer as sender,
    TRANSACTION_FEE/pow(10,24) as tx_fee,
    tx:receipt[0].outcome.gas_burnt::NUMBER/pow(10,16) as gas_burned,
    gas_burned*0.3 as dev_revenue,
    tx_fee*0.3 as revenue_fees
    from near.core.fact_transactions
    where block_timestamp >= current_date - 365
    )

    select t2.project_name,
    count(t1.tx_hash) as n_tx,
    sum(gas_burned) as "Burnt Fees (NEAR)",
    sum(t1.dev_revenue) as "Revenue"
    from t1
    left join near.core.dim_address_labels t2
    on t1.contract_address = t2.address
    where label_type not in ('cex', 'token')
    and t2.project_name not in ('near','usdc','lnr')
    group by 1
    order by 4 desc
    limit 20
    QueryRunArchived: QueryRun has been archived