SQL Query calculating two additional columns

I have a table which gets populated daily with database size. I need to modify the query where I can calculate daily growth and weekly growth.

select * from sys.dbsize 
where SNAP_TIME > sysdate -3
order by SNAP_TIME

Current Output

I would like to add two additional columns which would be Daily Growth (DB_SIZE sysdate – DB_SIZE (sysdate -1)) Weekly Growth (DB_SIZE sysdate – DB_SIZE (sysdate -7))

Need some help constructing the SQL for those two additional columns. Any help will be greatly appreciated.

Thanks,

Answer

One option is to use LAG analytic function to calculate daily growth and correlated subquery (within the SELECT statement) for weekly growth.

For example:

SQL> with dbsize (snap_time, db_size) as
  2    (select sysdate - 8, 100 from dual union all
  3     select sysdate - 7, 110 from dual union all
  4     select sysdate - 6, 105 from dual union all
  5     select sysdate - 5, 120 from dual union all
  6     select sysdate - 4, 130 from dual union all
  7     select sysdate - 3, 130 from dual union all
  8     select sysdate - 2, 142 from dual union all
  9     select sysdate - 1, 144 from dual union all
 10     select sysdate - 0, 150 from dual
 11    )
 12  select
 13    a.snap_time,
 14    a.db_size,
 15    a.db_size - lag(a.db_size) over (order by a.snap_time) daily_growth,
 16    --
 17    db_size - (select db_size from dbsize b
 18               where trunc(b.snap_time) = trunc(a.snap_time) - 7
 19              ) weekly_growth
 20  from dbsize a
 21  order by a.snap_time;

SNAP_TIME              DB_SIZE DAILY_GROWTH WEEKLY_GROWTH
------------------- ---------- ------------ -------------
24.08.2020 21:52:20        100
25.08.2020 21:52:20        110           10
26.08.2020 21:52:20        105           -5
27.08.2020 21:52:20        120           15
28.08.2020 21:52:20        130           10
29.08.2020 21:52:20        130            0
30.08.2020 21:52:20        142           12
31.08.2020 21:52:20        144            2            44
01.09.2020 21:52:20        150            6            40

9 rows selected.

SQL>