select

BLOCK_TIMESTAMP::date as date,

TX_HASH,

replace(EVENT_RESOURCE,'Event','') as events

from aptos.core.fact_events

where SUCCESS = 'true'

and TX_TYPE = 'user_transaction'

and event_Address = '0x2c7bccf7b31baf770fdbcc768d9e9cb3d87805e255355df5db32ac9a669010a2'

and replace(EVENT_RESOURCE,'Event','') in ('FillCollectionBid','Sell','Buy')),

base2 as (

select

case

when a.EVENT_RESOURCE = 'FillCollectionBidEvent' then 'Fill Collection Bid'

when a.EVENT_RESOURCE = 'SellEvent' then 'Sell'

when a.EVENT_RESOURCE = 'BuyEvent' then 'Buy'

end as action_type,

a.TX_HASH,

a.BLOCK_TIMESTAMP::date as date,

a.EVENT_DATA:amount as nft_count,

case

when EVENT_RESOURCE in ('FillCollectionBidEvent','SellEvent') then a.EVENT_DATA:bid_id

when EVENT_RESOURCE in ('BuyEvent') then a.EVENT_DATA:listing_id

end as ID,

a.EVENT_DATA:seller as seller,

a.EVENT_DATA:buyer as buyer,

a.EVENT_DATA:price/pow(10,8) as price,

to_timestamp(a.EVENT_DATA:timestamp)::date as date_of_list,

to_timestamp(a.EVENT_DATA:deadline)::date as deadline,

case

when EVENT_RESOURCE in ('FillCollectionBidEvent','SellEvent') then

when EVENT_RESOURCE = 'BuyEvent' then EVENT_DATA:token_id:token_data_id:collection

a.EVENT_DATA:token_id:token_data_id:collection as collection_name,

a.EVENT_DATA:token_id:token_data_id:creator as creator,

a.EVENT_DATA:token_id:token_data_id:name as nft_name