1

This is my ethereum_transaction_receipts table in redshift:

CREATE TABLE "crypto_blockchains"."ethereum_transaction_receipts" (
    "hash" character varying(256),
    "block_number" bigint,
    "tx_time" timestamp without time zone,
    "event_generator_address" character varying(256),
    "event_hash" character varying(256),
    "event_decoded" character varying(256),
    "event_param_1" character varying(256),
    "event_param_2" character varying(256),
    "data" text,
    "token_from" character varying(256),
    "token_to" character varying(256),
    "token_amount" numeric(38,5)
    )

tx_time is the transaction time, token_from is from address, token_to is to address and token_amount is the transaction amount. I want the balance snapshot for each user for each day.

For example: If there is one transaction from A -> B on 2017-01-01 and another on A -> B on 2018-04-09, I want entries for each date between min and max date for the balances of A and B.

I have tried this query to get sum of token amount for token from user, same way I can calculate the sum of token amount for token to user and join them and subtract to get output, but this query gives me one row per transaction and also I don't get details for the user who have stopped making transactions after a certain date. 

select 
    date(tx_time) as date,
    token_from as addr,
    sum(nullif(token_amount,null)) over (partition by event_generator_address, token_from order by tx_time rows unbounded preceding) as amt 
    from crypto_blockchains.ethereum_transaction_receipts
    where event_decoded = 'Transfer'

Is there a better way to calculate balances per day using window functions.

Edit 1: Sample Data:

token to,token from,token amount,tx_time
A,B,0.4,1/1/17
B,A,0.4,1/3/17

Desired Result:

user,balance,date
A,0,1/1/17
A,0.4,1/2/17
A,0,1/3/17
B,0,1/1/17
B,-0.4,1/2/17
B,0,1/3/17
rajat
  • 3,415
  • 15
  • 56
  • 90
  • 1
    Sample data and desired results would really help. – Gordon Linoff Apr 09 '18 at 16:09
  • Sample data and result in edit – rajat Apr 09 '18 at 16:21
  • For what I understood of your data and desired result this has nothing to do with Windowing functions. You need a time/date table with every date to join with your data, and also, it is not clear why the last A and B result in desired output to be 0 for balance. – Jorge Campos Apr 09 '18 at 16:27
  • @JorgeCampos But I need the date dimension table to join with the user as well, How would I do that? Assuming there is a date table, can you suggest a query what the query would be to get to this result? – rajat Apr 09 '18 at 16:38
  • After using something as: https://stackoverflow.com/questions/2157282/generate-days-from-date-range to generate the date range you just need to left join it with your data. – Jorge Campos Apr 09 '18 at 16:50
  • if I left join it with the current table I would get some rows which will only dates and not any other data, How would I arrive at daily balances of each user from that table? – rajat Apr 09 '18 at 16:54

0 Answers0