Zanyar_98Total amount of Opensea fees on Polygon (in USD)
Updated 2022-08-02Copy Reference Fork
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
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