Skip to main content
Clause ordering and optionality
  • Required: FROM · SHOW · GROUP BY · SINCE
  • Optional: WHERE · SEGMENT BY
  • Order: FROM → SHOW → GROUP BY → [WHERE] → SINCE → [SEGMENT BY]
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 product_id
WHERE geo_country = 'US' AND marketing_source IS NOT NULL
SINCE this_month
SEGMENT BY geo_city

​
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.
  • Examples: 
    GROUP BY product_id
GROUP BY product_attributes.color, product_attributes.size

​
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 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

​
SEGMENT BY (optional)

  • Purpose: Adds an additional segmentation dimension applied to each group.
  • Examples: 
    SEGMENT BY geo_country
SEGMENT BY marketing_campaign, geo_country
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