subquery-patterns-and-union

Period-over-period comparisons in OPAL are performed by defining labeled subqueries for different time ranges, applying timeshift to align buckets, and unioning the results to compare metrics across adjacent periods. You then collapse the sparse union output to produce a single summary row per group.

Merging multiple datasets in OPAL for time-series analysis is best done using union patterns. You define separate subqueries with @label <- @dataset syntax, union their comparable columns to merge results, and apply aggregations to produce aligned time-series outputs.

The any_not_null function collapses sparse union results into a single per-group row. When you union subqueries from different periods or datasets, it merges the non-null values, ensuring each group has one consolidated record for accurate period-over-period analysis.

Yes, you can use timeshift in OPAL to shift data and align buckets for accurate joins. It adjusts the time periods of your subqueries so that current and previous periods match, enabling accurate multi-dataset analysis and period-over-period comparisons.

You should use labeled subqueries with @label <- @dataset when building complex multi-stage calculations. They organize pipelines into reusable blocks, making it easier to apply timeshift, union different periods, and handle multi-dataset analysis without repeating query logic.

An OPAL union returns sparse rows because each subquery populates different columns based on its time period. To resolve this, apply the any_not_null collapse function to merge the sparse union results into a single per-group row for clean period-over-period comparisons.