Updated 2024-09-25
    with pallet as ( select DISTINCT tx_id,
    msg_type
    from sei.core.fact_msg_attributes
    where ATTRIBUTE_VALUE in ('sei152u2u0lqc27428cuf8dx48k8saua74m6nql5kgvsu4rfeqm547rsnhy4y9')
    and ATTRIBUTE_KEY = '_contract_address'
    and msg_type in ('wasm-buy_now','wasm-accept_bid')
    and TX_SUCCEEDED = 'true'
    and block_timestamp::date >= current_date - 31)
    ,
    seller as ( select date(block_timestamp) as date, tx_id, msg_index, ATTRIBUTE_VALUE as seller
    from sei.core.fact_msg_attributes
    where tx_id in (select tx_id from pallet)
    and ATTRIBUTE_KEY = 'nft_seller'
    and block_timestamp::date >= current_date - 31)
    ,
    nft_id as ( select date, a.tx_id, a.msg_index,seller, ATTRIBUTE_VALUE as token_id
    from sei.core.fact_msg_attributes a join seller b on a.tx_id = b.tx_id and a.msg_index = b.msg_index
    where ATTRIBUTE_KEY = 'nft_token_id'
    and block_timestamp::date >= current_date - 31)
    ,
    collection as ( select date, a.tx_id, a.msg_index,seller, token_id, ATTRIBUTE_VALUE as collection_address
    from sei.core.fact_msg_attributes a join nft_id b on a.tx_id = b.tx_id and a.msg_index = b.msg_index
    where ATTRIBUTE_KEY = 'nft_address'
    and block_timestamp::date >= current_date - 31)
    ,
    price as ( select date, a.tx_id, a.msg_index,seller, token_id, collection_address, case when attribute_value ilike 'native:%' then substr(attribute_value, 13,24)
    else split(attribute_value, 'usei')[0] end as prices, prices/pow(10,6) as price
    from sei.core.fact_msg_attributes a join collection b on a.tx_id = b.tx_id and a.msg_index = b.msg_index
    where ATTRIBUTE_KEY = 'sale_price'
    and block_timestamp::date >= current_date - 31)
    ,
    buyer as ( select a.block_timestamp, 'Pallet' as platform, a.tx_id, a.msg_index,seller, token_id, collection_address, price , ATTRIBUTE_VALUE as buyer
    from sei.core.fact_msg_attributes a join price b on a.tx_id = b.tx_id
    where ATTRIBUTE_KEY in ('fee_payer','sold_to')
    and block_timestamp::date >= current_date - 31
    )
    QueryRunArchived: QueryRun has been archived