1

I have a table in Snowflake with data that updates at the end of the month as well as data that updates every day (t2). I've joined these tables and now I need to lag the data points that update monthly until the monthly filingDate is equal to AsOfDate.

This is what my table looks like:

AsOfDate DailyValue ID FilingDate MonthlyValue
7/05/23 158 45 null null
7/04/23 157 45 null null
7/03/23 157 45 null null
7/02/23 154 45 null null
7/01/23 152 45 null null
6/30/23 152 45 6/30/23 4
7/05/23 34 67 null null
7/04/23 33 67 null null
7/03/23 32 67 null null
7/02/23 28 67 null null
7/01/23 28 67 null null
6/30/23 23 67 6/30/23 82

This is what I need my table to look like:

AsOfDate DailyValue ID FilingDate MonthlyValue
7/05/23 158 45 6/30/23 4
7/04/23 157 45 6/30/23 4
7/03/23 157 45 6/30/23 4
7/02/23 154 45 6/30/23 4
7/01/23 152 45 6/30/23 4
6/30/23 152 45 6/30/23 4
7/05/23 34 67 6/30/23 82
7/04/23 33 67 6/30/23 82
7/03/23 32 67 6/30/23 82
7/02/23 28 67 6/30/23 82
7/01/23 28 67 6/30/23 82
6/30/23 23 67 6/30/23 82
6/29/23 22 67 6/27/23 80
6/28/23 21 67 6/27/23 80
6/27/23 20 67 6/27/23 80
6/26/23 19 67 5/31/23 77

I tried using row_number to find the latest filing date for each id.

select row_number over (partition by id order by filingDate desc) as rn

so rn = 1 is the latest filingDate for each date and then I tried using an update statement.

update table 
set MonthlyValue = (select b.MonthlyValue
                    from table b
                    where rn = 1 and b.id = a.id and b.MonthlyValue is not null)
from table a
where a.MonthlyValaue is null

This did not work in Snowflake. I got an error message of unsupported subquery type. I honestly cannot think of a way of doing this without a subquery, however. Any help would be greatly appreciated!!!

naomitrina
  • 31
  • 3

1 Answers1

0

This type of question comes up so often that I wrote a blog post about it: Create missing records with Snowflake SQL.

Look at the syntax for using the LAST_VALUE() function.

Maja F.
  • 353
  • 2
  • 9
  • I've tried multiple variations of queries in Snowflake using last_value(), and none of them have worked. I still just see nulls where ever filingDate is null. `SELECT AsOfDate, DailyValue, ID, LAST_VALUE(FilingDate IGNORE NULLS) OVER (PARTITION BY ID ORDER BY AsOfDate ) AS FilingDate, LAST_VALUE(MonthlyValue IGNORE NULLS) OVER (PARTITION BY ID ORDER BY FilingDate desc ) AS MonthlyValue FROM table` – naomitrina Jul 27 '23 at 02:02
  • Your query works perfectly fine on the sample data you provided. Do you have any other examples where it is not working? – Maja F. Jul 28 '23 at 07:23
  • For me, the query simply returrns the same FilingDate for each AsOfDate. However, I only want backfill wherever FilingDate is less than AsOfDate. I tried using a case when FilingDate < AsOfDate then last_value(MonthlyValue IGNORE NULLS) over (Partition by ID, Order by FilingDate desc) else MonthlyValue in the querry, but that didn't produce what I want either. – naomitrina Jul 28 '23 at 14:19
  • I added a few more rows to my desired output table to illustrate this. I only need last_value(filingDate) and last_value(MonthlyValue) where FilingDate < AsOfDate. – naomitrina Jul 28 '23 at 14:24
  • Try this: `SELECT AsOfDate, DailyValue, ID, LAST_VALUE(FilingDate IGNORE NULLS) OVER (PARTITION BY ID ORDER BY AsOfDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS FilingDate, LAST_VALUE(MonthlyValue IGNORE NULLS) OVER (PARTITION BY ID ORDER BY AsOfDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS MonthlyValue FROM table` – Maja F. Jul 29 '23 at 13:01