For each bank I need to find all dates with higher asset compared to its previous dates (quarter). (For instance a bank (id: 123) has asset 10,000 in 3/31/02 and asset 20,000 in 6/30/02. Then bank id (123), asset value (20,000) and date (6/30/02) should be recorded.) Report the first 10 observation of output table. How can I do this within one query?
Advertisement
Answer
You would use lag()
:
x
select t.*
from (select t.*,
lag(asset) over (partition by id order by date) as prev_asset
from t
) t
where asset > prev_asset ;
Note: This assume that all banks have exactly one row for each quarter of interest — no duplicate rows for a bank and no skipped quarters.