> ## Documentation Index
> Fetch the complete documentation index at: https://docs.uselayers.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Syntax

> Syntax reference for LayersQL queries — SELECT, SHOW, GROUP BY, WHERE, and time windows — with predictable structure for ecommerce analytics queries.

Clause ordering and optionality

* **Required:** FROM · SHOW · (SINCE or DURING)
* **Optional:** GROUP BY · WHERE · UNTIL · TIMESERIES · SEGMENT BY · COMPARE TO · WITH · VISUALIZE
* **Canonical order:** FROM → SHOW → (GROUP BY / WHERE / SEGMENT BY) → (SINCE \[UNTIL] | DURING) → \[TIMESERIES] → \[SEGMENT BY] → \[COMPARE TO] → \[WITH ...] → \[VISUALIZE]

The parser accepts flexible ordering of WHERE, GROUP BY, and SEGMENT BY clauses before the time bound. SEGMENT BY can also appear after the time bound or TIMESERIES for backwards compatibility.

Use either SINCE (with an optional UNTIL) or DURING to bound a query, but not both. DURING is a single-token shortcut for common ranges; SINCE/UNTIL gives you full control over the start and end.

Minimal example

```sql theme={null}
FROM products
SHOW SUM(total_sales)
GROUP BY product_id
SINCE -7d
```

Full example

```sql theme={null}
FROM products
SHOW SUM(total_sales) + SUM(quantity_purchased) * 10 AS weighted_score
GROUP BY TOP 10 product_id
WHERE geo_country = 'US' AND marketing_source IS NOT NULL
SINCE last_month
UNTIL this_month
TIMESERIES day
SEGMENT BY geo_city
COMPARE TO previous_year
WITH TOTALS, PERCENT_CHANGE
VISUALIZE SUM(total_sales) TYPE line MAX 30
```

## Clauses

### FROM

* **Purpose:** Selects the dataset.
* **Example:**
  ```sql theme={null}
  FROM products
  ```

### SHOW

* **Purpose:** Lists expressions to compute. Typically uses aggregation functions over metrics or fields.
* **Examples:**
  ```sql theme={null}
  SHOW SUM(total_sales)
  SHOW COUNT(quantity_purchased), MAX(total_sales)
  SHOW COUNT(*)
  ```

### GROUP BY

* **Purpose:** Defines the grouping keys for aggregation.
* **Keys:** Can be identifiers, dimensions, or product\_attributes.\* paths.
* **TOP N Modifier:** Limit results to the top N values for a dimension.
* **Examples:**
  ```sql theme={null}
  GROUP BY product_id
  GROUP BY product_attributes.color, product_attributes.size
  GROUP BY TOP 5 product_title
  GROUP BY ONLY TOP 10 geo_country
  GROUP BY TOP 5 OVERALL marketing_campaign
  ```

#### TOP N modifier

The TOP N modifier limits grouped results to show only the highest-ranking values. Three variants are available:

* **TOP N**: Shows the top N values plus an "Other" category for the remainder.
  ```sql theme={null}
  GROUP BY TOP 5 product_title
  ```

* **ONLY TOP N**: Shows only the top N values, hiding the remainder entirely.
  ```sql theme={null}
  GROUP BY ONLY TOP 5 product_title
  ```

* **TOP N OVERALL**: Ranks values across the full time range rather than per-period. Useful with TIMESERIES to maintain consistent rankings.
  ```sql theme={null}
  GROUP BY TOP 5 OVERALL product_title
  ```

### WHERE (optional)

* **Purpose:** Filters rows before aggregation using boolean expressions over dimensions, fields, or attributes.
* **Comparison operators:** `=`, `!=`, `<`, `<=`, `>`, `>=`
* **Logical operators:** `AND`, `OR`, `NOT`
* **Null handling:** `IS NULL`, `IS NOT NULL`
* **Precedence:** Parentheses are supported for grouping conditions.
* **Examples:**
  ```sql theme={null}
  WHERE geo_country = 'US'
  WHERE geo_country = 'US' AND marketing_source = 'facebook'
  WHERE (geo_country = 'US' OR geo_country = 'CA') AND marketing_source IS NOT NULL
  WHERE NOT marketing_campaign = 'holiday_sale'
  WHERE product_attributes.color = 'red'
  ```

### SINCE

* **Purpose:** Defines the start of the time window for the query.
* **Accepted forms:**
  * Relative: -7d, -14d, -30d, -60d, -90d, -180d, -365d, -24h, -48h, -12m, -1y
  * Keywords: today, yesterday, this\_week, last\_week, this\_month, last\_month, this\_quarter, last\_quarter, this\_year, last\_year
  * Rolling windows: past\_7\_days, past\_14\_days, past\_30\_days, past\_60\_days, past\_90\_days, past\_180\_days, past\_365\_days
  * Custom rolling window: past\_N\_days where N is any positive integer (for example, past\_45\_days, past\_120\_days)
  * Year-to-date: year\_to\_date
  * Black Friday / Cyber Monday by year: bfcm2024, bfcm2025 (the four days from Black Friday through Cyber Monday)
  * ISO: 2025-03-15 or 2025-03-15T10:30:00Z (timezone offsets supported, ISO 8601)
* **Examples:**
  ```sql theme={null}
  SINCE -7d
  SINCE this_month
  SINCE past_45_days
  SINCE year_to_date
  SINCE bfcm2024
  SINCE 2025-01-01
  SINCE 2025-01-01T00:00:00Z
  ```

### UNTIL (optional)

* **Purpose:** Defines the end of the time window for the query. When omitted, the query runs up to the current time.
* **Accepted forms:** Same as SINCE (relative offsets, keywords, rolling windows, ISO timestamps).
* **Examples:**
  ```sql theme={null}
  SINCE -30d UNTIL -7d
  SINCE last_month UNTIL this_month
  SINCE 2024-01-01 UNTIL 2024-12-31
  ```

<Note>
  The UNTIL clause is currently parsed but not yet applied during metric execution. End date filtering will be supported in a future release.
</Note>

### DURING

* **Purpose:** A single-token shortcut for common time ranges. Use DURING instead of SINCE/UNTIL when one of the named ranges fits your query.
* **Accepted forms:**
  * Calendar keywords: today, yesterday, this\_week, last\_week, this\_month, last\_month, this\_quarter, last\_quarter, this\_year, last\_year
  * Rolling windows: past\_7\_days, past\_30\_days, past\_90\_days, past\_N\_days (any positive integer N)
  * Year-to-date: year\_to\_date
  * Black Friday / Cyber Monday by year: bfcm2024, bfcm2025
* **Mutually exclusive:** A query uses either SINCE (with optional UNTIL) or DURING — never both.
* **Examples:**

  ```sql theme={null}
  FROM products
  SHOW SUM(total_sales)
  GROUP BY product_id
  DURING last_month
  ```

  ```sql theme={null}
  FROM products
  SHOW SUM(total_sales)
  GROUP BY product_id
  DURING bfcm2024
  ```

### TIMESERIES (optional)

* **Purpose:** Groups results by time intervals with automatic backfilling for missing periods.
* **Granularities:**
  * Standard: `day`, `week`, `month`, `quarter`, `year`, `hour`, `minute`
  * Cyclical: `day_of_week`, `hour_of_day`, `week_of_year`, `month_of_year`
* **Examples:**
  ```sql theme={null}
  TIMESERIES day
  TIMESERIES week
  TIMESERIES month
  TIMESERIES hour_of_day
  ```

When using TIMESERIES, the query results include a time dimension that shows values for each period within the SINCE range. Missing periods are automatically backfilled with zero values, making it ideal for charting and trend analysis.

### SEGMENT BY (optional)

* **Purpose:** Adds an additional segmentation dimension applied to each group.
* **Examples:**
  ```sql theme={null}
  SEGMENT BY geo_country
  SEGMENT BY marketing_campaign, geo_country
  ```

### COMPARE TO (optional)

* **Purpose:** Repeats the query over a shifted time window and returns both result sets so you can compare two periods (for example, this month vs. last month). The shifted window has the same length as the primary window.
* **Syntax:** `COMPARE TO <target>`
* **Targets:**
  * `previous_period` — shifts back by the exact length of the primary window (a 7-day window compares against the prior 7 days).
  * `previous_month` — shifts back by one calendar month.
  * `previous_year` — shifts back by one calendar year.
  * `previous_year_match_day_of_week` — shifts back 364 days so the same days of the week align (useful for retail where day-of-week effects matter).
* **Combine with WITH PERCENT\_CHANGE** to surface the percentage delta between the two periods.
* **Examples:**

  ```sql theme={null}
  FROM products
  SHOW SUM(total_sales)
  GROUP BY product_id
  SINCE this_month
  COMPARE TO previous_month
  ```

  ```sql theme={null}
  FROM products
  SHOW SUM(total_sales)
  DURING bfcm2024
  COMPARE TO previous_year_match_day_of_week
  WITH PERCENT_CHANGE
  ```

### WITH (optional)

* **Purpose:** Adds calculated rollups and derived series to the result without changing the base aggregation. Combine modifiers with commas in any order; each modifier may appear at most once.
* **Syntax:** `WITH <modifier> [, <modifier>]*`
* **Modifiers:**
  * `TOTALS` — adds a grand total row across every group.
  * `GROUP_TOTALS` — adds subtotal rows when a query has both GROUP BY and SEGMENT BY (or TIMESERIES). One subtotal is emitted per primary group.
  * `PERCENT_CHANGE` — adds a percent-change column. Requires COMPARE TO.
  * `CUMULATIVE_VALUES` — adds a running total series. Only valid for additive expressions (SUM, COUNT, and add/subtract combinations of them). Non-additive expressions such as AVG, MIN, MAX, COUNT\_DISTINCT, and ratios are rejected.
* **Examples:**

  ```sql theme={null}
  FROM products
  SHOW SUM(total_sales)
  GROUP BY product_id
  SINCE -30d
  WITH TOTALS
  ```

  ```sql theme={null}
  FROM products
  SHOW SUM(total_sales)
  GROUP BY geo_country
  SEGMENT BY marketing_source
  SINCE -30d
  WITH TOTALS, GROUP_TOTALS
  ```

  ```sql theme={null}
  FROM products
  SHOW SUM(total_sales)
  SINCE this_month
  TIMESERIES day
  WITH CUMULATIVE_VALUES
  ```

### VISUALIZE (optional)

* **Purpose:** Specifies how to render the query results graphically.
* **Syntax:** `VISUALIZE <target> [TYPE <type>] [BY <field>] [SERIES <field>] [BUCKETS <n>, <n>, ...] [LIMIT <n>] [MAX <number>]`
* **Target:** The metric or expression to visualize (e.g., `SUM(total_sales)` or a field name).
* **Options:** Each option may appear at most once and they can be written in any order.
  * `TYPE <type>` — chart type. One of `table`, `bar`, `line`, `donut`, `horizontal_bar`, `stacked_bar`, `area`, `card`, `geo_bar`. Use `geo_bar` for ranked geographic distributions; country codes in `geo_country` are rendered as full country names, and bars are sorted in descending order of the metric.
  * `BY <field>` — primary axis or grouping field for the chart (for example, `BY position`).
  * `SERIES <field>` — splits the data into separate series by the given field (for example, `SERIES geo_country`).
  * `BUCKETS <n>, <n>, ...` — numeric bucket boundaries for histogram-style charts.
  * `LIMIT <n>` — caps the number of categories or series rendered.
  * `MAX <number>` — caps the number of data points displayed.
* **Examples:**
  ```sql theme={null}
  VISUALIZE SUM(total_sales)
  VISUALIZE SUM(total_sales) TYPE bar
  VISUALIZE SUM(total_sales) TYPE line MAX 30
  VISUALIZE COUNT_DISTINCT(view_sessions) TYPE donut MAX 10
  VISUALIZE SUM(total_sales) TYPE bar BY product_title SERIES geo_country LIMIT 20
  VISUALIZE AVG(price) TYPE bar BUCKETS 10, 25, 50, 100
  VISUALIZE searches TYPE geo_bar BY geo_country LIMIT 12
  ```

The VISUALIZE clause is particularly useful when combined with TIMESERIES for creating time-series charts, or with GROUP BY for creating bar charts and donut charts showing distribution across dimensions.

Identifiers and paths

* **Identifiers:** letters, digits, underscores; start with a letter or underscore.
* **Paths:** dot notation (for example, product\_attributes.color).

Literals

* **Strings:** 'value' or "value"
* **Numbers:** 10, 10.5
* **Booleans:** TRUE, FALSE

## Arithmetic expressions

LayersQL supports arithmetic operations in both SHOW and WHERE clauses, enabling you to create calculated metrics and complex filtering conditions.

### Supported operators

* **Addition:** `+`
* **Subtraction:** `-`
* **Multiplication:** `*`
* **Division:** `/`
* **Modulo:** `%`
* **Unary minus:** `-` (negation)

### Operator precedence

Arithmetic expressions follow standard mathematical precedence: multiplication, division, and modulo are evaluated before addition and subtraction. Use parentheses to override the default precedence.

### Examples

Combine multiple aggregations:

```sql theme={null}
FROM products
SHOW SUM(total_sales) + SUM(quantity_purchased) AS combined_metric
GROUP BY product_id
SINCE -7d
```

Calculate a weighted score:

```sql theme={null}
FROM products
SHOW SUM(total_sales) * 100 AS sales_cents
GROUP BY product_id
SINCE -7d
```

Use parentheses to control precedence:

```sql theme={null}
FROM products
SHOW (SUM(total_sales) + SUM(quantity_purchased)) * 2 AS doubled_total
GROUP BY product_id
SINCE -7d
```

Calculate profit per view:

```sql theme={null}
FROM products
SHOW (SUM(total_sales) - SUM(quantity_purchased)) / SUM(view_sessions) AS profit_per_view
GROUP BY product_id
SINCE -7d
```

Arithmetic in WHERE clause:

```sql theme={null}
FROM products
SHOW SUM(total_sales)
GROUP BY product_id
WHERE product_id * 2 > 100
SINCE -7d
```

### Aliasing arithmetic expressions

Use the `AS` keyword to give your calculated expressions meaningful names:

```sql theme={null}
FROM products
SHOW SUM(total_sales) + SUM(quantity_purchased) AS total_revenue,
     SUM(total_sales) - SUM(quantity_purchased) AS profit
GROUP BY product_id
SINCE -7d
```

## Examples

Group by attributes with a rolling window

```sql theme={null}
FROM products
SHOW SUM(total_sales)
GROUP BY product_id, product_attributes.metafields.custom.style_code
SINCE past_30_days
```

Filter by geography and marketing channel

```sql theme={null}
FROM products
SHOW SUM(total_sales)
GROUP BY product_id
WHERE geo_country = 'US' AND marketing_source = 'facebook'
SINCE -7d
```

Calculate weighted metrics with segmentation

```sql theme={null}
FROM products
SHOW SUM(total_sales) * 0.7 + COUNT_DISTINCT(view_sessions) * 0.3 AS weighted_score
GROUP BY product_id
SINCE -30d
SEGMENT BY geo_country
```