Calculating daily growth using previous row value in new column - SQL Server

I have the following query below that shows size of a database and when this data was collected. I am trying to create a new column that has "average daily growth" which would be determined by the size on the previous day.

If my query captures data from multiple databases/instances how can I do this for each database? As seen in here

I am trying to calculate daily growth per database using the "DB Size mb" column and would like a new column called "LastDayGrowth"

This is my query:

SELECT DISTINCT
        c.instance_name ,
        a.database_name ,
        CONVERT (DATE, SWITCHOFFSET(CAST (a.collection_time AS DATETIMEOFFSET),
                                    '+00:00')) AS 'Collection Time' ,
        'LDF mb' = ( CONVERT(DEC(15, 2), a.logsize) * 8192 / 1048576.0 ) ,
        'MDF mb' = ( CONVERT(DEC(15, 2), a.dbsize) * 8192 / 1048576.0 ) ,
        ( ( CONVERT(DEC(15, 2), a.dbsize) + CONVERT(DEC(15, 2), a.logsize) )
          * 8192 / 1048576.0 ) AS 'DB Size mb' ,
        'Reserved mb' = ( a.reservedpages * 8192 / 1048576.0 ) ,
        'Data mb' = CONVERT(DEC(15, 2), a.pages) * 8192 / 1048576.0 ,
        'Index mb' = ( a.usedpages - a.pages ) * 8192 / 1048576.0 ,
        'Unused mb' = ( ( CONVERT(DEC(15, 2), a.reservedpages)
                          - CONVERT(DEC(15, 2), a.usedpages) ) * 8192
                        / 1048576.0 ) ,
        'Unallocated mb' = ( CASE WHEN a.dbsize >= a.reservedpages
                                  THEN ( CONVERT (DEC(15, 2), a.dbsize)
                                         - CONVERT (DEC(15, 2), a.reservedpages) )
                                       * 8192 / 1048576.0
                                  ELSE 0
                             END )
FROM    MDWDB.snapshots.disk_usage a
        INNER JOIN MDWDB.core.snapshots_internal b ON a.snapshot_id = b.snapshot_id
        INNER JOIN MDWDB.core.source_info_internal c ON b.source_id = c.source_id
ORDER BY c.instance_name ,
        a.database_name ASC;

I have tried th

I just read this article adressing this topic and I suppose you might be interested, because it uses the lag function to solve exactly your Task:

https://www.mssqltips.com/sqlservertip/3690/identify-sql-server-database-growth-rates/

The Author created the following Script using the LAG Window function:

SELECT DISTINCT
    A.[database_name]
,   AVG( A.[Backup Size (MB)] - A.[Previous Backup Size (MB)] ) OVER ( PARTITION BY A.[database_name] ) AS [Avg Size Diff From Previous (MB)]
,   MAX( A.[Backup Size (MB)] - A.[Previous Backup Size (MB)] ) OVER ( PARTITION BY A.[database_name] ) AS [Max Size Diff From Previous (MB)]
,   MIN( A.[Backup Size (MB)] - A.[Previous Backup Size (MB)] ) OVER ( PARTITION BY A.[database_name] ) AS [Min Size Diff From Previous (MB)]
,   A.[Sample Size]
FROM 
(
    SELECT
        s.[database_name]
    --, s.[backup_start_date]
    ,   COUNT(*) OVER ( PARTITION BY s.[database_name] ) AS [Sample Size]
    ,   CAST ( ( s.[backup_size] / 1024 / 1024 ) AS INT ) AS [Backup Size (MB)]
    ,   CAST ( ( LAG(s.[backup_size] ) 
            OVER ( PARTITION BY s.[database_name] ORDER BY s.[backup_start_date] ) / 1024 / 1024 ) AS INT ) AS [Previous Backup Size (MB)]
    FROM 
        [msdb]..[backupset] s
    WHERE
        s.[type] = 'D' --full backup
    --ORDER BY
    --  s.[database_name]
    --, s.[backup_start_date]
) AS A
ORDER BY
    [Avg Size Diff From Previous (MB)] DESC;
GO

StackExchange Database Administrators Q#112303, answer score: 2