JOIN Clause 🔗

Use join when you need to combine two result tables in the same query — most often when comparing a benchmark period to a recent period on the same line or product.

For reshaping or re-summarizing a single helper query, prefer from instead — see FROM Clause.

Syntax

WITH <helper_name> AS ( <first query> )
AGGREGATE
    <helper_name>.<column> as <name>,
    <new calculation> as <name>
JOIN <helper_name> USING (<shared column names>)
WHERE <filters for the new calculation>
GROUP BY <shared dimensions> as <column names>

with saves the first result set (for example, a 30-day benchmark).
join lines up rows that share the same column values (for example, the same line name).
using (...) lists those shared column names.

Example 1: Benchmark vs last 24 hours (one column to match on)

Compare each line’s 30-day 75th percentile to the last 24 hours:

with benchmark as (
    aggregate p75(metric_group('performance_uuid')) as p75
    where time from now()-30D to now()
      and factory.name = 'Factory A'
    group by line.name as linename
)
aggregate
    benchmark.p75 as p75_30d,
    p75(metric_group('performance_uuid')) as p75_24h
join benchmark using (linename)
where time from now()-24h to now()
  and factory.name = 'Factory A'
group by line.name as linename

Example 2: Match on line and product

with benchmark as (
    aggregate p75(metric_group('performance_uuid')) as p75
    where time from now()-30D to now()
      and factory.name = 'Factory A'
    group by line.name as linename, product.name as productname
)
aggregate
    benchmark.p75 as p75_30d,
    p75(metric_group('performance_uuid')) as p75_24h
join benchmark using (linename, productname)
where time from now()-24h to now()
  and factory.name = 'Factory A'
group by line.name as linename, product.name as productname

The same linename and productname must appear in both the helper query and the outer group by.

Example 3: Cross join (no matching columns)

using () with nothing inside combines every row from the helper with every row from the main query. Use this only when you intentionally want all combinations.

with benchmark as (
    aggregate p75(metric_group('performance_uuid')) as p75
    where time from now()-30D to now()
      and line.name = 'Line 1'
    group by line.name as linename
)
aggregate
    benchmark.p75 as p75_30d,
    p75(metric_group('performance_uuid')) as p75_24h
join benchmark using ()
where time from now()-24h to now()
  and line.name = 'Line 1'
group by line.name as linename

Quick reference

Pattern	When to use it
`from helper`	Summarize or reshape one helper query
`join helper using (linename)`	Add helper columns next to new calculations on the same line/product
`join helper using ()`	Cross join — every row paired with every row

✨ Prefer from for simple second-step math. Use join when two time ranges or two datasets must appear in the same row.