Compare the dates, compute the difference between them, postgres

Question

I have a date column and a balance column for each user. Every time user makes a transaction, a new row gets added to this table. It could be that the user makes 15 transactions during the day, and no transaction at all during 5 days.

Like this one

  date                balance
2017-06-01              95.63
2017-06-01              97.13
2017-06-01              72.14
2017-06-06              45.04
2017-06-08              20.04
2017-06-09              10.63
2017-06-09              -29.37
2017-06-09              -51.35
2017-06-13              -107.55
2017-06-13              -101.35
2017-06-15              -157.55
2017-06-16              -159.55
2017-06-17              -161.55

The goal is to select the positive and negative transactions made during the same day, compute their average or min value and to consider it as one transaction.If the next day no transaction has been made, then the amount of the previous day should be used.

it means for each day in a month i should calculate an interest and it the balance has not been updated then the balance of the previous day should be used. Hypothetically my table should look like

date    balance
1/6/2017    72.14
6/2/2017    72.14
6/3/2017    72.14
6/4/2017    72.14
6/5/2017    72.14
6/6/2017    45.04
7/6/2017    45.04
8/6/2017    20.04
9/6/2017    -51.35
10/6/2017   -51.35
11/6/2017   -51.35
12/6/2017   -51.35
13/06/2017  -107.55
14/06/2017  -107.55
15/06/2017  -157.55
16/06/2017  -159.55
17/06/2017  -161.55

i have added those days that were missing and group the days that were duplicate.

Once I have this done, I can select the number of positive balance days, e.g. 8 days, compute the average positive balance, and multiply it by 0.4%.

8*58.8525*0.004=0.23

The same should be done with negative balance. but with a different interest rate number of negative balance days, e.g. 9 multiplied by average negative balance during those days and 8.49%.

9*-99.90555556*0.00849=-0.848

So my expected result is just to have these two columns

 Neg        Pos
-0.848     0.23

How can I do that it in postgres? The function OVERLAP does not really help since I need to specify the dates.

Besides i do not know how to

loop the days and to see if there is a duplicate.
See which days are missing and use the previous balance for each of these missing days.

Anuraag Veerapaneni · Accepted Answer · 2017-06-21 20:55:35Z

2

please try this.. replace table with your table name

with cte as
(
   Select "date" as date
         ,min(balance) as balance
         ,lead("date")  over(order by "date") next_date
         ,Coalesce(ABS("date" - lead("date")  over(order by "date")),1) date_diff 
from table
   group by "date"
),
cte2 as
(
   Select date_diff*balance as tot_bal , date_diff
   from cte 
   Where balance > 0  
),
cte3 as
(
   Select date_diff*balance as tot_bal , date_diff
   from cte 
   Where balance < 0  
)

Select (sum(cte2.tot_bal) / sum(cte2.date_diff) ) * 0.004 as pos
,(sum(cte3.tot_bal) / sum(cte3.date_diff) ) * 0.00849 as neg
from cte2
     ,cte3;

edited Jun 21, 2017 at 20:55

user330315

answered Jun 21, 2017 at 20:52

Anuraag Veerapaneni

6795 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Compare the dates, compute the difference between them, postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related