Return data where the running total of amounts 30 days from another row is greater than or equal to the amount for that row

Question

Let's say I a table that contains the date, account, product, purchase type, and amount like below:

Looking at this table, you can see that for any particular account/product combination, there are buys and sells. Essentially, what I'd like to write is a SQL query that flags the following: Are there accounts that bought at a certain amount and then sold the same aggregate amount or more 30 days from that buy?

So for example, we can see account 1 bought product A for 20k on 8/1. If we look at the running sum of sells by account 1 for product A over the next 30 days, we see they sold a total of 20k - the same as the initial buy:

Ideally, the query would return results that flag all of these instances: for each individual buy, find all sells for that product/account 30 days from that buy, and only return rows where the running total of sells is greater than or equal to that initial buy.

EDIT: Using the sample data provided, the desired should look more or less look like the following:

You'll see that the buy on 8/2 for product B/account 2 is not returned because the running sum of sells for that product/account/buy combination over the next 30 days does not equal or exceed the buy amount of 35k but it does return rows for the buy on 8/3 for product B/ account 2 because the sells do exceed the buy amount of 10k.

I know I need to self join the sells against the buys, where the accounts/products equal and the datediff is less than or equal 30 and I basically have that part structured. What I can't seem to get working is the running total part and only returning data when that total is greater than or equal to that buy. I know I likely need to use the over/partition by clauses for the running sum but I'm struggling to produce the right results/optimize properly. Any help on this would be greatly appreciated - just looking for some general direction on how to approach this.

Bonus: Would be even more powerful to stop returning the sells once the running total passes the buy, so for example, the last two rows in the desired output I provided aren't technically needed - since the first two sells following the buy had already eclipsed the buy amount.

Answer 1

In SQL Server, one option uses a lateral join:

select 
    t.*, 
    case when t.amount = x.amount then 1 else 0 end as is_returned
from mytable t
cross apply (
    select sum(amount) amount
    from mytable t1
    where 
        t1.purchase_type = 'Sell'
        and t1.account = t.account 
        and t1.product = t.product
        and t1.date >= t.date
        and t1.date <= dateadd(day, 30, t.date)
) x
where t.purchase_type = 'Buy'

The lateral join sums the amount of "sells" of the same account and product within the following 30 days, which you can then compare with the amount of the buy. The query gives you one row per buy, with a boolean flag that indicates if the amounts match.

In databases that support the range specification to window functions, this would be more efficiently expressed with a window sum:

select *
from (
    select 
        t.*,
        case when amount = sum(case when purchase_type = 'Sell' then amount end) over(
            partition by account, product
            order by date
            range between current row and interval '30' day following
        ) then 1 else 0 end as is_returned
    from mytable t
) t
where purchase_type = 'Buy'

---

Edit: this would generate a resultset similar to the third table in your question:

select t.*, x.*
from mytable t
cross apply (
    select 
        t1.date sale_date, 
        t1.amount sell_amount, 
        sum(t1.amount) over(order by t1.date) running_sell_amount,
        sum(t1.amount) over() total_sell_amount
    from mytable t1
    where 
        t1.purchase_type = 'Sell'
        and t1.account = t.account 
        and t1.product = t.product
        and t1.date >= t.date
        and t1.date <=  dateadd(day, 30, t.date)
) x
where t.purchase_type = 'Buy' and t.amount = x.total_sell_amount