Look at running median 30 days back from current date

Question

Background: I have a table like the below where I'm trying to get the median value based on a rolling 30 day lookback from each created_date partitioned by each city.

The problem is that I have missing dates for some cities so bound by rows preceding won't work in this situation.

Note: I do have a date-spine table that I can leverage, but not sure if it's helpful here

CREATED_DATE	CITY	VALUE
2018-08-30	Charlotte	374900
2018-08-31	Charlotte	272000
2018-09-10	Charlotte	1
2018-09-24	Charlotte	365000
2018-10-04	Charlotte	342000
2018-10-07	Charlotte	460000
2018-10-08	Charlotte	91000
2018-10-15	Charlotte	342000
2018-10-18	Charlotte	155000
2018-10-19	Charlotte	222000
...	...	...

Expected Output:

CREATED_DATE	CITY	VALUE	MOVING_MEDIAN_30_DAY
2018-08-30	Charlotte	374900	374900
2018-08-31	Charlotte	272000	323450
2018-09-10	Charlotte	1	272000
2018-09-24	Charlotte	365000	318500
2018-10-04	Charlotte	342000	342000
2018-10-07	Charlotte	460000	353500
2018-10-08	Charlotte	91000	342000
2018-10-15	Charlotte	342000	342000
2018-10-18	Charlotte	155000	342000
2018-10-19	Charlotte	222000	342000
...	...	...	...

Question: How can I get to my expected output using sql / snowflake?

Thanks in advance!!

Answer 1

Since Snowflake's MEDIAN window function doesn't support sliding frames, we must take another approach. We can use Snowflake's MEDIAN aggregate function, along with a self-join, to simulate a window function.

 SELECT original.created_date
      , original.city
      , original.value
      , MEDIAN(window.value) AS rolling_30_day_median
   FROM cities AS original
   LEFT JOIN cities AS window
     ON original.city = window.city
    AND DATEDIFF(days, original.created_date, window.created_date) BETWEEN -30 AND 0
  GROUP BY 1, 2, 3
  ORDER BY 1
;

This produces the desired output.

CREATED_DATE	CITY	VALUE	ROLLING_30_DAY_MEDIAN
2018-08-30	Charlotte	374,900	374,900
2018-08-31	Charlotte	272,000	323,450
2018-09-10	Charlotte	1	272,000
2018-09-24	Charlotte	365,000	318,500
2018-10-04	Charlotte	342,000	342,000
2018-10-07	Charlotte	460,000	353,500
2018-10-08	Charlotte	91,000	342,000
2018-10-15	Charlotte	342,000	342,000
2018-10-18	Charlotte	155,000	342,000
2018-10-19	Charlotte	222,000	342,000