x.sql

WITH combined_keys AS (
    -- Feed v3 via account_activity (2024+)
    SELECT DISTINCT t.signer, aa.address AS key
    FROM solana.transactions t, solana.account_activity aa
    WHERE t.block_date >= TIMESTAMP '2024-06-01'
      AND aa.block_time >= TIMESTAMP '2024-06-01'
      AND aa.tx_id = t.id

    UNION ALL

    -- Feed v2 via account_activity (2022–2024)
    SELECT DISTINCT t.signer, aa.address AS key
    FROM solana.transactions t, solana.account_activity aa
    WHERE t.block_date BETWEEN DATE '2022-01-01' AND DATE '2024-07-01'
      AND aa.block_time BETWEEN DATE '2022-01-01' AND DATE '2024-07-01'
      AND aa.tx_id = t.id

    UNION ALL

    -- Feed v1 via account_keys (2021–2022)
    SELECT DISTINCT signer, key
    FROM solana.transactions
    CROSS JOIN UNNEST(account_keys) AS t(key)
    WHERE block_time BETWEEN TIMESTAMP '2021-05-01' AND TIMESTAMP '2022-12-31'

    UNION ALL

    -- Marginfi banks via account_keys (2024+)
    SELECT DISTINCT signer, key
    FROM solana.transactions
    CROSS JOIN UNNEST(account_keys) AS t(key)
    WHERE block_time >= TIMESTAMP '2024-06-01'
)
SELECT COUNT(DISTINCT ck.signer) AS unique_signers
FROM combined_keys ck
JOIN (
    SELECT feed FROM dune.switchboard.result_sb_v_3_feeds
    UNION
    SELECT feed FROM dune.switchboard.result_sb_v_2_feeds
    UNION
    SELECT feed FROM dune.switchboard.result_sb_v_1_feeds
    UNION
    SELECT feed FROM dune.switchboard.result_sb_v_3_marginfi_banks
) f
  ON ck.key = f.feed;