flipside

    Specific pack query 2.0
    ralphpescado-BUqc_HSpecific pack query 2.0
    Updated 2025-03-20
    -- Step 1: Create CTE for Packs with Mint events
    WITH Packs AS (
    SELECT
    fe.event_data:id::STRING AS packId,
    CONVERT_TIMEZONE('UTC', 'America/Los_Angeles', fe.block_timestamp) AS mintedTimestamp
    FROM
    flow.core.fact_events fe
    WHERE
    fe.event_type = 'Mint'
    AND fe.event_data:distId = '5043'
    AND fe.Event_contract = 'A.e4cf4bdc1751c65d.PackNFT'
    ),

    -- Step 2: Create CTE for last Deposit event for each pack
    LastDeposit AS (
    SELECT
    fe.event_data:id::STRING AS packId,
    fe.event_data:to AS currentOwner,
    ROW_NUMBER() OVER (PARTITION BY fe.event_data:id ORDER BY fe.block_timestamp DESC) AS rn
    FROM
    flow.core.fact_events fe
    WHERE
    fe.event_type = 'Deposit'
    AND fe.event_data:distId = '4078'
    AND fe.Event_contract = 'A.e4cf4bdc1751c65d.PackNFT'
    )
    ,
    -- Step 3: Create CTE for last Revealed event details for each pack
    LastRevealed AS (
    SELECT
    fe.event_data:id::STRING AS packId,
    REPLACE(fe.event_data:nfts, 'A.e4cf4bdc1751c65d.AllDay.', '') AS NFTs,
    fe.event_data:salt AS Salt,
    ROW_NUMBER() OVER (PARTITION BY fe.event_data:id ORDER BY fe.block_timestamp DESC) AS rn
    FROM
    flow.core.fact_events fe
    Last run: about 1 month ago
    PACKID
    MINTEDTIMESTAMP
    CURRENTOWNER
    NFTS
    SALT
    1
    		1638272339328562025-03-12 12:36:25.087
    2
    		1638272339328582025-03-12 12:36:25.087
    3
    		1638272339328602025-03-12 12:36:25.087
    4
    		1638272339328622025-03-12 12:36:25.087
    5
    		1638272339328642025-03-12 12:36:25.087
    6
    		1638272339328662025-03-12 12:36:25.087
    7
    		1638272339328682025-03-12 12:36:25.087
    8
    		1638272339328702025-03-12 12:36:25.087
    9
    		1638272339328722025-03-12 12:36:25.087
    10
    		1638272339328742025-03-12 12:36:25.087
    11
    		1638272339328762025-03-12 12:36:25.087
    12
    		1638272339328782025-03-12 12:36:25.087
    13
    		1638272339328802025-03-12 12:36:25.087
    14
    		1638272339328822025-03-12 12:36:25.087
    15
    		1638272339328842025-03-12 12:36:25.087
    16
    		1638272339328862025-03-12 12:36:25.087
    17
    		1638272339328882025-03-12 12:36:25.087
    18
    		1638272339328902025-03-12 12:36:25.087
    19
    		1638272339328922025-03-12 12:36:25.087
    20
    		1638272339328942025-03-12 12:36:25.087
    ...
    2230
    132KB
    65s