boomer77Opensea Polygon Collected Fees
Updated 2021-10-03
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
›
⌄
with opensea_fees as (select date_trunc('week', block_timestamp) as block_week, sum(event_inputs:value/1e18) as fees, contract_name
from polygon.events_emitted
where event_inputs:to::string = '0x5b3256965e7c3cf26e11fcaf296dfc8807c01073' --opensea wallet address
and contract_name in ('dai','weth')
group by 1,3),
eth as (select date_trunc('week', hour) as block_week, avg(price) as price
from ethereum.token_prices_hourly
where symbol = 'ETH'
group by 1),
final as (select a.block_week, a.fees, a.contract_name, b.price
from opensea_fees a
join eth b on a.block_week = b.block_week)
select *, case when contract_name = 'weth' then (fees*price) else fees end as fees_usd
from final