Skip to content

Instantly share code, notes, and snippets.

@allenday allenday/tx.sql
Last active Jun 7, 2019

Embed
What would you like to do?
recent anomalous token transfers
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.