Finding banks with higher assets in previous quarter SQL?

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?

Answer

You would use lag():

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.