crypto_edgarDormant Wallet Addresses
    Updated 2024-03-01
    with defi_pirates_call as (
    SELECT
    VALUE:MINT :: STRING AS MINT,
    VALUE:LAST_TX_TO :: STRING AS HOLDER
    FROM
    (
    select
    *
    from
    (
    select
    livequery.live.udf_api(
    'https://flipsidecrypto.xyz/api/v1/queries/18b16e6b-a1d3-41bc-a9ec-3eba25005806/data/latest'
    ) :data as data
    )
    ) response,
    LATERAL FLATTEN(INPUT => PARSE_JSON(response.data))
    ),
    transactions_last_6_months AS (
    SELECT
    f.BLOCK_TIMESTAMP,
    f.TX_FROM
    FROM
    solana.core.fact_transfers f
    WHERE
    f.BLOCK_TIMESTAMP >= DATEADD(MONTH, -6, CURRENT_DATE())
    ),
    dormant_wallets as (
    SELECT
    DISTINCT dpc.HOLDER
    FROM
    defi_pirates_call dpc
    LEFT JOIN transactions_last_6_months tl6m ON dpc.HOLDER = tl6m.TX_FROM
    WHERE
    tl6m.TX_FROM IS NULL
    )
    QueryRunArchived: QueryRun has been archived