Building Institutional-Grade x402 Analytics on BigQuery

SELECT
  chain,
  COUNT(*) AS total_tx,
  SUM(amount_usdc) AS total_vol
FROM (
  SELECT 'solana' as chain, amount_usdc
  FROM `web3-publicgoods.tektonic_x402.sol_raw`
  WHERE tx_status = 'SUCCESS'
  UNION ALL
  SELECT 'base' as chain, amount_usdc
  FROM `web3-publicgoods.tektonic_x402.base_raw`
  WHERE tx_status = 1
)
WHERE block_timestamp >= TIMESTAMP("2024-01-01")
GROUP BY chain;

SELECT
  chain,
  COUNT(*) AS successful_txns,
  ROUND(SUM(amount_usdc), 2) AS volume_usdc,
  ROUND(AVG(amount_usdc), 4) AS avg_tx_usdc
FROM (
  SELECT 'solana' AS chain, amount_usdc
  FROM `web3-publicgoods.tektonic_x402.sol_raw`
  WHERE tx_status = 'SUCCESS'
    AND block_timestamp >= TIMESTAMP("2026-01-01")
    AND block_timestamp <  TIMESTAMP("2026-02-01")
  UNION ALL
  SELECT 'base' AS chain, amount_usdc
  FROM `web3-publicgoods.tektonic_x402.base_raw`
  WHERE tx_status = 1
    AND block_timestamp >= TIMESTAMP("2026-01-01")
    AND block_timestamp <  TIMESTAMP("2026-02-01")
)
GROUP BY chain;

SELECT
  DATE(block_timestamp) AS day,
  COUNT(*)              AS total_txns,
  COUNTIF(tx_status = 'SUCCESS') AS success_txns,
  ROUND(COUNTIF(tx_status = 'SUCCESS') / COUNT(*) * 100, 2)
                        AS success_rate_pct,
  ROUND(SUM(IF(tx_status = 'SUCCESS', amount_usdc, 0)), 2)
                        AS volume_usdc
FROM `web3-publicgoods.tektonic_x402.sol_raw`
WHERE block_timestamp >= TIMESTAMP("2026-03-01")
  AND block_timestamp <  TIMESTAMP("2026-04-01")
GROUP BY day
ORDER BY day DESC;

SELECT
  facilitator_signer,
  COUNT(*)                    AS total_txns,
  ROUND(SUM(amount_usdc), 2) AS total_volume_usdc,
  COUNT(DISTINCT transaction_from) AS unique_buyers,
  MIN(block_timestamp)        AS first_seen
FROM `web3-publicgoods.tektonic_x402.sol_raw`
WHERE block_timestamp >= TIMESTAMP("2026-03-01")
  AND tx_status = 'SUCCESS'
GROUP BY facilitator_signer
ORDER BY total_volume_usdc DESC
LIMIT 20;

WITH combined AS (
  SELECT 'solana' AS chain, block_timestamp, amount_usdc
  FROM `web3-publicgoods.tektonic_x402.sol_raw`
  WHERE tx_status = 'SUCCESS'
    AND block_timestamp >= TIMESTAMP("2026-03-01")
  UNION ALL
  SELECT 'base' AS chain, block_timestamp, amount_usdc
  FROM `web3-publicgoods.tektonic_x402.base_raw`
  WHERE tx_status = 1
    AND block_timestamp >= TIMESTAMP("2026-03-01")
)
SELECT
  chain,
  DATE(block_timestamp)       AS day,
  COUNT(*)                    AS txns,
  ROUND(SUM(amount_usdc), 2) AS volume_usdc
FROM combined
GROUP BY chain, day
ORDER BY day DESC, chain;

SELECT
  DATE(block_timestamp) AS day,
  COUNT(*)              AS total,
  COUNTIF(tx_status = 'FAILED') AS failed,
  ROUND(COUNTIF(tx_status = 'FAILED') / COUNT(*) * 100, 4)
                        AS failure_rate_pct
FROM `web3-publicgoods.tektonic_x402.sol_raw`
WHERE block_timestamp >= TIMESTAMP("2026-03-25")
GROUP BY day
ORDER BY day DESC;

SELECT
  tx_signature,
  block_timestamp,
  destination_ata    AS seller,
  facilitator_signer AS facilitator,
  amount_usdc,
  tx_status
FROM `web3-publicgoods.tektonic_x402.sol_raw`
WHERE transaction_from = '< WALLET_ADDRESS >'
  AND block_timestamp >= TIMESTAMP("2026-01-01")
ORDER BY block_timestamp DESC;