x.sql

-- v1: May 2021 - Dec 2022
WITH
tx_signers_v1 AS (
  SELECT DISTINCT tx_id, address AS signer
  FROM solana.account_activity
  WHERE signed = TRUE AND block_time BETWEEN TIMESTAMP '2021-05-01' AND TIMESTAMP '2022-12-31'
),
combined_keys_v1 AS (
  SELECT DISTINCT t.signer, aa.address AS key
  FROM solana.account_activity aa
  JOIN tx_signers_v1 t ON t.tx_id = aa.tx_id
  WHERE aa.block_time BETWEEN TIMESTAMP '2021-05-01' AND TIMESTAMP '2022-12-31'
),
v1_signers AS (
  SELECT DISTINCT ck.signer
  FROM combined_keys_v1 ck
  JOIN dune.switchboard.result_sb_v_1_feeds f ON ck.key = f.feed
),

-- v2: Jan 2022 - May 2024
tx_signers_v2 AS (
  SELECT DISTINCT tx_id, address AS signer
  FROM solana.account_activity
  WHERE signed = TRUE AND block_time BETWEEN TIMESTAMP '2022-01-01' AND TIMESTAMP '2024-05-01'
),
combined_keys_v2 AS (
  SELECT DISTINCT t.signer, aa.address AS key
  FROM solana.account_activity aa
  JOIN tx_signers_v2 t ON t.tx_id = aa.tx_id
  WHERE aa.block_time BETWEEN TIMESTAMP '2022-01-01' AND TIMESTAMP '2024-05-01'
),
v2_signers AS (
  SELECT DISTINCT ck.signer
  FROM combined_keys_v2 ck
  JOIN dune.switchboard.result_sb_v_2_feeds f ON ck.key = f.feed
),

-- v3: May 2024 - now
tx_signers_v3 AS (
  SELECT DISTINCT tx_id, address AS signer
  FROM solana.account_activity
  WHERE signed = TRUE AND block_time >= TIMESTAMP '2024-05-01'
),
combined_keys_v3 AS (
  SELECT DISTINCT t.signer, aa.address AS key
  FROM solana.account_activity aa
  JOIN tx_signers_v3 t ON t.tx_id = aa.tx_id
  WHERE aa.block_time >= TIMESTAMP '2024-05-01'
),
v3_signers AS (
  SELECT DISTINCT ck.signer
  FROM combined_keys_v3 ck
  JOIN (
    SELECT feed FROM dune.switchboard.result_sb_v_3_feeds
    UNION
    SELECT feed FROM dune.switchboard.result_sb_v_3_marginfi_banks
  ) f ON ck.key = f.feed
)

-- Final aggregation
SELECT COUNT(DISTINCT signer) AS unique_signers
FROM (
  SELECT signer FROM v1_signers
  UNION
  SELECT signer FROM v2_signers
  UNION
  SELECT signer FROM v3_signers
) all_signers;