DiamondFlowty: Top Shot
    Updated 2024-03-15
    with main as (
    select
    CONVERT_TIMEZONE('UTC', 'America/Los_Angeles', BLOCK_TIMESTAMP) AS "Date",
    TX_ID,
    'Offer' as "Sale Type",
    split_part(NFT_COLLECTION, '.', 3) as COLLECTION_name,
    BUYER,
    --SELLER,
    CAST(event_data:"offerAmount" AS DECIMAL(18, 2)) AS "Price"
    FROM
    FLOW.CORE.FACT_EVENTS
    left join flow.nft.ez_nft_sales using(tx_id)
    WHERE
    EVENT_CONTRACT IN ('A.b8ea91944fd51c43.OffersV2')
    AND EVENT_TYPE = 'OfferCompleted' --AND BLOCK_TIMESTAMP >= CONVERT_TIMEZONE('America/Los_Angeles', 'UTC', '2023-01-01')
    AND tx_succeeded = 'true'
    AND EVENT_DATA :: string LIKE '%0x6590f8918060ef13%'
    AND EVENT_DATA:purchased LIKE '%true%'
    AND COLLECTION_name IN ('TopShot', 'AllDay', 'JollyJokers', 'Golazos', 'UFC_NFT', 'Flovatar', 'Genies')
    union
    all
    SELECT
    CONVERT_TIMEZONE('UTC', 'America/Los_Angeles', BLOCK_TIMESTAMP) AS "Date",
    TX_ID,
    'Listing' as "Sale Type",
    split_part(event_data:nftType, '.', 3) as COLLECTION_name,
    EVENT_DATA:buyer as BUYER,
    --SELLER,
    CAST(EVENT_DATA:"salePrice" AS DECIMAL(18, 2)) AS "Price"
    FROM
    FLOW.CORE.FACT_EVENTS --left join flow.nft.ez_nft_sales using(tx_id)
    WHERE
    EVENT_CONTRACT IN ('A.3cdbb3d569211ff3.NFTStorefrontV2')
    AND EVENT_TYPE = 'ListingCompleted'
    AND TX_SUCCEEDED = TRUE
    AND EVENT_DATA:purchased :: string = 'true' --AND block_timestamp > CONVERT_TIMEZONE('America/Los_Angeles', 'UTC', '2023-01-01 00:00:00')
    QueryRunArchived: QueryRun has been archived