Pacific-Design.com

    
Home Index

1. Databases

2. Delta

Databases / Delta /

Calculate Delta between rows

SELECT  t1.video_id, 
        t1.dt,
        t1.lifetime,
        t1.lifetime - IFNULL(t2.lifetime,0) AS delta

    FROM 
        total_video_views_organic_unique t1
        LEFT JOIN total_video_views_organic_unique t2
            ON t1.video_id = t2.video_id AND 
                t2.dt = ( 
                    SELECT MAX(dt)
                    FROM total_video_views_organic_unique t3
                    WHERE t3.dt < t1.dt
                )   

ORDER BY t1.dt
+------------------+------------+----------+-------+ | video_id | dt | lifetime | delta | +------------------+------------+----------+-------+ | 1286102781458821 | 2017-04-16 | 3 | 3 | | 1286102781458821 | 2017-04-17 | 5 | 2 | | 1286102781458821 | 2017-04-18 | 11 | 6 | | 1286102781458821 | 2017-04-19 | 29 | 18 | +------------------+------------+----------+-------+