I want to find records in date range 1/1/19-1/7/19 which increase amount
using table HISTORY:
DATE AMOUNT ID
(Date, number, varchar2(30))
I find IDs inside range correctly
assuming increase/decrease can happens only when having two records with same Id
with suspect as
(select id
from history
where t.createddate < to_date('2019-07-01', 'yyyy-mm-dd')
group by id
having count(1) > 1),
ids as
(select id
from history
join suspect
on history.id = suspect.id
where history.date > to_date('2019-01-01', 'yyyy-mm-dd')
and history.date < to_date('2019-07-01', 'yyyy-mm-dd'))
select count(distinct id)
from history a, history b
where a.id = b.id
and a.date < b.date
and a.amount < b.amount
The problem to find increase I need to find previous record which can be before time range
I can find last previous time before time range, but I failed to use it:
ids_prevtime as (
select history.*, max(t.date) over (partition by t.id) max_date
from history
join ids on history.userid = ids.id
where history.date < to_date('2019-01-01','yyyy-mm-dd' )
), ids_prev as (
select * from ids_prevtime where createdate=max_date
)
I see that you found solution, but maybe you could do it simpler, using lag():
select count(distinct id)
from (select id, date_, amount,
lag(amount) over (partition by id order by date_) prev_amt
from history)
where date_ between date '2019-01-01' and date '2019-07-01'
and amount > prev_amt;