1

I have a large table (in Snowflake) of amounts for multiple entities in multiple currencies across a variety of time periods. However, instances where the amount is 0 are missing, and need to be added- starting from the first date up to the current. A sample of the data looks something like this:

Name Currency Date Amount
A USD 2020-01-01 3
A USD 2020-01-03 4
A USD 2020-01-04 2
A CAD 2021-01-04 5
A CAD 2021-01-06 6
A CAD 2020-01-07 1
B USD 2019-01-01 3
B USD 2019-01-03 4
B USD 2019-01-04 5
B CAD 2017-01-04 3
B CAD 2017-01-06 2
B CAD 2017-01-07 2

The problem with most of the solutions I have seen that work by generating a table of dates and joining is that:

  • they will only fill in dates once, rather than filling in dates for all of the partitions/pairs (A and USD, A and CAD, B and USD, B and CAD)
  • they use a fixed starting date from a pre-generated table, while the earliest date varies depending on which pair of Name and Currency are being considered

Oracle DB supports something like a partitioned outer join, which would get me at least part way there. However Snowflake has no such ability.

I am not too concerned with efficiency.

Jeffrey Leung
  • 13
  • 3

2 Answers2

2

Using Simeon's fake data, an alternative using repeat and split_to_table comes to mind. The idea is to create the exact number of combinations that we need per name and currency, then use a set based logic to create the final output

with lookup (name, currency, min_date, day_count) as

(select name,currency, min(date), datediff(day,min(date),max(date))
from fake_data
group by name, currency)

select name, currency, date, amount --start with what you already have
from fake_data

union all

select name, currency, dateadd('day',b.index-1,min_date), 0  -- add in all the combinations
from lookup, lateral split_to_table(repeat('.',day_count), '.') b

minus

select name, currency, date, 0 --remove the combinations you already have the amounts for
from fake_data
order by name, currency, date
Radagast
  • 5,102
  • 3
  • 12
  • 27
1
WITH fake_data AS (
    SELECT * FROM VALUES
        ('A','USD','2020-01-01'::date,3)
        ,('A','USD','2020-01-03'::date,4)
        ,('A','USD','2020-01-04'::date,2)
        ,('A','CAD','2021-01-04'::date,5)
        ,('A','CAD','2021-01-06'::date,6)
        ,('A','CAD','2020-01-07'::date,1)
        ,('B','USD','2019-01-01'::date,3)
        ,('B','USD','2019-01-03'::date,4)
        ,('B','USD','2019-01-04'::date,5)
        ,('B','CAD','2017-01-04'::date,3)
        ,('B','CAD','2017-01-06'::date,2)
        ,('B','CAD','2017-01-07'::date,2)
    d(Name,Currency,Date,Amount)
), partition_ranges AS (
    SELECT name,
        currency, 
        min(date) as min_date, 
        max(date) as max_date,
        datediff('days', min_date, max_date) as span
    FROM fake_data
    GROUP BY 1,2
), huge_range as (
    SELECT ROW_NUMBER() OVER(order by true)-1 as rn
    FROM table(generator(ROWCOUNT => 10000000))
), in_fill as (
    SELECT pr.name,
        pr.currency,
        dateadd('day', hr.rn, pr.min_date) as date
    FROM partition_ranges as pr
    JOIN huge_range as hr ON pr.span >= hr.rn
)
SELECT 
    i.name, 
    i.currency, 
    i.date,
    nvl(d.amount, 0) as amount
from in_fill as i
left join fake_data as d on d.name = i.name and d.currency = i.currency and d.date = i.date
order by 1,2,3;
NAME CURRENCY DATE AMOUNT
A CAD 2020-01-07 1
A CAD 2020-01-08 0
A CAD 2020-01-09 0
A CAD 2020-01-10 0
A CAD 2020-01-11 0
A CAD 2020-01-12 0
A CAD 2020-01-13 0
A CAD 2020-01-14 0
A CAD 2020-01-15 0
A CAD 2020-01-16 0
A CAD 2020-01-17 0
A CAD 2020-01-18 0
A CAD 2020-01-19 0
A CAD 2020-01-20 0
A CAD 2020-01-21 0
A CAD 2020-01-22 0
A CAD 2020-01-23 0
A CAD 2020-01-24 0
A CAD 2020-01-25 0
A CAD 2020-01-26 0
A CAD 2020-01-27 0
A CAD 2020-01-28 0
A CAD 2020-01-29 0
A CAD 2020-01-30 0
A CAD 2020-01-31 0
A CAD 2020-02-01 0
A CAD 2020-02-02 0
A CAD 2020-02-03 0
A CAD 2020-02-04 0
A CAD 2020-02-05 0
A CAD 2020-02-06 0
A CAD 2020-02-07 0
A CAD 2020-02-08 0
A CAD 2020-02-09 0
A CAD 2020-02-10 0
A CAD 2020-02-11 0
A CAD 2020-02-12 0
A CAD 2020-02-13 0
A CAD 2020-02-14 0
A CAD 2020-02-15 0
A CAD 2020-02-16 0
A CAD 2020-02-17 0
A CAD 2020-02-18 0
A CAD 2020-02-19 0
A CAD 2020-02-20 0
A CAD 2020-02-21 0
A CAD 2020-02-22 0
A CAD 2020-02-23 0
A CAD 2020-02-24 0
A CAD 2020-02-25 0
A CAD 2020-02-26 0
A CAD 2020-02-27 0
A CAD 2020-02-28 0
A CAD 2020-02-29 0
A CAD 2020-03-01 0
A CAD 2020-03-02 0
A CAD 2020-03-03 0
A CAD 2020-03-04 0
A CAD 2020-03-05 0
A CAD 2020-03-06 0
A CAD 2020-03-07 0
A CAD 2020-03-08 0
A CAD 2020-03-09 0
A CAD 2020-03-10 0
A CAD 2020-03-11 0
A CAD 2020-03-12 0
A CAD 2020-03-13 0
A CAD 2020-03-14 0
A CAD 2020-03-15 0
A CAD 2020-03-16 0
A CAD 2020-03-17 0
A CAD 2020-03-18 0
A CAD 2020-03-19 0
A CAD 2020-03-20 0
A CAD 2020-03-21 0
A CAD 2020-03-22 0
A CAD 2020-03-23 0
A CAD 2020-03-24 0
A CAD 2020-03-25 0
A CAD 2020-03-26 0
A CAD 2020-03-27 0
A CAD 2020-03-28 0
A CAD 2020-03-29 0
A CAD 2020-03-30 0
A CAD 2020-03-31 0
A CAD 2020-04-01 0
A CAD 2020-04-02 0
A CAD 2020-04-03 0
A CAD 2020-04-04 0
A CAD 2020-04-05 0
A CAD 2020-04-06 0
A CAD 2020-04-07 0
A CAD 2020-04-08 0
A CAD 2020-04-09 0
A CAD 2020-04-10 0
A CAD 2020-04-11 0
A CAD 2020-04-12 0
A CAD 2020-04-13 0
A CAD 2020-04-14 0
A CAD 2020-04-15 0
A CAD 2020-04-16 0
A CAD 2020-04-17 0
A CAD 2020-04-18 0
A CAD 2020-04-19 0
A CAD 2020-04-20 0
A CAD 2020-04-21 0
A CAD 2020-04-22 0
A CAD 2020-04-23 0
A CAD 2020-04-24 0
A CAD 2020-04-25 0
A CAD 2020-04-26 0
A CAD 2020-04-27 0
A CAD 2020-04-28 0
A CAD 2020-04-29 0
A CAD 2020-04-30 0
A CAD 2020-05-01 0
A CAD 2020-05-02 0
A CAD 2020-05-03 0
A CAD 2020-05-04 0
A CAD 2020-05-05 0
A CAD 2020-05-06 0
A CAD 2020-05-07 0
A CAD 2020-05-08 0
A CAD 2020-05-09 0
A CAD 2020-05-10 0
A CAD 2020-05-11 0
A CAD 2020-05-12 0
A CAD 2020-05-13 0
A CAD 2020-05-14 0
A CAD 2020-05-15 0
A CAD 2020-05-16 0
A CAD 2020-05-17 0
A CAD 2020-05-18 0
A CAD 2020-05-19 0
A CAD 2020-05-20 0
A CAD 2020-05-21 0
A CAD 2020-05-22 0
A CAD 2020-05-23 0
A CAD 2020-05-24 0
A CAD 2020-05-25 0
A CAD 2020-05-26 0
A CAD 2020-05-27 0
A CAD 2020-05-28 0
A CAD 2020-05-29 0
A CAD 2020-05-30 0
A CAD 2020-05-31 0
A CAD 2020-06-01 0
A CAD 2020-06-02 0
A CAD 2020-06-03 0
A CAD 2020-06-04 0
A CAD 2020-06-05 0
A CAD 2020-06-06 0
A CAD 2020-06-07 0
A CAD 2020-06-08 0
A CAD 2020-06-09 0
A CAD 2020-06-10 0
A CAD 2020-06-11 0
A CAD 2020-06-12 0
A CAD 2020-06-13 0
A CAD 2020-06-14 0
A CAD 2020-06-15 0
A CAD 2020-06-16 0
A CAD 2020-06-17 0
A CAD 2020-06-18 0
A CAD 2020-06-19 0
A CAD 2020-06-20 0
A CAD 2020-06-21 0
A CAD 2020-06-22 0
A CAD 2020-06-23 0
A CAD 2020-06-24 0
A CAD 2020-06-25 0
A CAD 2020-06-26 0
A CAD 2020-06-27 0
A CAD 2020-06-28 0
A CAD 2020-06-29 0
A CAD 2020-06-30 0
A CAD 2020-07-01 0
A CAD 2020-07-02 0
A CAD 2020-07-03 0
A CAD 2020-07-04 0
A CAD 2020-07-05 0
A CAD 2020-07-06 0
A CAD 2020-07-07 0
A CAD 2020-07-08 0
A CAD 2020-07-09 0
A CAD 2020-07-10 0
A CAD 2020-07-11 0
A CAD 2020-07-12 0
A CAD 2020-07-13 0
A CAD 2020-07-14 0
A CAD 2020-07-15 0
A CAD 2020-07-16 0
A CAD 2020-07-17 0
A CAD 2020-07-18 0
A CAD 2020-07-19 0
A CAD 2020-07-20 0
A CAD 2020-07-21 0
A CAD 2020-07-22 0
A CAD 2020-07-23 0
A CAD 2020-07-24 0
A CAD 2020-07-25 0
A CAD 2020-07-26 0
A CAD 2020-07-27 0
A CAD 2020-07-28 0
A CAD 2020-07-29 0
A CAD 2020-07-30 0
A CAD 2020-07-31 0
A CAD 2020-08-01 0
A CAD 2020-08-02 0
A CAD 2020-08-03 0
A CAD 2020-08-04 0
A CAD 2020-08-05 0
A CAD 2020-08-06 0
A CAD 2020-08-07 0
A CAD 2020-08-08 0
A CAD 2020-08-09 0
A CAD 2020-08-10 0
A CAD 2020-08-11 0
A CAD 2020-08-12 0
A CAD 2020-08-13 0
A CAD 2020-08-14 0
A CAD 2020-08-15 0
A CAD 2020-08-16 0
A CAD 2020-08-17 0
A CAD 2020-08-18 0
A CAD 2020-08-19 0
A CAD 2020-08-20 0
A CAD 2020-08-21 0
A CAD 2020-08-22 0
A CAD 2020-08-23 0
A CAD 2020-08-24 0
A CAD 2020-08-25 0
A CAD 2020-08-26 0
A CAD 2020-08-27 0
A CAD 2020-08-28 0
A CAD 2020-08-29 0
A CAD 2020-08-30 0
A CAD 2020-08-31 0
A CAD 2020-09-01 0
A CAD 2020-09-02 0
A CAD 2020-09-03 0
A CAD 2020-09-04 0
A CAD 2020-09-05 0
A CAD 2020-09-06 0
A CAD 2020-09-07 0
A CAD 2020-09-08 0
A CAD 2020-09-09 0
A CAD 2020-09-10 0
A CAD 2020-09-11 0
A CAD 2020-09-12 0
A CAD 2020-09-13 0
A CAD 2020-09-14 0
A CAD 2020-09-15 0
A CAD 2020-09-16 0
A CAD 2020-09-17 0
A CAD 2020-09-18 0
A CAD 2020-09-19 0
A CAD 2020-09-20 0
A CAD 2020-09-21 0
A CAD 2020-09-22 0
A CAD 2020-09-23 0
A CAD 2020-09-24 0
A CAD 2020-09-25 0
A CAD 2020-09-26 0
A CAD 2020-09-27 0
A CAD 2020-09-28 0
A CAD 2020-09-29 0
A CAD 2020-09-30 0
A CAD 2020-10-01 0
A CAD 2020-10-02 0
A CAD 2020-10-03 0
A CAD 2020-10-04 0
A CAD 2020-10-05 0
A CAD 2020-10-06 0
A CAD 2020-10-07 0
A CAD 2020-10-08 0
A CAD 2020-10-09 0
A CAD 2020-10-10 0
A CAD 2020-10-11 0
A CAD 2020-10-12 0
A CAD 2020-10-13 0
A CAD 2020-10-14 0
A CAD 2020-10-15 0
A CAD 2020-10-16 0
A CAD 2020-10-17 0
A CAD 2020-10-18 0
A CAD 2020-10-19 0
A CAD 2020-10-20 0
A CAD 2020-10-21 0
A CAD 2020-10-22 0
A CAD 2020-10-23 0
A CAD 2020-10-24 0
A CAD 2020-10-25 0
A CAD 2020-10-26 0
A CAD 2020-10-27 0
A CAD 2020-10-28 0
A CAD 2020-10-29 0
A CAD 2020-10-30 0
A CAD 2020-10-31 0
A CAD 2020-11-01 0
A CAD 2020-11-02 0
A CAD 2020-11-03 0
A CAD 2020-11-04 0
A CAD 2020-11-05 0
A CAD 2020-11-06 0
A CAD 2020-11-07 0
A CAD 2020-11-08 0
A CAD 2020-11-09 0
A CAD 2020-11-10 0
A CAD 2020-11-11 0
A CAD 2020-11-12 0
A CAD 2020-11-13 0
A CAD 2020-11-14 0
A CAD 2020-11-15 0
A CAD 2020-11-16 0
A CAD 2020-11-17 0
A CAD 2020-11-18 0
A CAD 2020-11-19 0
A CAD 2020-11-20 0
A CAD 2020-11-21 0
A CAD 2020-11-22 0
A CAD 2020-11-23 0
A CAD 2020-11-24 0
A CAD 2020-11-25 0
A CAD 2020-11-26 0
A CAD 2020-11-27 0
A CAD 2020-11-28 0
A CAD 2020-11-29 0
A CAD 2020-11-30 0
A CAD 2020-12-01 0
A CAD 2020-12-02 0
A CAD 2020-12-03 0
A CAD 2020-12-04 0
A CAD 2020-12-05 0
A CAD 2020-12-06 0
A CAD 2020-12-07 0
A CAD 2020-12-08 0
A CAD 2020-12-09 0
A CAD 2020-12-10 0
A CAD 2020-12-11 0
A CAD 2020-12-12 0
A CAD 2020-12-13 0
A CAD 2020-12-14 0
A CAD 2020-12-15 0
A CAD 2020-12-16 0
A CAD 2020-12-17 0
A CAD 2020-12-18 0
A CAD 2020-12-19 0
A CAD 2020-12-20 0
A CAD 2020-12-21 0
A CAD 2020-12-22 0
A CAD 2020-12-23 0
A CAD 2020-12-24 0
A CAD 2020-12-25 0
A CAD 2020-12-26 0
A CAD 2020-12-27 0
A CAD 2020-12-28 0
A CAD 2020-12-29 0
A CAD 2020-12-30 0
A CAD 2020-12-31 0
A CAD 2021-01-01 0
A CAD 2021-01-02 0
A CAD 2021-01-03 0
A CAD 2021-01-04 5
A CAD 2021-01-05 0
A CAD 2021-01-06 6
A USD 2020-01-01 3
A USD 2020-01-02 0
A USD 2020-01-03 4
A USD 2020-01-04 2
B CAD 2017-01-04 3
B CAD 2017-01-05 0
B CAD 2017-01-06 2
B CAD 2017-01-07 2
B USD 2019-01-01 3
B USD 2019-01-02 0
B USD 2019-01-03 4
B USD 2019-01-04 5
Simeon Pilgrim
  • 22,906
  • 3
  • 32
  • 45