CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
JuniorAngelo
JuniorAngelo

Reputation: 57

How to balance history base on date with SQL?

I have a transaction table which contains transaction history of a user. As table indicates, for user id of 1, from Jan 01 to March 01, where are 4 transactions in total. Three are positive one is negative.

Table1

| id | user_id | type | amount | created_at (timestamp) | 
|----|---------|------|--------|------------------------|
|  1 |       1 |    1 |    $25 |             2022-01-01 | 
|  2 |       1 |    1 |    $5  |             2022-01-01 | 
|  3 |       1 |    2 |    $-5 |             2022-02-01 | 
|  4 |       1 |    1 |    $10 |             2022-03-01 |

My desired outcome is I want to create a balance history table that create a Table2 from Table1, which contains rows of summary of balance of each day. For instance if on Jan 04th there is no transaction for this user, then balance will stay $30 until the next transaction, in this specific case,Feb 01st the balance changed from $30 to 30-5 = $25 and keep it $25 till March 01st. it changes from 25 to 25+10 = 35.

Table2

| date       | user_id | balance | 
|------------|---------|---------|
| 2022-01-01 |       1 |      30 | 
| 2022-01-02 |       1 |      30 |
| 2022-01-03 |       1 |      30 | 
| …          |       1 |      30 |
| 2022-02-01 |       1 |      25 | 
| 2022-02-02 |       1 |      25 |
| …          |       1 |      25 | 
| 2022-03-01 |       1 |      35 | 
| 2022-03-02 |       1 |      35 |
| …          |       1 |      35 |

Upvotes: 0

Views: 144

Answers (1)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 25968

with data in table1 like:

with table1(id, user_id, type, amount, created_at) as (
    select * from values
    ( 1, 1, 1, 25, '2022-01-01'::date),
    ( 2, 1, 1, 5 , '2022-01-01'::date),
    ( 3, 1, 2, -5, '2022-02-01'::date),
    ( 4, 1, 1, 10, '2022-03-01'::date)
)

with dates as (
    select user_id, min(created_at) as min_date, max(created_at) as max_date 
    from table1
    group by 1
), date_ranges as (
    select d.user_id,
        dateadd('day', row_number()over(order by null)-1, d.min_date) as day_date
    from dates as d
        ,table(generator(ROWCOUNT => 1000))
    qualify day_date <= d.max_date
)
select date, user_id, sum(day_balance) over(partition by user_id order by date) as balance
from (
    select dr.day_date as date
        ,dr.user_id
        ,sum(zeroifnull(t1.amount)) as day_balance
    from date_ranges as dr
    left join table1 as t1
        on t1.user_id = dr.user_id and dr.day_date = t1.created_at
    group by 1,2
)
order by 1,2;

gives:

DATE USER_ID BALANCE
2022-01-01 1 30
2022-01-02 1 30
2022-01-03 1 30
... 1 30
2022-02-01 1 25
2022-02-02 1 25
... 1 25
2022-03-01 1 35

Upvotes: 1

Related Questions