Zanyar_98Total amount of Opensea fees on Polygon (in USD)
    Updated 2022-08-02
    WITH WETH_PRICE AS (SELECT flipside_prod_db.polygon.udm_events.BLOCK_TIMESTAMP::DATE AS DAYS, SUM(AMOUNT) "WETH amount", AVG(PRICE) "WETH price", COUNT(DISTINCT(TX_ID)) "Number of transactions"
    FROM flipside_prod_db.polygon.udm_events
    JOIN ethereum.core.fact_hourly_token_prices
    ON ethereum.core.fact_hourly_token_prices.HOUR::DATE = flipside_prod_db.polygon.udm_events.BLOCK_TIMESTAMP::DATE
    WHERE EVENT_TYPE = 'erc20_transfer' AND FROM_ADDRESS = '0xf715beb51ec8f63317d66f491e37e7bb048fcc2d'
    AND (TO_ADDRESS = '0x5b3256965e7c3cf26e11fcaf296dfc8807c01073' OR TO_ADDRESS = '0x8de9c5a032463c561423387a9648c5c7bcc5bc90')
    AND CONTRACT_ADDRESS = '0x7ceb23fd6bc0add59e62ac25578270cff1b9f619' AND AMOUNT_USD IS NULL AND ethereum.core.fact_hourly_token_prices.SYMBOL IS NULL AND token_address IS NULL
    GROUP BY DAYS),

    WETH_USD_FEES AS
    ( SELECT flipside_prod_db.polygon.udm_events.BLOCK_TIMESTAMP::DATE AS DAYS, SUM(AMOUNT_USD) "Fees", 'WETH Fees' AS TYPE, COUNT(DISTINCT(TX_ID)) "Number of transactions"
    FROM flipside_prod_db.polygon.udm_events
    WHERE EVENT_TYPE = 'erc20_transfer' AND FROM_ADDRESS = '0xf715beb51ec8f63317d66f491e37e7bb048fcc2d'
    AND (TO_ADDRESS = '0x5b3256965e7c3cf26e11fcaf296dfc8807c01073' OR TO_ADDRESS = '0x8de9c5a032463c561423387a9648c5c7bcc5bc90')
    AND CONTRACT_ADDRESS = '0x7ceb23fd6bc0add59e62ac25578270cff1b9f619' AND AMOUNT_USD IS NOT NULL GROUP BY DAYS),

    FIXED_WETH_PRICE AS (SELECT DAYS, "WETH amount" * "WETH price" "Fees", 'WETH Fees' AS TYPE, "Number of transactions" FROM WETH_PRICE ),
    WETH_FEES AS (SELECT * FROM FIXED_WETH_PRICE
    UNION ALL
    SELECT * FROM WETH_USD_FEES),
    STABLECOINS_FEES AS (SELECT flipside_prod_db.polygon.udm_events.BLOCK_TIMESTAMP::DATE AS DAYS, SUM(AMOUNT) "Fees", 'Stablecoins Fees' AS TYPE, COUNT(DISTINCT(TX_ID)) "Number of transactions"
    FROM flipside_prod_db.polygon.udm_events
    WHERE EVENT_TYPE = 'erc20_transfer' AND FROM_ADDRESS = '0xf715beb51ec8f63317d66f491e37e7bb048fcc2d' AND
    (TO_ADDRESS = '0x5b3256965e7c3cf26e11fcaf296dfc8807c01073' OR TO_ADDRESS = '0x8de9c5a032463c561423387a9648c5c7bcc5bc90')
    AND (CONTRACT_ADDRESS = '0x2791bca1f2de4661ed88a30c99a7a9449aa84174' OR CONTRACT_ADDRESS = '0x8f3cf7ad23cd3cadbd9735aff958023239c6a063') GROUP BY DAYS),


    TT AS (SELECT SUM("Fees") "FEE", SUM("Number of transactions") AS TOTAL_NUMBER_OF_TRANSACTIONS FROM WETH_FEES
    UNION ALL
    SELECT SUM("Fees") "FEE", SUM("Number of transactions") AS TOTAL_NUMBER_OF_TRANSACTIONS FROM STABLECOINS_FEES)

    SELECT SUM("FEE"), SUM(TOTAL_NUMBER_OF_TRANSACTIONS) FROM TT


    Run a query to Download Data