malloy-queries

Files (1)

• SKILL.md

SKILL.md (first lines)

<!--
Copyright (c) Credible Data Inc.
SPDX-License-Identifier: MIT
-->

# Malloy Query Reference

Only use field names defined in the model. Ground yourself first with `get_context`; never invent entities or guess field names.

> **Tool names** are written bare here - `get_context`, `execute_query`, `search_malloy_docs`. The exact prefixed name depends on the host surface; match each against the tools you actually have.

## Query Patterns

**Simple aggregation:**
```malloy
run: source -> {
  aggregate: total_revenue, order_count
}
```

**Group by dimension:**
```malloy
run: source -> {
  group_by: category
  aggregate: revenue
  order_by: revenue desc
  limit: 10
}
```

**Time trend:**
```malloy
# line_chart
run: source -> {
  group_by: order_date.month
  aggregate: revenue
  order_by: 1
}
```

**Filtered query:**
```malloy
run: source -> {
  where: status = 'active'
  group_by: region
  aggregate: count_orders, total_revenue
}
```

**Run a pre-built view:**
```malloy
run: source -> view_name
```

**Refine a view with additional options:**
```malloy
run: source -> view_name + { limit: 10, where: region = 'US' }
```

**Percent of total:** use `all()`, not `parent()`.
```malloy
run: source -> {
  group_by: category
  aggregate:
    revenue
    pct_of_total is revenue / all(revenue)
}
```

**Conditional dimensions with `pick`:** `pick` is a keyword, not a function.
```malloy
run: source -> {
  group_by:
    tier is pick 'Premium' when price > 100
            pick 'Standard' when price > 50
            else 'Budget'
  aggregate: count()
}
```
Wrong: `pick('Premium') { ... }` (that's not Malloy syntax).

**Window functions with `calculate:`:** running totals, `lag()`, `lead()`, and other window operations belong in `calculate:`, not `aggregate:`.
```malloy
run: source -> {
  group_by: order_month is order_date.month
  aggregate: revenue
  calculate: prev_month_revenue is lag(revenue)
  order_by: order_month
}
```

## Field Paths and Joins

**Use the joins the model declares.** When you answer a question over a published model, every query is rooted on one source, and you reach a joined source's fields by a dotted path within the query body (for example `stores.region`). Do not write `join_one` or `join_many` in an answer query, and never invent a join key. (A join written in a query does compile, and model files and notebooks use them; the rule is that an answer uses the joins the model declares.) If the field you need is on a source the model does not join, say so and ask the user to add the join to the model. If you do write a join in query text, it is gated as if you had entered that source yourself: a locked source refuses the query with a 403 that names your alias, and a row-filtered source filters the rows your join sees, so a denial on a join you wrote means the joined source refused you, not that the model is broken.

The `->` operator separates a **source** from a **view** (query transformation). It does NOT navigate between joined sources.

Wrong:
```malloy
run: candidate -> hiring_manager -> { aggregate: employee_count }
```
Right:
```malloy
run: candidate -> { aggregate: hiring_manager.employee_count }
```

Use the field paths defined in the model **verbatim**. If the model defines `hiring_manager.employee_count`, do not strip the `hiring_manager.` prefix; that prefix is the join namespace, not a separate source to navigate to.

## Dates and Time

### Comparisons need `@` literals

**Use `@` date literals for comparisons.** Never compare a timestamp to a bare number.

Wrong (compares a timestamp to the integer `2020`):
```malloy
where: order_date.year >= 2020
```
Right:
```malloy

From malloydata/publisher, MIT.

More from malloydata/publisher