SQL to find when amount reached a certain value for the first time-CodePudding

I have a table that has 3 columns: user_id, date, amount. I need to find out on which date the amount reached 1 Million for the first time. The amount can go up or down on any given day.

I tried using partition by user_id order by date desc but I can't figure out how to find the exact date on which it reached 1 Million for the first time. I am exploring lead, lag functions. Any pointers would be appreciated.

CodePudding user response：

Try this:

select date,
    sum(amount) as totalamount
from tablename
group by date
having totalamount>=1000000
order by date asc
limit 1

This would summarize the amount for each day and return 1 record where it reached 1M for the first time.

Sample result on SQL Fiddle.

And if you want it to be grouped for both date and user_id, add user_id in select and group by clauses.

select user_id, date,
    sum(amount) as totalamount
from tablename
group by user_id,date
having totalamount>=1000000
order by date asc
limit 1

Example here.

CodePudding user response：

You may use conditional aggregation as the following:

select user_id,
  min(case when amount >= 1000000 then date end) as expected_date
from table_name
group by user_id

And if you want to check where the amount reaches exactly 1M, use case when amount = 1000000 ...

If you meant that the amount is a cumulative amount over the increasing of date, then query will be:

select user_id,
  min(case when cumulative_amount >= 1000000 then date end) as expected_date
from 
(
  select *,
    sum(amount) over (partition by user_id order by date) cumulative_amount
  from table_name
) T
group by user_id;