SELECT DISTINCT

tx_id,

event_attributes:"1_contract_address"::string AS contract

FROM terra.msg_events

WHERE event_attributes:"0_contract_address"::string = 'terra12v8vrgntasf37xpj282szqpdyad7dgmkgnq60j' --the contract address for knowhere

AND event_attributes:"0_action"::string = 'settle'

AND event_type = 'wasm'

and date(block_timestamp) = CURRENT_DATE - 1

),

kwsettle AS (

SELECT

kwsubinfo.contract,

sum(

(nvl(event_attributes:"0_amount"[0]:amount::float,0)

+ nvl(event_attributes:"1_amount"[0]:amount::float,0)

+ nvl(event_attributes:"2_amount"[0]:amount::float,0)) / 1e6

) AS totalAmount

FROM terra.msg_events me, kwsubinfo

WHERE me.tx_id = kwsubinfo.tx_id

--and me.msg_index = 0 --for instances where place bid + settle are in the same tx. This is a 'buy now', not an auction.

AND me.event_type = 'transfer'

GROUP BY kwsubinfo.contract

),

kw_settle_table as (SELECT

contract as NFT_collection, case

when nft_collection = 'terra13ptfhnrl7n2zcq8369s39a5eqg85xxtj77jtuh' then 'Astroverse'

when nft_collection = 'terra1yrlrdx8sgw25urlet5dvxr3uuwnymlyzdra2az' then 'Cool Ape Punk'

when nft_collection = 'terra1p70x7jkqhf37qa7qm4v23g4u4g8ka4ktxudxa7' then 'Levana Meteor Dust'

when nft_collection = 'terra1jp5fjj7rlc0erw4z3qr5zuvmg2w49pfzyhvsnk' then 'wagmimonkeez'

when nft_collection = 'terra1k0y373yxqne22pc9g7jvnr4qclpsxtafevtrpg' then 'Levana Eggs'

when nft_collection = 'terra13qrc9j00lk3x0rvpptzdmwtckfj64d5g6xnrv9' then 'Terra Loot'

when nft_collection = 'terra18g7zaxuju84qzr70sslw4ahjmaehetsg0fd0y9' then 'Terranime Punks'

when nft_collection = 'terra16h5elefh6y054a6tjvkw5zknlvnytw5rt842rc' then 'Terrasaurs'