BigQuery missing rows with SUM OVER PARTITION BY

Question

TL;DR:

Given this table:

WITH subscriptions AS (SELECT TIMESTAMP("2020-11-01") as date, "premium" as product, 50 as diff
  UNION ALL SELECT TIMESTAMP("2020-11-01"), "basic", 100
  UNION ALL SELECT TIMESTAMP("2020-11-02"), "basic", -10
  UNION ALL SELECT TIMESTAMP("2020-11-03"), "premium", 20
  UNION ALL SELECT TIMESTAMP("2020-11-03"), "basic", 40
)

How to do I get a table where the missing date/product combination (2020-11-02 - premium) is included with a fallback value for diff of 0.

Ideally, for multiple products. A list of all products can be get like this:

SELECT ARRAY_AGG(DISTINCT product) FROM subscriptions

I want to be able to get the subscription count per day, either for all products or just for some products.

And the way I think this can be easily achieved is by preparing a database that looks like this:

|---------------------|------------------|------------------|
|         date        |      product     |       total      |
|---------------------|------------------|------------------|
|      2020-11-01     |      premium     |        100       |
|---------------------|------------------|------------------|
|      2020-11-01     |       basic      |        50        |
|---------------------|------------------|------------------|

With this table, I can easily group by date and product or just by date and sum the total.

Before I get to the result table I have generated a table where for each day and product I calculate the difference in subscriptions. How many new subscribers for each product are there and how many are no longer subscribed.

This table looks like this:

|---------------------|------------------|------------------|
|         date        |      product     |       diff       |
|---------------------|------------------|------------------|
|      2020-11-01     |      premium     |        50        |
|---------------------|------------------|------------------|
|      2020-11-01     |       basic      |       -20        |
|---------------------|------------------|------------------|

Meaning on November, 1st the total count of premium subscribers increased by 50, and the total count of basic subscribers decreased by 20.

The problem now is that this temporary table is missing date points if there weren't any changes one product, see the example below.

When I started there was no product table and I only had the date and diff column.

To get from the second to the first table I used this query which worked perfect:

WITH subscriptions AS (SELECT TIMESTAMP("2020-11-01") as date, 150 as diff
  UNION ALL SELECT TIMESTAMP("2020-11-02"), -10
  UNION ALL SELECT TIMESTAMP("2020-11-03"), 60
)
SELECT 
  *,
  SUM(diff) OVER (ORDER BY date) as total_subscriptions
FROM subscriptions
ORDER BY date

But when I add the product column and try to calculate the sum per day and product there are some data points missing.

WITH subscriptions AS (SELECT TIMESTAMP("2020-11-01") as date, "premium" as product, 50 as diff
  UNION ALL SELECT TIMESTAMP("2020-11-01"), "basic", 100
  UNION ALL SELECT TIMESTAMP("2020-11-02"), "basic", -10
  UNION ALL SELECT TIMESTAMP("2020-11-03"), "premium", 20
  UNION ALL SELECT TIMESTAMP("2020-11-03"), "basic", 40
)
SELECT 
  *,
  SUM(diff) OVER (PARTITION BY product ORDER BY date) as total_subscriptions
FROM subscriptions
ORDER BY date

--

|---------------------|------------------|------------------|
|         date        |      product     |      total       |
|---------------------|------------------|------------------|
|      2020-11-01     |       basic      |       100        |
|---------------------|------------------|------------------|
|      2020-11-01     |      premium     |        50        |
|---------------------|------------------|------------------|
|      2020-11-02     |       basic      |        90        |
|---------------------|------------------|------------------|
|      2020-11-03     |       basic      |       130        |
|---------------------|------------------|------------------|
|      2020-11-03     |      premium     |        70        |
|---------------------|------------------|------------------|

If I now show the total number of subscriptions per day, I would get:

150 -> 90 -> 200

But I would expect:

150 -> 140 -> 200

Same goes for the total number of premium subscriptions per day:

50 -> 0 -> 70

But I would expect:

50 -> 50 -> 70

I believe the best option to fix this would be to add the missing date/product combinations.

How would I do this?

user_11706377 · Answer

      -- Try this,I am creating a table for list of products and add total product in that list. Joining with your table to get data as per your requirement.
      WITH subscriptions AS (SELECT TIMESTAMP("2020-11-01") as date, "premium" as product, 50 as diff
        UNION ALL SELECT TIMESTAMP("2020-11-01"), "basic", 100
        UNION ALL SELECT TIMESTAMP("2020-11-02"), "basic", -10
        UNION ALL SELECT TIMESTAMP("2020-11-03"), "premium", 20
        UNION ALL SELECT TIMESTAMP("2020-11-03"), "basic", 40
      ),

      product_name as (
      Select product from subscriptions group by 1
      union all
      Select "Total" as product
      )

      Select date
            ,product
            ,total_subscriptions
      from (      
      Select a.date
            ,a.product
            ,diff
            ,SUM(diff) OVER (PARTITION BY a.product ORDER BY a.date) as total_subscriptions
      from 
      (
      Select date,a.product
      from product_name A
       join subscriptions B
       on 1=1
       where a.product !='Total'
      group by 1,2
      ) A
      left join subscriptions B 
      on A.product = B.product
      and A.date = B.date
      group by 1,2,3
      ) group by 1,2,3
      union all
      Select date
            ,product
            ,total_subscriptions
      from 
      (
      Select date,a.product
            ,diff
            ,SUM(diff) OVER (PARTITION BY a.product ORDER BY date) as total_subscriptions
      from product_name A
       join subscriptions B
       on 1=1
       where a.product ='Total'
      group by 1,2,3
      ) group by 1,2,3
      order by 1,2

user_13473525 · Answer

Use GENERATE_TIMESTAMP_ARRAY:

WITH subscriptions AS (SELECT TIMESTAMP("2020-11-01") as date, "premium" as product, 50 as diff
  UNION ALL SELECT TIMESTAMP("2020-11-01"), "basic", 100
  UNION ALL SELECT TIMESTAMP("2020-11-02"), "basic", -10
  UNION ALL SELECT TIMESTAMP("2020-11-03"), "premium", 20
  UNION ALL SELECT TIMESTAMP("2020-11-03"), "basic", 40
),
dates AS (
  SELECT * 
  FROM UNNEST(GENERATE_TIMESTAMP_ARRAY('2020-11-01 00:00:00', '2020-11-03 00:00:00', INTERVAL 1 DAY)) as date
),
products AS (
  SELECT DISTINCT product FROM subscriptions
)
SELECT dates.date, products.product, subscriptions.diff
FROM dates 
CROSS JOIN products
LEFT JOIN subscriptions 
ON subscriptions.date = dates.date AND subscriptions.product = products.product

user_10676716 · Answer

If I follow you correctly, one approach is to can generate a fixed the list of dates for the period you want, and cross join it with the list of products. This gives you all possible combinations. Then, you can bring the subscriptions table with a left join, and finally perform the window sum:

select d.dt, p.product, sum(s.diff) over(partition by p.product order by d.dt) total
from unnest(generate_timestamp_array(
    timestamp('2020-11-01'), 
    timestamp('2020-11-03'), 
    interval 1 day)
) dt
cross join (
    select 'basic' product 
    union all select 'premium'
) p
left join subscriptions on s.product = p.product and s.date = dt

We can make the query a more generic by dynamically generating the date range and list of products:

select d.dt, p.product, sum(s.diff) over(partition by p.product order by d.dt) total
from (select min(date) min_dt, max(date) max_dt from subscriptions) d0
cross join unnest(generate_timestamp_array(d0.min_dt, d0.max_dt, interval 1 day)) dt
cross join (select distinct product from subscriptions) p
left join subscriptions on s.product = p.product and s.date = dt

BigQuery missing rows with SUM OVER PARTITION BY

3 Answers