sql - Duplicate groups of records to fill multiple date gaps in Google BigQuery

Question

I've found a similar question (Duplicating records to fill gap between dates in Google BigQuery), however with a different scenario and the answer there does not apply.

I have data structured like so (which is basically price-change history for multiple products and partners):

+------------+---------+---------+-------+
|    date    | product | partner | value |
+------------+---------+---------+-------+
| 2017-01-01 | a       | x       |    10 |
| 2017-01-01 | b       | x       |    15 |
| 2017-01-01 | a       | y       |    11 |
| 2017-01-01 | b       | y       |    16 |
| 2017-01-05 | b       | x       |    13 |
| 2017-01-07 | a       | y       |    15 |
| 2017-01-07 | a       | x       |    15 |
+------------+---------+---------+-------+

What I need is a query (specifically written in BigQuery Standard SQL) that, given a date range (in this case, 2017-01-01 to 2017-01-10), outputs the following result:

+--------------+---------+---------+-------+
|      date    | product | partner | value |
+--------------+---------+---------+-------+
|   2017-01-01 | a       | x       |    10 |
|   2017-01-02 | a       | x       |    10 |
|   2017-01-03 | a       | x       |    10 |
|   2017-01-04 | a       | x       |    10 |
|   2017-01-05 | a       | x       |    10 |
|   2017-01-06 | a       | x       |    10 |
|   2017-01-07 | a       | x       |    15 |
|   2017-01-08 | a       | x       |    15 |
|   2017-01-09 | a       | x       |    15 |
|   2017-01-10 | a       | x       |    15 |
|   2017-01-01 | a       | y       |    11 |
|   2017-01-02 | a       | y       |    11 |
|   2017-01-03 | a       | y       |    11 |
|   2017-01-04 | a       | y       |    11 |
|   2017-01-05 | a       | y       |    11 |
|   2017-01-06 | a       | y       |    11 |
|   2017-01-07 | a       | y       |    15 |
|   2017-01-08 | a       | y       |    15 |
|   2017-01-09 | a       | y       |    15 |
|   2017-01-10 | a       | y       |    15 |
|   2017-01-01 | b       | x       |    15 |
|   2017-01-02 | b       | x       |    15 |
|   2017-01-03 | b       | x       |    15 |
|   2017-01-04 | b       | x       |    15 |
|   2017-01-05 | b       | x       |    13 |
|   2017-01-06 | b       | x       |    13 |
|   2017-01-07 | b       | x       |    13 |
|   2017-01-08 | b       | x       |    13 |
|   2017-01-09 | b       | x       |    13 |
|   2017-01-10 | b       | x       |    13 |
|   2017-01-01 | b       | y       |    16 |
|   2017-01-02 | b       | y       |    16 |
|   2017-01-03 | b       | y       |    16 |
|   2017-01-04 | b       | y       |    16 |
|   2017-01-05 | b       | y       |    16 |
|   2017-01-06 | b       | y       |    16 |
|   2017-01-07 | b       | y       |    16 |
|   2017-01-08 | b       | y       |    16 |
|   2017-01-09 | b       | y       |    16 |
|   2017-01-10 | b       | y       |    16 |
+--------------+---------+---------+-------+

Basically a price history with all date gaps filled, for every combination of product and partner.

I'm having a hard time figuring out how to get this done, especially how to generate multiple rows for the same date where no price change has happened. Any ideas?

Answer 1

Waitting for answers