Leigh's Stellar BigQuery Queries

-- Query to extract and summarize types from the key_decoded field
-- Analyzes the type distribution in contract events data

WITH
-- Extract types from key_decoded field
key_decoded_types AS (
  SELECT
    -- Extract the type from key_decoded, or use value if type is empty
    CASE
      WHEN JSON_VALUE(key_decoded, '$.type') = ''
      THEN REGEXP_EXTRACT(JSON_VALUE(key_decoded, '$.value'), r'\(([^)]+)\)')
      ELSE JSON_VALUE(key_decoded, '$.type')
    END AS key_type,
    key_decoded
  FROM
    `crypto-stellar.crypto_stellar.contract_data`
  WHERE
    key_decoded IS NOT NULL
),

-- First, collect type counts
type_counts AS (
  SELECT
    key_type,
    COUNT(*) AS count
  FROM
    key_decoded_types
  GROUP BY
    key_type
),

-- Get sample key_decoded for each type
type_samples AS (
  SELECT
    key_type,
    ARRAY_AGG(key_decoded ORDER BY RAND() LIMIT 1)[OFFSET(0)] AS sample_key_decoded
  FROM
    key_decoded_types
  GROUP BY
    key_type
)

-- Join the results together
SELECT
  c.key_type,
  c.count,
  s.sample_key_decoded
FROM
  type_counts c
JOIN
  type_samples s ON c.key_type = s.key_type
ORDER BY
  c.count DESC;

-- Query to extract and summarize types from the val_decoded field
-- Analyzes the type distribution in contract events data

WITH
-- Extract types from val_decoded field
val_decoded_types AS (
  SELECT
    -- Extract the type from val_decoded, or use value if type is empty
    CASE
      WHEN JSON_VALUE(val_decoded, '$.type') = ''
      THEN REGEXP_EXTRACT(JSON_VALUE(val_decoded, '$.value'), r'\(([^)]+)\)')
      ELSE JSON_VALUE(val_decoded, '$.type')
    END AS val_type,
    val_decoded
  FROM
    `crypto-stellar.crypto_stellar.contract_data`
  WHERE
    val_decoded IS NOT NULL
),

-- First, collect type counts
type_counts AS (
  SELECT
    val_type,
    COUNT(*) AS count
  FROM
    val_decoded_types
  GROUP BY
    val_type
),

-- Get sample val_decoded for each type
type_samples AS (
  SELECT
    val_type,
    ARRAY_AGG(val_decoded ORDER BY RAND() LIMIT 1)[OFFSET(0)] AS sample_val_decoded
  FROM
    val_decoded_types
  GROUP BY
    val_type
)

-- Join the results together
SELECT
  c.val_type,
  c.count,
  s.sample_val_decoded
FROM
  type_counts c
JOIN
  type_samples s ON c.val_type = s.val_type
ORDER BY
  c.count DESC;

-- Query to extract and summarize types from the data_decoded field
-- Analyzes the type distribution in contract events data

WITH
-- Extract types from data_decoded field
data_decoded_types AS (
  SELECT
    -- Extract the type from data_decoded
    JSON_VALUE(data_decoded, '$.type') AS data_type,
    data_decoded
  FROM
    `crypto-stellar.crypto_stellar.history_contract_events`
  WHERE
    data_decoded IS NOT NULL
    AND in_successful_contract_call = TRUE
    AND JSON_VALUE(data_decoded, '$.type') IS NOT NULL
    AND type_string = 'ContractEventTypeContract'
),

-- First, collect type counts
type_counts AS (
  SELECT
    data_type,
    COUNT(*) AS count
  FROM
    data_decoded_types
  GROUP BY
    data_type
),

-- Get sample data_decoded for each type
type_samples AS (
  SELECT
    data_type,
    ARRAY_AGG(data_decoded ORDER BY RAND() LIMIT 1)[OFFSET(0)] AS sample_data_decoded
  FROM
    data_decoded_types
  GROUP BY
    data_type
)

-- Join the results together
SELECT
  c.data_type,
  c.count,
  s.sample_data_decoded
FROM
  type_counts c
JOIN
  type_samples s ON c.data_type = s.data_type
ORDER BY
  c.count DESC;

-- Query to extract topic types from topics_decoded field and count their occurrences
-- Groups by the combined type signature (e.g., "Sym,Address,Address")
-- Limited to ContractEventTypeContract events

WITH
-- Extract topics_decoded and create a string of all types in each event
events_with_type_signature AS (
  SELECT
    contract_id,
    topics_decoded,
    -- Extract each type from the topics_decoded array and join them with commas
    (
      SELECT STRING_AGG(
        JSON_VALUE(topic, '$.type'),
        ','
        ORDER BY OFFSET
      )
      FROM UNNEST(JSON_EXTRACT_ARRAY(topics_decoded, '$.topics_decoded')) AS topic WITH OFFSET
      WHERE JSON_VALUE(topic, '$.type') IS NOT NULL
    ) AS type_signature
  FROM
    `crypto-stellar.crypto_stellar.history_contract_events`
  WHERE
    topics_decoded IS NOT NULL
    AND in_successful_contract_call = TRUE
    AND type_string = 'ContractEventTypeContract'
)

-- Group by type signature, get counts and a sample topics_decoded
SELECT
  type_signature,
  COUNT(*) AS count,
  -- Get a sample of topics_decoded for each type signature
  ARRAY_AGG(topics_decoded ORDER BY RAND() LIMIT 1)[OFFSET(0)] AS sample_topics_decoded
FROM
  events_with_type_signature
WHERE
  type_signature IS NOT NULL
GROUP BY
  type_signature
ORDER BY
  count DESC;