Skip to content

Instantly share code, notes, and snippets.

@ana0
Created April 25, 2019 13:15
Show Gist options
  • Save ana0/1eda8d9e44a3eae5ffa704897bf4c27e to your computer and use it in GitHub Desktop.
Save ana0/1eda8d9e44a3eae5ffa704897bf4c27e to your computer and use it in GitHub Desktop.
CREATE OR REPLACE VIEW accounts.address_token_balances AS (
SELECT
address_hash,
token_contract_address_hash,
block_number,
SUM(amount) OVER (
PARTITION BY address_hash, token_contract_address_hash
ORDER BY block_number ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS amount
FROM (
SELECT
xfer.src AS address_hash,
xfer.contract AS token_contract_address_hash,
xfer.block_number AS block_number,
-1 * xfer.amount AS amount
FROM accounts.token_value_transfers AS xfer
UNION ALL
SELECT
xfer.dst AS address_hash,
xfer.contract AS token_contract_address_hash,
xfer.block_number AS block_number,
xfer.amount AS amount
FROM accounts.token_value_transfers AS xfer
) as amount
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment