Skip to main content

Handy Snippets & Performance Tips

Handy Snippets​

Convert smallest units → human units​

-- raw_amount / 10^decimals
SELECT
  r.raw_amount,
  r.symbol,
  r.raw_amount / POW(10, t.decimals) AS human_amount
FROM UNNEST([STRUCT<raw_amount NUMERIC, symbol STRING>(1e20, 'WETH')]) r
JOIN `defi_prepared.token_contract` t ON t.symbol = r.symbol
LIMIT 1;

Latest row per entity (any table with datetime)​

SELECT *
FROM `defi_prepared.uniswap_v3_pool_state`
QUALIFY ROW_NUMBER() OVER (PARTITION BY uniswap_v3_pool_id ORDER BY datetime DESC) = 1;

Performance Tips​

  • Filter by time early (tables are time-partitioned on datetime).
  • Join using integer IDs, then select display columns.
  • Use SAFE_DIVIDE / NULLIF to avoid division errors.
  • For "latest", prefer QUALIFY ROW_NUMBER() over nested subqueries.