Instantly share code, notes, and snippets.

@odinuv /code.sql
Last active Nov 2, 2016

Embed
What would you like to do?
gapfilling currency
-- 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"
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment