crypto_edgarTotal Investors
    Updated 2023-06-29
    WITH mooar_mic_drops_transfers as (
    SELECT
    DATE_TRUNC('Day', BLOCK_TIMESTAMP) AS DAY,
    INSTRUCTION:accounts[0] as wallet_address,
    value as tmp_instruction,
    (tmp_instruction:parsed:info:amount)::float / POW(10, 9) as amount_gmt
    FROM
    solana.core.fact_events as events,
    lateral flatten(input => events.INNER_INSTRUCTION:instructions)
    WHERE BLOCK_TIMESTAMP BETWEEN '2023-06-20 00:00:00' AND CURRENT_TIMESTAMP()
    AND PROGRAM_ID = 'E74bvE68HWB2bsdRiFdX55gSHDG7wWHhy8DhfjQs3iyB'
    AND INSTRUCTION:accounts[2] = '7v34pHCKLtuZCopCmvaLFGPGewsG1PWUA9wd5PAWFNRH'
    AND tmp_instruction:parsed:type = 'transfer'
    AND tmp_instruction:parsed:info:destination = 'C22oU2kXGHEvaTz3KcKAaFv8HJKYyddvWEiyPQpwGrce'
    AND SUCCEEDED = 'True'
    ORDER BY amount_gmt),
    mooar_mic_drops_rolling_amounts as (
    SELECT
    DATE_TRUNC('Day', BLOCK_TIMESTAMP) AS DAY,
    SIGNERS[0] as wallet_address,
    value AS matched_message,
    (SUBSTRING(log.value, POSITION('rolling amount: ' IN log.value) + LENGTH('rolling amount: ')))::float / POW(10, 9) AS amount_gmt
    FROM
    solana.core.fact_transactions,
    LATERAL FLATTEN(input => LOG_MESSAGES) log
    WHERE
    BLOCK_TIMESTAMP BETWEEN '2023-06-20 00:00:00' AND CURRENT_TIMESTAMP()
    AND SUCCEEDED = 'True'
    AND matched_message LIKE '%rolling amount: %'
    ),
    final_table as (
    (
    SELECT *
    FROM mooar_mic_drops_transfers
    )
    UNION
    Run a query to Download Data