WITH
z AS -- TODO better to use percentiles
(
  SELECT
    tok.address,
    COUNT(tok.address) AS n,
    AVG(SAFE_CAST(tx.value AS NUMERIC)/POWER(10,CAST(tok.decimals AS NUMERIC))) AS mu,
    STDDEV(SAFE_CAST(tx.value AS NUMERIC)/POWER(10,CAST(tok.decimals AS NUMERIC))) AS sigma
  FROM `crypto-etl-ethereum-dev.crypto_ethereum.tokens` AS tok,
       `crypto-etl-ethereum-dev.crypto_ethereum.token_transfers` AS tx
  WHERE tx.token_address = tok.address
    AND CAST(tx.block_timestamp AS DATETIME) > DATETIME_SUB(CURRENT_DATETIME(), INTERVAL 1 DAY)
  GROUP BY tok.address
  HAVING n >= 3
),
itx AS
(
  SELECT transaction_hash AS id, from_address AS address, token_address AS token, value, lab.label
  FROM `crypto-etl-ethereum-dev.crypto_ethereum.token_transfers` AS tx,
       `crypto-etl-ethereum-dev.dataflow_sql.all_labels` AS lab
  WHERE tx.from_address = lab.address
    AND CAST(tx.block_timestamp AS DATETIME) > DATETIME_SUB(CURRENT_DATETIME(), INTERVAL 1 DAY)
),
otx AS
(
  SELECT transaction_hash AS id, to_address AS address, token_address AS token, value, lab.label
  FROM `crypto-etl-ethereum-dev.crypto_ethereum.token_transfers` AS tx,
       `crypto-etl-ethereum-dev.dataflow_sql.all_labels` AS lab
  WHERE tx.to_address = lab.address
    AND CAST(tx.block_timestamp AS DATETIME) > DATETIME_SUB(CURRENT_DATETIME(), INTERVAL 1 DAY)
)

SELECT
  (CAST(itx.value AS NUMERIC)/POWER(10,CAST(tok.decimals AS NUMERIC)) - z.mu) / z.sigma AS z_score,
  tok.symbol,
  itx.address AS i_address,
  otx.address AS o_address,
  itx.label AS i_label,
  otx.label AS o_label,
  CAST(itx.value AS NUMERIC)/POWER(10,CAST(tok.decimals AS NUMERIC)) AS value
FROM z, itx, otx, `crypto-etl-ethereum-dev.crypto_ethereum.tokens` AS tok
WHERE TRUE
  AND z.sigma > 0
  AND itx.id = otx.id
  AND itx.token = tok.address
  AND z.address = tok.address
  AND otx.label NOT LIKE '%_hw' -- TODO factor this out to also be used in z-score calculations
ORDER BY z_score DESC