Skip to main content

Query Examples

Query Examples

These examples collect less-common query patterns in one place. They use an Iris model or a generic university model with students, courses, and enrollments. Adapt the concept paths to the models shown by trilogy explore.

Full query structure

A query has a fixed clause order. Only SELECT is required.

<top-level definitions>;
WHERE <input filter>
SELECT <dimensions and expressions>
SUBSET|UNION JOIN <left key> = <right key>
BY ROLLUP|CUBE|GROUPING SETS (...)
HAVING <output filter>
ORDER BY <expression> ASC|DESC
LIMIT <count>;

Top-level definitions and named rowsets precede the final query:

import enrollments as enroll;
import students as students;

auto completed_credits <- sum(
    enroll.credits ? enroll.completed = true
);

rowset department_totals <- select
    enroll.department as department,
    sum(enroll.credits) as total_credits,
;

where enroll.year >= 2015
select
    students.major,
    count(enroll.id) as enrollment_count,
    --completed_credits,
subset join enroll.student_id = students.id
having completed_credits > 0
order by enrollment_count desc nulls first
limit 100;

select
    department_totals.department,
    department_totals.total_credits,
order by department_totals.total_credits desc nulls first;

WHERE filters input rows before aggregation. HAVING filters the result after joins, grouping, aggregation, and windows. A field prefixed with -- is hidden, not commented out: it still participates in the query.

Filtered aggregate

The ? operator filters one expression's input. This lets several differently filtered measures share a query.

import iris as iris;

select
    iris.species,
    avg(iris.petal_length) as avg_petal,
    avg(
        iris.petal_length ? iris.petal_width > 1.5
    ) as avg_petal_wide_only,
    count(
        iris.petal_length ? iris.sepal_length > 6.0
    ) as long_sepal_count,
order by iris.species asc;

To count rows, count the model's unique grain key. count(k) counts distinct values of k, so counting a repeated sub-key can undercount rows.

Row-level WHERE versus aggregate HAVING

A per-row condition belongs in WHERE:

import iris as iris;

where iris.sepal_length > 6.0
select
    iris.species,
    avg(iris.petal_length) as avg_petal,
order by iris.species asc;

An aggregate result condition belongs in HAVING:

select
    iris.species,
    avg(iris.petal_length) as avg_petal,
having avg_petal > 4.0
order by avg_petal desc;

These are not interchangeable. having max(iris.sepal_length) > 6.0 keeps entire species groups whose maximum passes the threshold; it does not remove short flowers before calculating the average.

Compare an entity total with its group average

Give each aggregation layer an explicit grain. Here the inner value is a total per student and department; the outer value averages those totals by department.

import enrollments as enroll;

auto student_department_credits <- sum(enroll.credits)
    by enroll.student_id, enroll.department;

auto department_avg_credits <- avg(student_department_credits)
    by enroll.department;

select
    enroll.student_id,
    --student_department_credits,
    --department_avg_credits,
having student_department_credits > 1.2 * department_avg_credits;

Selecting the measures as hidden fields makes them available to HAVING without returning them.

Pivot dimension values into columns

A def macro paired with a filtered aggregate provides a concise pivot:

import iris as iris;

def species_avg(species_name) ->
    avg(iris.petal_length ? iris.species = species_name);

select
    @species_avg('setosa') as setosa_avg_petal,
    @species_avg('versicolor') as versicolor_avg_petal,
    @species_avg('virginica') as virginica_avg_petal,
;

With no row dimensions in the select, this returns one summary row.

Build categories with CASE

Create a reusable derived category, then group and sort with it:

import iris as iris;

auto petal_size <- case
    when iris.petal_length < 2.0 then 'small'
    when iris.petal_length < 5.0 then 'medium'
    else 'large'
end;

select
    petal_size,
    count(iris.petal_length) as flower_count,
    avg(iris.petal_width) as avg_petal_width,
order by flower_count desc;

Use else null when unmatched rows should fall out of downstream grouping.

Period-over-period comparison

Use lag or lead to compare a row with an offset period:

import enrollments as enroll;

auto recent_year <- enroll.year ? enroll.year >= 2015;

def versus_prior_year(amount) ->
    round(
        amount / (
            lag(amount, 1) over (order by enroll.year asc)
        ),
        2
    );

where enroll.year in recent_year
select
    enroll.year,
    @versus_prior_year(count(enroll.id)) as enrollment_growth,
having enrollment_growth is not null
order by enroll.year asc;

The filtered concept creates a reusable value set. The window sees the rows that survive WHERE; use HAVING when a filter must run after the window.

Stack channels with the union(...) TVF

union(...) stacks two or more self-contained query arms by column position:

import enrollments as enroll;

with by_channel as union(
    (
        where enroll.department = 'Biology'
        select
            'Biology' as channel,
            enroll.course as course,
            count(enroll.id) as enrollment_count,
    ),
    (
        where enroll.department = 'Chemistry'
        select
            'Chemistry' as channel,
            enroll.course as course,
            count(enroll.id) as enrollment_count,
    ),
    (
        where enroll.department = 'Physics'
        select
            'Physics' as channel,
            enroll.course as course,
            count(enroll.id) as enrollment_count,
    )
) -> (channel, course, enrollment_count);

select
    by_channel.channel,
    by_channel.course,
    by_channel.enrollment_count,
order by by_channel.channel asc,
    by_channel.enrollment_count desc nulls first
limit 100;

The output list names the positional columns. Arms may have independent filters, aggregates, and scoped joins, but must return compatible shapes.

See Table-Valued and Row-Producing Functions for the full contract.

Blend models with scoped joins

A scoped join declares how key domains relate for one query:

  • subset join a = b declares that values of a are contained in b.
  • union join a = b keeps unmatched values from both domains.
import enrollments as enroll;
import students as students;

select
    students.major,
    count(enroll.id) as enrollment_count,
subset join enroll.student_id = students.id
order by enrollment_count desc nulls first;

Join on the complete shared grain. For a two-part grain, use one equality group per key:

rowset completed <- where enroll.completed = true
    select
        enroll.department as department,
        enroll.course as course,
        count(enroll.id) as completed_count;

rowset incomplete <- where enroll.completed = false
    select
        enroll.department as department,
        enroll.course as course,
        count(enroll.id) as incomplete_count;

select
    completed.department,
    completed.course,
    completed.completed_count,
    incomplete.incomplete_count,
union join completed.department = incomplete.department
union join completed.course = incomplete.course
order by completed.department asc nulls first;

Keys may be expressions. This compares each year to the previous year:

rowset current_year <- select
    enroll.year as year,
    count(enroll.id) as enrollment_count;

rowset prior_year <- select
    enroll.year as year,
    count(enroll.id) as enrollment_count;

select
    current_year.year,
    current_year.enrollment_count as current_count,
    prior_year.enrollment_count as prior_count,
union join current_year.year = prior_year.year + 1
order by current_year.year asc;

Joins preserve rows. Add explicit is not null predicates when the desired result is an intersection.

Existence and anti-joins

Use membership instead of a subquery:

import students as students;
import enrollments as enroll;

auto biology_students <- enroll.student_id
    ? enroll.course = 'Biology 101';

where students.id in biology_students
select
    students.major,
    count(students.id) as enrolled_students;

Use not in for an anti-join:

where students.id not in enroll.student_id
select
    students.major,
    count(students.id) as never_enrolled;

For a composite key, compare tuples so the parts are matched row-wise:

rowset multi_year_courses <- select
    enroll.department as department,
    enroll.course as course,
having count_distinct(enroll.year) > 1;

where
    (enroll.department, enroll.course)
    in (
        multi_year_courses.department,
        multi_year_courses.course
    )
select
    enroll.student_id,
    count(enroll.id) as enrollment_count;

Testing each tuple component independently can admit unrelated cross-pairs.

Set intersection and difference

For multi-column INTERSECT or EXCEPT behavior, group by the full tuple and create a presence count for each population:

import enrollments as enroll;

auto in_completed <- sum(
    case when enroll.completed = true then 1 else 0 end
) by enroll.student_id, enroll.course;

auto in_open <- sum(
    case when enroll.completed = false then 1 else 0 end
) by enroll.student_id, enroll.course;

select
    sum(
        case
            when in_completed > 0 and in_open > 0 then 1
            else 0
        end
    ) as in_both,
    sum(
        case
            when in_completed > 0 and in_open = 0 then 1
            else 0
        end
    ) as completed_only,
;

Grouping preserves null key parts as their own group. Avoid manufacturing a single concatenated key unless nullable parts are handled explicitly.

Deduplicate before aggregating

Trilogy does not use SELECT DISTINCT. A rowset groups at its own output grain, so selecting a tuple into a rowset collapses identical tuples before a later aggregate:

import enrollments as enroll;

rowset unique_student_courses <- select
    enroll.student_id as student_id,
    enroll.course as course,
;

select
    unique_student_courses.course,
    count(unique_student_courses.student_id) as distinct_students,
order by unique_student_courses.course asc;

Alias every rowset field that will be referenced downstream. Without an alias, the field retains its complete source path under the rowset namespace.

Subtotals and a grand total with ROLLUP

by rollup (department, course) calculates detail rows, department subtotals, and a grand total in one pass:

import enrollments as enroll;

select
    case
        when grouping(enroll.department) = 1 then 'ALL DEPARTMENTS'
        else enroll.department
    end as department,
    case
        when grouping(enroll.course) = 1 then 'ALL COURSES'
        else enroll.course
    end as course,
    sum(enroll.credits) as total_credits,
    --grouping(enroll.department) + grouping(enroll.course) as grouping_level,
by rollup (enroll.department, enroll.course)
having total_credits > 0
order by grouping_level asc,
    department asc nulls first,
    course asc nulls first
limit 100;

grouping(field) distinguishes generated total rows from real nulls. Project a hidden grouping level when it is needed for sorting.

Rank within each ROLLUP level

Use one multi-key window over the rollup output. Partition by the grouping level and parent so detail rows rank within their parent while subtotals rank among themselves.

import enrollments as enroll;

auto enrollment_count <- count(enroll.id);
auto course_grouped <- grouping(enroll.course);
auto year_grouped <- grouping(enroll.year);
auto grouping_level <- course_grouped + year_grouped;
auto parent <- case
    when year_grouped = 0 then enroll.course
    else null
end;
auto level_rank <- rank(enroll.course, enroll.year) over (
    partition by grouping_level, parent
    order by enrollment_count desc
);

select
    enroll.course,
    enroll.year,
    enrollment_count,
    grouping_level,
    --parent,
    level_rank,
by rollup (enroll.course, enroll.year)
order by grouping_level desc nulls first,
    parent asc nulls first,
    level_rank asc nulls first
limit 100;

The select-level grouping clause applies consistently to the aggregates and grouping(...) expressions.

Stage coarse-grain membership

When a fine-grain query depends on a condition calculated at a coarser grain, calculate the membership set first:

import enrollments as enroll;

rowset multi_year <- select
    enroll.course as course,
    --count_distinct(enroll.year) as year_count,
having year_count > 1;

where enroll.course in multi_year.course
select
    enroll.course,
    count(enroll.id) as enrollment_count,
order by enrollment_count desc
limit 100;

Staging prevents the coarse aggregate from being unintentionally recomputed at the final query's finer grain.

Correlated existence with grouped counts

A self-referential EXISTS or NOT EXISTS can be expressed as filtered counts pinned to the correlation grain:

import enrollments as enroll;

auto enrollees_per_course <- count(enroll.student_id)
    by enroll.course;

auto completers_per_course <- count(
    enroll.student_id ? enroll.completed = true
) by enroll.course;

where
    enroll.completed = true
    and enrollees_per_course > 1
    and completers_per_course = 1
select
    enroll.student_id,
    count(enroll.course) as sole_completions,
order by sole_completions desc
limit 100;

The explicit by enroll.course is essential because the correlation grain differs from the final student-level output grain.