jp126 [Polygon] Opensea Fees - Overall collected
    Updated 2022-09-03
    WITH prices as (
    SELECT date_trunc('day', hour) as date, symbol, avg(price) as daily_avg_price
    FROM ethereum.core.fact_hourly_token_prices
    WHERE token_address IN ('0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2', '0x6b175474e89094c44da98b954eedeac495271d0f', '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48', '0x7d1afa7b718fb893db30a3abc0cfc608aacfebb0') -- Equivalent Tokens on ETH
    GROUP BY 1, 2
    )
    , raw as (
    SELECT block_timestamp::date as date, tx_id, EVENT_INPUTS:from as from_address, EVENT_INPUTS:to as to_address,
    CASE -- Tokens on Polygon
    WHEN CONTRACT_ADDRESS = '0x7ceb23fd6bc0add59e62ac25578270cff1b9f619' THEN 'WETH'
    WHEN CONTRACT_ADDRESS = '0x8f3cf7ad23cd3cadbd9735aff958023239c6a063' THEN 'DAI'
    WHEN CONTRACT_ADDRESS = '0x2791bca1f2de4661ed88a30c99a7a9449aa84174' THEN 'USDC'
    WHEN CONTRACT_ADDRESS = '0x0d500b1d8e8ef31e21c99d1db9a6444d3adf1270' THEN 'MATIC'
    END as token,
    CASE CONTRACT_ADDRESS
    WHEN '0x7ceb23fd6bc0add59e62ac25578270cff1b9f619' THEN EVENT_INPUTS:value / 1e18
    WHEN '0x8f3cf7ad23cd3cadbd9735aff958023239c6a063' THEN EVENT_INPUTS:value / 1e18
    WHEN '0x2791bca1f2de4661ed88a30c99a7a9449aa84174' THEN EVENT_INPUTS:value / 1e6
    WHEN '0x0d500b1d8e8ef31e21c99d1db9a6444d3adf1270' THEN EVENT_INPUTS:value / 1e18
    WHEN '0xd93f7E271cB87c23AaA73edC008A79646d1F9912' THEN EVENT_INPUTS:value / 1e9
    END as sale_amount
    FROM flipside_prod_db.polygon.events_emitted
    WHERE TX_TO_ADDRESS = '0xf715beb51ec8f63317d66f491e37e7bb048fcc2d' and EVENT_NAME = 'Transfer'
    and (from_address <> '0xf715beb51ec8f63317d66f491e37e7bb048fcc2d' and to_address <> '0xf715beb51ec8f63317d66f491e37e7bb048fcc2d') and sale_amount < 100000 and TX_SUCCEEDED = 'TRUE'
    and CONTRACT_ADDRESS IN ('0x7ceb23fd6bc0add59e62ac25578270cff1b9f619', '0x8f3cf7ad23cd3cadbd9735aff958023239c6a063', '0x2791bca1f2de4661ed88a30c99a7a9449aa84174', '0x0d500b1d8e8ef31e21c99d1db9a6444d3adf1270')
    -- UNION
    -- SELECT block_timestamp::date as date, tx_id, EVENT_INPUTS:"_from" as from_address, EVENT_INPUTS:"_to" as to_address, EVENT_INPUTS:value / 1e18 as weth,
    -- contract_address as collection_id, CONTRACT_NAME as collection_name
    -- FROM flipside_prod_db.polygon.events_emitted
    -- WHERE TX_TO_ADDRESS = '0xf715beb51ec8f63317d66f491e37e7bb048fcc2d' and EVENT_NAME = 'TransferBatch' and TX_SUCCEEDED = 'TRUE'
    )

    , joined as (
    Run a query to Download Data