CASE

WHEN contract_address = '0x8d0501d85becda92b89e56177ddfcea5fc1f0af2' then 'The Senses'

end as collection,

*

from polygon.core.fact_event_logs

WHERE EVENT_REMOVED = 'false' and TX_STATUS = 'SUCCESS' and contract_address = '0x8d0501d85becda92b89e56177ddfcea5fc1f0af2' and date_trunc('day', BLOCK_TIMESTAMP) >= '2022-08-19'

),

df as (

SELECT

--date_trunc('day',a.BLOCK_TIMESTAMP) as date,

a.BLOCK_TIMESTAMP,

a.TX_HASH as TX_HASH,

a.CONTRACT_ADDRESS as CONTRACT_ADDRESS,

a.collection as collection,

c.FROM_ADDRESS as sellers,

c.TO_ADDRESS as buyers,

c.RAW_AMOUNT/pow(10, 18) as amount_eth,

b.TX_FEE as TX_FEE,

b.GAS_PRICE as GAS_PRICE,

b.GAS_USED as GAS_USED

from df1 a join polygon.core.fact_transactions b on a.BLOCK_TIMESTAMP = b.BLOCK_TIMESTAMP and a.TX_HASH = b.TX_HASH

join polygon.core.fact_token_transfers c on b.BLOCK_TIMESTAMP = c.BLOCK_TIMESTAMP and b.TX_HASH = c.tx_hash)

select

distinct sellers as Seller,

count(DISTINCT tx_hash) as tx_total,

sum(amount_eth) as sales_volume_eth,

avg(amount_eth) as avg_price_eth

from df

group by seller

order by sales_volume_eth desc