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;

-- Query to find the 100 millionth contract invocation.

WITH txs AS
(
  SELECT
    transaction_hash,
    row_number() over (ORDER BY id) AS row_num
  FROM `crypto-stellar.crypto_stellar.history_transactions`
  WHERE operation_count = 1
  AND soroban_resources_instructions > 0
  AND successful = TRUE
)
SELECT transaction_hash
FROM txs
WHERE row_num = 100000000

SELECT
  asset_code,
  asset_issuer,
  COUNT(asset_code) AS cnt
FROM
  `crypto-stellar.crypto_stellar.trust_lines`
WHERE
  trust_line_limit < 9223372036854775807
GROUP BY
  asset_code,
  asset_issuer
ORDER BY
  cnt DESC
LIMIT 1000;