Identify which users have positive balance every day in SQL

user_8248194 0
Viewed 57

I have user transaction data in a bank from several users with the following schema:

CREATE TABLE if not EXISTS transactions (
  id int,
  user_id int,
  created_at DATE,
  amount float
);

INSERT INTO transactions VALUES
(1, 1, '2020-01-01', 100),
(2, 1, '2020-01-02', -50),
(3, 1, '2020-01-04', -50),
(4, 2, '2020-01-04', 80),
(5, 3, '2020-01-06', 10),
(6, 3, '2020-01-10', -10);

I want to know, for each day from the beginning of the transactions to the current date, which users have a positive balance on their accounts.

In this case, the output of the query would be:

date,user_id
'2020-01-01',1
'2020-01-02',1
'2020-01-03',1
'2020-01-04',1
'2020-01-04',2
'2020-01-05',2
'2020-01-06',2
'2020-01-07',2
...
'2021-05-17',2 -- Today's date, user 2 still has positive balance
'2020-01-06',3
'2020-01-07',3
'2020-01-08',3
'2020-01-09',3
'2020-01-10',3

Is there an easy way to do this using PostgreSQL? Or even better, in BigQuery?

google-bigquery postgresql sql
1 Answers

Try this for BigQuery:

with transactions as (
  select 1 as user_id, date '2020-01-01' as date, 100 as amount union all
  select 1, '2020-01-02', -50 union all
  select 1, '2020-01-04', -50 union all
  select 2, '2020-01-04', 80 union all
  select 3, '2020-01-06', 10 union all
  select 3, '2020-01-10', -10
),
all_users as (
  select min(date) as min_date, user_id
  from transactions
  group by user_id
),
all_days as (
  select *
  from all_users, unnest(generate_date_array('2020-01-01', current_date())) as date
  where date >= min_date
)
select date, user_id
from all_days left join transactions using (user_id, date)
where true
qualify sum(amount) over (partition by user_id order by date) > 0

Without qualify:

with transactions as (
  select 1 as user_id, date '2020-01-01' as date, 100 as amount union all
  select 1, '2020-01-02', -50 union all
  select 1, '2020-01-04', -50 union all
  select 2, '2020-01-04', 80 union all
  select 3, '2020-01-06', 10 union all
  select 3, '2020-01-10', -10
),
all_users as (
  select min(date) as min_date, user_id
  from transactions
  group by user_id
),
all_days as (
  select *
  from all_users, unnest(generate_date_array('2020-01-01', current_date())) as date
  where date >= min_date
)
select date, user_id
from (
  select date, user_id, sum(amount) over (partition by user_id order by date) as balance
  from all_days left join transactions using (user_id, date)
)
where balance > 0
user_134735250
Related