Skip to content

Instantly share code, notes, and snippets.

@ana0

ana0/gist:1eda8d9e44a3eae5ffa704897bf4c27e

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.
Download ZIP

Revisions

  1. ana0 created this gist Apr 25, 2019.
    28 changes: 28 additions & 0 deletions gistfile1.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,28 @@
    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
    );