Analytics Engineering, Bigquery, Sql, Data Engineering, Editors Pick.
Schematically, this will look like this: Schematical representation of going from a history (date range) to event representation. Image by author. Here, the “missing” values have been represented with the magic number -1. This is because in step 3. we don’t want to treat them the same way as NULLs