subquery-patterns-and-union

What problem does it solve?

This Skill provides a structured pattern library to build and combine multiple OPAL subqueries using labels, unions, and timeshift, enabling powerful period-over-period comparisons and multi-dataset analysis.

Core Features & Use Cases

• Subquery syntax: define reusable blocks with @label <- @dataset to organize complex pipelines.
• Union patterns: merge results from different periods or datasets while preserving comparable columns.
• Timeshift and alignment: shift data to compare adjacent periods (e.g., current vs previous hour) and align buckets for accurate joins.
• any_not_null() collapse: collapse sparse union results into a single per-group row.
• Use Case: perform period-over-period metric comparisons, time-series analysis, and build intermediate results for multi-stage calculations.

Quick Start

Run a simple example: build a current subquery for a metric, a timeshifted previous subquery, union their results and apply aggregation to produce a per-group summary.