Search code examples
sqldateselectsumwindow-functions

SQL get the first date the condition exists

I have a collections table: subscription_id, transaction_date_est, start_date_est, end_date_est, transaction_id, invoice_number, user_id, transaction_amount, plan_code, transaction_type

I have users who purchased a subscription, for each subscription I generate a subscription ID. Each subscription has between 1 to many invoices. For each subscription I try to find when the user passed the accumulated amount of $100 with using a window function. My Expected results: subscription_id, transaction_date

I tried:

SELECT subscription_id, MAX(date) transaction_date
FROM (SELECT subscription_id, 
SUM(usd_price) LAG (partition by subscription_id
ORDER BY first_transaction_date ASC) AS total
GROUP BY(current_period_started_at)
HAVING BY total >= 100
ORDER BY usd_price)

but I don't succeed to extract the first date the user passed 100$.

Solution

  • I think you want:

    select c.*
from (
    select c.*, sum(usd_price) over((partition by subscription_id order by transaction_date) sum_usd_price
    from collections c
) c
where sum_usd_price >= 100 and sum_usd_price - usd_price < 100