Big query sql Using lag function with missing days

Question

I am using sql in Big Query superset to create a pivot table to calculate the difference of a game between a range of days:

Game    Date        Sum     Prev_sum
First   2022-08-15  178.68  
        2022-08-16  345.09  166.41
        2022-08-18  570.53  991.89   --wrong input ,missing day
        2022-08-19  173.69  -396.84
        2022-08-20  532.35  358.66
        2022-08-21  248.35  -284
Second  2022-08-17  518.59  600.53   --wrong input ,missing day
        2022-08-20  182.91  148.3
Third   2022-08-15  527.35  
        2022-08-17  359.93  426.53
        2022-08-20  539.05  564.57
        2022-08-21  543.74  4.69

i m using

 select Date , SUM(Sum) ,
           SUM(Sum) - 
           lag(SUM(Sum)) over(partition by Game order by min(Date) ASC)
   from my_table

ideally i would like to have the difference of sum between each row regardless of the date shown

Game    Date        Sum     Prev_sum
First   2022-08-15  178.68  
        2022-08-16  345.09  166.41
        2022-08-18  570.53  225   
        2022-08-19  173.69  -396.84
        2022-08-20  532.35  358.66
        2022-08-21  248.35  -284
Second  2022-08-17  518.59  
        2022-08-20  182.91  -336

"difference of sum between each row" - what does that mean? Why sum on a single row. Try `select Game, [Date] ,[Sum], [Sum] - lag([Sum]) over(partition by Game order by [Date]) from my_table` — CHill60, Aug 22 '22 at 13:05
sorry for the missuunderstanding , to re phrase it i would like to get the difference of current row from the previous row ,regardless of the date. "SUM" is just a metric name. Using lag , i get false results when the dates are not consecutive. All i want is to calculate the difference of the previous row regardless the date — George Papadopoulos, Aug 22 '22 at 13:08

score 1 · Accepted Answer · answered Aug 22 '22 at 13:28

Consider below query:

SELECT Game, Date,
       Sum - LAG(Sum) OVER(PARTITION BY Game ORDER BY Date) Prev_sum
  FROM my_table
 ORDER BY 1, 2;

Query results:

Game	Date	Prev_sum
First	2022-08-15
First	2022-08-16	166.40999999999997
First	2022-08-18	225.44
First	2022-08-19	-396.84
First	2022-08-20	358.66
First	2022-08-21	-284.0
Second	2022-08-17
Second	2022-08-20	-335.68000000000006
Third	2022-08-15
Third	2022-08-17	-167.42000000000002
Third	2022-08-20	179.11999999999995
Third	2022-08-21	4.6900000000000546

Sample Table

create temp table my_table AS
select 'First' Game,   '2022-08-15' Date,  178.68  Sum union all
select 'First',   '2022-08-16',  345.09 union all
select 'First',   '2022-08-18',  570.53 union all
select 'First',   '2022-08-19',  173.69 union all
select 'First',   '2022-08-20',  532.35 union all
select 'First',   '2022-08-21',  248.35 union all
select 'Second',  '2022-08-17',  518.59 union all
select 'Second',  '2022-08-20',  182.91 union all
select 'Third',   '2022-08-15',  527.35 union all
select 'Third',   '2022-08-17',  359.93 union all
select 'Third',   '2022-08-20',  539.05 union all
select 'Third',   '2022-08-21',  543.74 ;

Thank you very much this worked for me. Can you please explain how that ORDER BY 1, 2 line works? — George Papadopoulos, Aug 23 '22 at 08:55
**ORDER BY 1, 2** is same as **ORDER BY Game, Date** which is column ordinal positions in **SELECT list**. and it's for displaying the results same as your expected output only and doesn't affect the result. — Jaytiger, Aug 23 '22 at 09:30

Big query sql Using lag function with missing days

1 Answers1

Query results:

Sample Table