sql - Does Oracle allow to do a sum over a partition but only when it obeys certain conditions, otherwise use a lag?

Question

So my company has an application that has a certain "in-app currency". We record every transaction. Recently, we found out there was a bug running for a couple of weeks that allowed users to spend currency in a certain place, even when they had none. When this happened, users wouldn't get charged at all: e.g. User had 4 m.u. and bought something that's worth 10 m.u. it's balance would remain at 4.

Now we need to find out who abused it and what's their available balance.

I want to get the column BUG_ABUSE and WISHFUL_CUMMULATIVE that reflect the illegitimate transactions and the amount that our users really see in their in-app wallets but I'm running out of ideas of how to get there.

I was wondering if I could do something like a sum(estrelas) if result over 0 else lag over (partition by user order by date) or something of the likes to get the wishful cummulative.

We're using oracle. Any help is highly appreciated

User_ID	EVENT_DATE	AMOUNT	DIRECTION	RK	CUM	WISHFUL_CUMMULATIVE	BUG_ABUSE
1	02/01/2021 13:37:19,009000	-5	0	1	-5	0	1
1	08/01/2021 01:55:40,000000	40	1	2	35	40	0
1	10/01/2021 10:45:41,000000	2	1	3	37	42	0
1	10/01/2021 10:45:58,000000	2	1	4	39	44	0
1	10/01/2021 13:47:37,456000	-5	0	5	34	39	0
2	13/01/2021 20:09:59,000000	2	1	1	2	2	0
2	16/01/2021 15:14:54,000000	-50	0	2	-48	2	1
2	19/01/2021 02:02:59,730000	-5	0	3	-53	2	1
2	23/01/2021 21:14:40,000000	3	1	4	-50	5	0
2	23/01/2021 21:14:50,000000	-5	0	5	-55	0	0

question from:https://stackoverflow.com/questions/65893790/does-oracle-allow-to-do-a-sum-over-a-partition-but-only-when-it-obeys-certain-co

Answer 1

Here's something you can try. This uses recursive subquery factoring (recursive WITH clause), so it will only work in Oracle 11.2 and higher.

I use columns USER_ID, EVENT_DATE and AMOUNT from your inputs. I assume all three columns are constrained NOT NULL, two events can't have exactly the same timestamp for the same user, and AMOUNT is negative for purchases and other debits (fees, etc.) and positive for deposits or other credits.

The input data looks like this:

select user_id, event_date, amount
from   sample_data
order  by user_id, event_date
;

USER_ID EVENT_DATE                    AMOUNT
------- ----------------------------- ------
      1 02/01/2021 13:37:19,009000000     -5
      1 08/01/2021 01:55:40,000000000     40
      1 10/01/2021 10:45:41,000000000      2
      1 10/01/2021 10:45:58,000000000      2
      1 10/01/2021 13:47:37,456000000     -5
      2 13/01/2021 20:09:59,000000000      2
      2 16/01/2021 15:14:54,000000000    -50
      2 19/01/2021 02:02:59,730000000     -5
      2 23/01/2021 21:14:40,000000000      3
      2 23/01/2021 21:14:50,000000000     -5

Perhaps your input data has additional columns (like cumulative amount, which I left out because it plays no role in the problem or its solution). You show a RK column - I assume you computed it as a step in your attempt to solve the problem; I re-create it in my solution below.

Here is what you can do with a recursive query (recursive WITH clause):

with
  p (user_id, event_date, amount, rk) as (
    select user_id, event_date, amount,
           row_number() over (partition by user_id order by event_date)
    from   sample_data
  )
, r (user_id, event_date, amount, rk, bug_flag, balance) as (
    select  user_id, event_date, amount, rk,
            case when amount < 0 then 'bug' end, greatest(amount, 0)
      from  p
      where rk = 1
    union all
    select  p.user_id, p.event_date, p.amount, p.rk, 
            case when p.amount + r.balance < 0 then 'bug' end,
            r.balance + case when r.balance + p.amount >= 0
                             then p.amount else 0 end
      from  p join r on p.user_id = r.user_id and p.rk = r.rk + 1
  )
select *
from   r
order  by user_id, event_date
;

Output:

USER_ID EVENT_DATE                    AMOUNT RK BUG BALANCE
------- ----------------------------- ------ -- --- -------
      1 02/01/2021 13:37:19,009000000     -5  1 bug       0
      1 08/01/2021 01:55:40,000000000     40  2          40
      1 10/01/2021 10:45:41,000000000      2  3          42
      1 10/01/2021 10:45:58,000000000      2  4          44
      1 10/01/2021 13:47:37,456000000     -5  5          39
      2 13/01/2021 20:09:59,000000000      2  1           2
      2 16/01/2021 15:14:54,000000000    -50  2 bug       2
      2 19/01/2021 02:02:59,730000000     -5  3 bug       2
      2 23/01/2021 21:14:40,000000000      3  4           5
      2 23/01/2021 21:14:50,000000000     -5  5           0