with nft_sales as (

-- Find opensea transactions with transfers that aren't for the opensea contract fee, which would be the nfts

select distinct block_timestamp, tx_id, contract_address nft_address

from flipside_prod_db.polygon.udm_events

where date(block_timestamp) >= date('2022-06-06')

and origin_address in ('0x110b2b128a9ed1be5ef3232d8e4e41640df5c2cd','0x9b814233894cd227f561b78cc65891aa55c62ad2') -- opensea main contracts

and contract_address != '0xf715beb51ec8f63317d66f491e37e7bb048fcc2d' -- opensea contract for fees

and from_address is null

and lower(event_name) like '%transfer%'

and amount is null

),

fees as (

-- Fees for each nft txn for opensea wallet

select

s.nft_address

,date(s.block_timestamp) dt

,s.tx_id

,e.to_address opensea_wallet

,e.contract_address token_address

,e.amount token_amount

from nft_sales s

left join flipside_prod_db.polygon.udm_events e

on s.tx_id = e.tx_id

and date(e.block_timestamp) >= date('2022-06-06')

and event_name = 'transfer'

and from_address = '0xf715beb51ec8f63317d66f491e37e7bb048fcc2d' -- opensea contract for fees

and to_address in ('0x8de9c5a032463c561423387a9648c5c7bcc5bc90','0x5b3256965e7c3cf26e11fcaf296dfc8807c01073') -- opensea_wallets

),

token_price as (

-- Getting average token price from sushiswap

select date(block_timestamp) dt, token_in, symbol_in, avg(amount_out_usd/amount_in) avg_usd

from polygon.sushi.ez_swaps

where date(block_timestamp) >= date('2022-06-06')