amelia-leeUntitled Query
    Updated 2022-08-01
    with price as (
    select hour::date as date, avg(price) as price
    from ethereum.core.fact_hourly_token_prices
    where date >= '2022-06-06' and symbol = 'WETH'
    group by 1
    ),usdc_fee as (
    select
    date_trunc('day', block_timestamp) as date,
    'USDC' as currency,
    sum(raw_amount) as opensea_fees
    from polygon.core.fact_token_transfers
    where
    (to_address = lower('0x8De9C5A032463C561423387a9648c5C7BCC5BC90') or to_address = lower('0x5b3256965e7C3cF26E11FCAf296DfC8807C01073'))
    and contract_address = '0x2791bca1f2de4661ed88a30c99a7a9449aa84174'
    group by 1
    ),dai_fee as (
    select
    date_trunc('day', block_timestamp) as date,
    'DAI' as currency,
    sum(raw_amount/1e18) as opensea_fees
    from polygon.core.fact_token_transfers
    where
    (to_address = lower('0x8De9C5A032463C561423387a9648c5C7BCC5BC90') or to_address = lower('0x5b3256965e7C3cF26E11FCAf296DfC8807C01073'))
    and contract_address = '0x8f3cf7ad23cd3cadbd9735aff958023239c6a063'
    group by 1
    ),weth_fee as (
    select
    date_trunc('day', block_timestamp) as date,
    'WETH' as currency,
    sum((raw_amount/1e18)*price) as opensea_fees
    from polygon.core.fact_token_transfers join price p on p.date = date
    where
    (to_address = lower('0x8De9C5A032463C561423387a9648c5C7BCC5BC90') or to_address = lower('0x5b3256965e7C3cF26E11FCAf296DfC8807C01073'))
    and contract_address = '0x7ceb23fd6bc0add59e62ac25578270cff1b9f619'
    group by 1
    )
    Run a query to Download Data