select

tx_hash,

contract_address as collection_address

from ethereum.core.fact_event_logs

where 1 = 1

-- and tx_hash = '0xb6144e77fef84a41c7d6a60e110b6ac002cae697764dfcac0da4ffc27cf4fefe'

and origin_function_signature = '0x3cebcf41' -- create sovereign NFT / transfer ownership

and event_inputs:newOwner in ( '0x2c325156416cc7b701151dafb6d4c0bdf0d398f0' -- super rare contract to create NFTs

, '0xa5f6535f186a54322ba68bc77f0c4bb1d59024cf') -- old superrare sovereign contract

and event_inputs:previousOwner = '0x0000000000000000000000000000000000000000' -- nft is minted from 0x00 then sent to this contract before sending to minter

),

sovereign_nft_collection_labels as ( -- deciphering the collection name and token ticker

tx_hash,

(len(input_data)-10)/64 as n,

try_hex_decode_string (substr(input_data, 203, (n-5)*64) ) as collection_name,

try_hex_decode_string (substr(input_data, ((n-1)*64 + 11), 64)) as collection_ticker

from ethereum.core.fact_transactions

where tx_hash in (select tx_hash from sovereign_nft_collection_create_contract)

),

sovereign_collection_minting_raw as

(

select

l.tx_hash,

block_timestamp,

event_inputs:newOwner as collection_creator,

contract_address as collection_contract_address,

collection_name,

collection_ticker

from ethereum.core.fact_event_logs l

left join sovereign_nft_collection_labels on sovereign_nft_collection_labels.tx_hash = l.tx_hash