jp126 [Polygon] Opensea Fees - Overall collected
Updated 2022-09-03Copy 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 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