amelia-leeUntitled Query
Updated 2022-08-01
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 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