Skip to main content
Clause ordering and optionality
  • Required: FROM · SHOW · SINCE
  • Optional: GROUP BY · WHERE · UNTIL · TIMESERIES · SEGMENT BY · VISUALIZE
  • Canonical order: FROM → SHOW → (GROUP BY / WHERE / SEGMENT BY) → SINCE → [UNTIL] → [TIMESERIES] → [SEGMENT BY] → [VISUALIZE]
The parser accepts flexible ordering of WHERE, GROUP BY, and SEGMENT BY clauses before SINCE. SEGMENT BY can also appear after SINCE or TIMESERIES for backwards compatibility. Minimal example 
FROM products
SHOW SUM(total_sales)
GROUP BY product_id
SINCE -7d
Full example 
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
VISUALIZE SUM(total_sales) TYPE line MAX 30

​
Clauses

​
FROM

  • Purpose: Selects the dataset.
  • Example: 
    FROM products

​
SHOW

  • Purpose: Lists expressions to compute. Typically uses aggregation functions over metrics or fields.
  • Examples: 
    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: 
    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. 
    GROUP BY TOP 5 product_title
  • ONLY TOP N: Shows only the top N values, hiding the remainder entirely. 
    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. 
    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: 
    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
    • Common rolling windows: past_7_days, past_14_days, past_30_days, past_60_days, past_90_days, past_180_days, past_365_days
    • ISO: 2025-03-15 or 2025-03-15T10:30:00Z (timezone offsets supported)
  • Examples: 
    SINCE -7d
SINCE this_month
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: 
    SINCE -30d UNTIL -7d
SINCE last_month UNTIL this_month
SINCE 2024-01-01 UNTIL 2024-12-31
The UNTIL clause is currently parsed but not yet applied during metric execution. End date filtering will be supported in a future release.

​
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: 
    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: 
    SEGMENT BY geo_country
SEGMENT BY marketing_campaign, geo_country

​
VISUALIZE (optional)

  • Purpose: Specifies how to render the query results graphically.
  • Syntax: VISUALIZE <target> [TYPE <type>] [MAX <number>]
  • Target: The metric or expression to visualize (e.g., SUM(total_sales) or a field name).
  • Types: table, bar, line, donut, horizontal_bar, stacked_bar, area, card
  • MAX: Limits the number of data points displayed.
  • Examples: 
    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
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: 
FROM products
SHOW SUM(total_sales) + SUM(quantity_purchased) AS combined_metric
GROUP BY product_id
SINCE -7d
Calculate a weighted score: 
FROM products
SHOW SUM(total_sales) * 100 AS sales_cents
GROUP BY product_id
SINCE -7d
Use parentheses to control precedence: 
FROM products
SHOW (SUM(total_sales) + SUM(quantity_purchased)) * 2 AS doubled_total
GROUP BY product_id
SINCE -7d
Calculate profit per view: 
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: 
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: 
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 
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 
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 
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