gapfilling currency

code.sql

-- Transformation code for Snowflake backend, assumes that the input table is named "cur"
-- Fills gaps in currency rates by the last known value

DROP TABLE IF EXISTS "curdates";
DROP TABLE IF EXISTS "outCur";

CREATE TABLE "curdates" AS (
	SELECT "date",
	    TO_CHAR("date", 'YYYY-MM-DD') ||  (SELECT DISTINCT "fromCurrency" FROM "cur" LIMIT 1) || "currency" AS "id",
	    "currency"
	FROM 
		(
		    SELECT DATEADD(day, ROW_NUMBER() OVER (ORDER BY seq8()), '1999-01-03'::DATE) AS "date"
		    FROM 
		      	TABLE(GENERATOR(ROWCOUNT=>10000))	     
	    ) AS "dates", 
	    (
	    	SELECT DISTINCT "toCurrency" AS "currency" 
	    	FROM "cur"
	    ) AS "currencies"
	    WHERE "date" < CURRENT_TIMESTAMP
	ORDER BY 1
);

CREATE TABLE "outCur" AS (
	SELECT 
		"cid" AS "id",
		"allDate" AS "date",
		(SELECT "fromCurrency" FROM "cur" LIMIT 1) AS "fromCurrency",
		COALESCE("toCurrency", FIRST_VALUE("leadCurrency") OVER (PARTITION BY "grp", "allCurrency" ORDER BY "allDate", "allCurrency")) AS "toCurrency",
		COALESCE("rate", FIRST_VALUE("leadRate") OVER (PARTITION BY "grp", "allCurrency" ORDER BY "allDate", "allCurrency")) AS "rate",
		(SELECT "source" FROM "cur" LIMIT 1) AS "source"
	FROM (
		SELECT 
			SUM("jump") OVER (ORDER BY "allDate") AS "grp", 
			* 
	  	FROM (  
	    	SELECT 
	    		CASE WHEN "leadRate" IS NOT NULL AND "rate" IS NULL OR
	      			"leadRate" IS NULL AND "rate" IS NOT NULL THEN 1 ELSE 0 END AS "jump",
	      		*
	    	FROM (
	    		SELECT 
	          		LEAD("cur"."rate", -1) OVER ( PARTITION BY "curdates"."currency" ORDER BY "curdates"."date", "curdates"."currency") AS "leadRate",
			        LEAD("cur"."toCurrency", -1) OVER ( PARTITION BY "curdates"."currency" ORDER BY "curdates"."date", "curdates"."currency") AS "leadCurrency",
	        		"curdates"."id" AS "cid",
	        		"curdates"."date" AS "allDate",
	        		"curdates"."currency" AS "allCurrency",
	        		"cur".* 
	        	FROM "curdates" LEFT JOIN "cur"
	          		ON "curdates"."id" = "cur"."id"
	    	)     
	  	)
	)
	ORDER BY "toCurrency", "date"
);