CODEWITHSUNDEEP
amazon-athenapresto
Artem
Artem

Reputation: 833

Summing a sequence of amounts iteratively grouped by ID and by date in Presto

I'm trying to run a query in Amazon Athena where I sum the sequence of transactions per ID up and to a specific date. The specified date yield the balance at that point in time. For each ID I need to include the very first transaction in order to get the correct balance. The data is structured as

ID   |  Amount  | Created
________________________

378218  -450.00 2018-09-27
378218  4500.00 2018-09-27
260709  899.00  2018-09-27
70791   -719.96 2018-09-27
70791   -65.00  2018-09-19
70791   -3.16   2018-09-07
70791   3.16    2018-09-07
70791   61.84   2018-09-01
70791   -61.84  2018-09-01
70791   -65.00  2018-08-16
70791   -61.84  2018-08-01

It's easy enough to get the balance of all ID for one specific date: 

SELECT SUM(amount), ID, created 
FROM db.table where created<'2018-10-01'
GROUP BY ID

This yields the following result:

 ID   |  Amount  | Created
 ________________________

70791   344.10  2015-09-27
260709  899.00  2018-09-27
378218  0.00    2018-09-27

The above query gives me the balances for the 30th of September. However, how is it achieved for multiple points in times in one query, e.g., for the final day of the month of the past 12 months? I know you can use temporary tables etc., but I'm not really familiar with this.

Thanks in advance.

Upvotes: 2

Views: 932

Answers (1)

Tanveer Uddin
Tanveer Uddin

Reputation: 1525

you will need to create a window function as below.

SELECT id, created,sum(amount) over (partition by id order by created)         
balance_at_date  
FROM "db"."table" 
order by id, created;

I had data like below

ID,AMOUNT,CREATED
123,100,2018-09-01
123,200,2018-09-15
123,-50,2018-09-18
345,100,2017-01-01
345,50,2018-01-01
345,-200,2018-09-01

and the query returns output as below:

id  created balance_at_date
123 2018-09-01  100
123 2018-09-15  300
123 2018-09-18  250
345 2017-01-01  100
345 2018-01-01  150
345 2018-09-01  -50

Hope that helps.

Upvotes: 1

Related Questions