select

block_timestamp

, tx_hash

, event_inputs:from as seller

, origin_from_address as purchaser

, event_inputs:tokenId as token_id

, contract_address as nft_address

, matic_value

from polygon.core.fact_event_logs lg inner join polygon.core.fact_transactions tr using(tx_hash)

where

event_inputs:tokenId is not null

and event_inputs:from != '0x0000000000000000000000000000000000000000'

and matic_value>0

and event_inputs:to !='0x0000000000000000000000000000000000000000'

and tr.block_timestamp >= '2021-06-16'

and tx_status = 'SUCCESS'

and event_name = 'Transfer'

and tr.block_timestamp::date < CURRENT_DATE

-- limit 10

select

distinct block_timestamp::date as DAY

, sum(matic_value) over (partition by Day) as daily_sales_volume

, avg(matic_value) over () as nft_avg_price

, sum(tx_fee) over (partition by Day) as daily_sales_feees

, avg(tx_fee) over (partition by Day) as daily_sales_feees_avg

, avg(tx_fee) over () as sales_feees_avg

, count(tx_hash) over (partition by Day) as daily_sales_tx

from

polygon.core.fact_transactions

WHERE tx_hash in (select tx_hash from nft_sales_transactions)

order by day