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'

),

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),

df2 AS (

select

date_trunc('day', BLOCK_TIMESTAMP) as date,

count(distinct buyers) as buyers,

count(distinct sellers) as sellers,

sum(amount_eth) as sales_volume_eth,

sales_volume_eth / LAG(sales_volume_eth) OVER (ORDER BY DATE_TRUNC('day', BLOCK_TIMESTAMP)) - 1 AS sales_volume_perc_change

from df

where contract_address != '0x0000000000000000000000000000000000000000' and buyers != '0x83bfa75c9391dbe19e0a1d2c53c9b6c4baef5ab7' and buyers != '0x0000a26b00c1f0df003000390027140000faa719'

group by date)

SELECT *,