DiamondEvaluate Trades : Dapper Sports NBATS
    Updated 2023-11-04
    -- forked from Evaluate Trades : Dapper Sports NFLAD @ https://flipsidecrypto.xyz/edit/queries/1035bf17-a2a1-4022-ab1d-fc6b71eff426

    SELECT
    CONVERT_TIMEZONE('America/Los_Angeles', to_date(BLOCK_TIMESTAMP)) as "Date",
    COUNT(NFT_ID) as "Trades",
    SUBSTRING(NFT_TYPE,
    POSITION('.' IN SUBSTR(NFT_TYPE, POSITION('.' IN NFT_TYPE) + 1)) + 1 + POSITION('.' IN NFT_TYPE),
    LENGTH(NFT_TYPE) - POSITION('.' IN NFT_TYPE) - POSITION('.' IN SUBSTR(NFT_TYPE, POSITION('.' IN NFT_TYPE) + 1))) as "Contract",
    CASE
    WHEN "Contract" = 'TopShot.NFT' THEN 'NBA Top Shot'
    ELSE "Contract"
    END as "Contract Name",
    SUM("Trades") OVER (ORDER BY "Date") as "Total Traded"
    FROM (
    WITH swaps_raw AS (
    SELECT
    FE.TX_ID,
    FE.BLOCK_TIMESTAMP,
    FE.BLOCK_HEIGHT,
    f.*,
    parse_json(f.VALUE) AS payload
    FROM FLOW.CORE.FACT_EVENTS FE,
    table(flatten(event_data)) f
    WHERE event_contract IN ('A.9066631feda9e518.Swap', 'A.15f55a75d7843780.Swap')
    AND event_type = 'ProposalExecuted'
    AND TX_SUCCEEDED = TRUE
    AND FE.BLOCK_TIMESTAMP BETWEEN '2022-11-16' AND '2025-12-31'
    ),

    assets_swapped AS (
    SELECT
    TX_ID,
    CONVERT_TIMEZONE('America/Los_Angeles', BLOCK_TIMESTAMP) as BLOCK_TIMESTAMP,
    BLOCK_HEIGHT,
    CASE WHEN f.VALUE:name = 'leftUserOffer' THEN GET(payload, 3):value:value ELSE GET(payload, 5):value:value END AS sending_user,
    f.VALUE:value:value AS assets_nested
    Run a query to Download Data