ShopifyQL reference

ShopifyQL follows the syntax below. You can place the entire query on one line or on separate lines.

Keywords need to follow the syntax order, otherwise, errors occur.

This section describes the required keywords in ShopifyQL queries.

A ShopifyQL query must contain at least the FROM and SHOW keywords.

Keywords need to be in the following order:

• FROM
• SHOW | VISUALIZE
• GROUP BY
• WHERE
• (SINCE & UNTIL) | DURING
• COMPARE TO
• ORDER BY
• LIMIT

• FROM { table_name }
• FROM accepts one parameter, table_name, where table_name is a table.

• SHOW { column1 AS { alias } , column2 AS { alias } , ... }
• SHOW accepts any number of parameters, where each parameter is a column in a table or an expression.
  • Each parameter optionally accepts an alias using the AS keyword.

• AS { alias }
• AS accepts one parameter, which is an alias for a column name in a table, or an alias for the return value of an aggregate function. If an alias has a space in the name, then surround the alias with double quotes.
• AS can used with both the SHOW and VISUALIZE keywords.

• VISUALIZE { column1 AS { alias } , column2 AS { alias } , ... }
• VISUALIZE accepts any number of parameters, where each parameter is a column in a table or an expression.
  • Each parameter optionally accepts an alias using the AS keyword.
• VISUALIZE returns axis labels and data formatting information for creating data visualizations.
• Optionally accepts a visualization_type using the TYPE keyword.

The sales are depicted as a single line, with the x-axis labeled as month, and the y-axis as sum_net_sales.

• TYPE { visualization_type }
• TYPE accepts one parameter, visualization_type, where visualization_type is line or bar.
  • line returns a line graph.
  • bar returns a bar graph.
• TYPE is an optional keyword. If TYPE isn't used, then ShopifyQL returns a visualization that's appropriate for the submitted query.

• GROUP BY { dimension | date }
• GROUP BY accepts any number of parameters, where each is a dimension or time dimension. A dimension is a field in a table.
  • Each parameter optionally accepts an alias_name using the AS keyword.
• If there isn't a return value for the specified dimension, expression, or date, then the dimension, expression, or date isn't returned. If you want to return the dimension, expression, or date when there's no data present, then you can use the ALL modifier.

• GROUP BY { dimension | date } ALL
• ALL is an optional GROUP BY modifier which can only be used with a date.
• The ALL modifier fills in zeros for any date where data isn't present.
• The ALL modifier enables you to retrieve continuous date periods. This allows you to get continuous date periods without having to perform joins to date lookup tables.
• When using the ALL modifier SINCE or DURING must also be specified.

• WHERE { condition }
• WHERE accepts one parameter, condition, where condition is an expression that consists of one or more comparison operators and logical operators.
• WHERE filters the results of an entire query based on the specified condition.

• SINCE { date_offset }

  • SINCE accepts one parameter, date_offset, where date_offset is a date range operator. The date range operator that's used for start_date_offset sets a starting date in a date range. This date is included in the range.

• UNTIL { date_offset }

  • UNTIL accepts one parameter, date_offset, where date_offset is a date range operator. The date range operator that's used for end_date_offset sets an ending date in a date range. This date is included in the range.
  • If UNTIL isn't used in a query, then today is used as the ending date.

• DURING { named_date_range }
• DURING accepts one parameter, named_date_range, where named_date_range is a named date range operator.
• DURING is an optional keyword that replaces SINCE and UNTIL statements.
• This keyword helps to filter the query results for known time periods such as a calendar year or a specific month, or to filter the query results for date ranges that have different dates every year, such as Black Friday Cyber Monday.

• COMPARE TO { named_date_range | relative_date_range }
• COMPARE TO is an optional keyword which is paired with SINCE and UNTIL or DURING.
  • When paired with SINCE and UNTIL COMPARE TO accepts a relative_date_range (see relative date range operators).
    • While using the relative_date_range previous_year the SINCE and UNTIL specified length of time must be a year or less.
  • When paired with DURING, COMPARE TO accepts either a named_date_range (see named date range operators) or a relative_date_range (see relative date range operators).
    • While using a named_date_range it must match the length of the parameter in DURING.
• This keyword allows you to compare data across the date_offset in SINCE and UNTIL or DURING to the COMPARE TO named_date_range or relative_date_range.

• ORDER BY { column } ASC | DESC
• ORDER BY can accept a list of one or more parameters. Each parameter consists of one or two elements, column, and an optional ASC or DESC. column is a column in a table, and ASC or DESC change the behavior of the returned results.
  • The ASC parameter is used after the column parameter, and indicates that the returned query results are sorted in an ascending order.
  • The DESC parameter is used after the column parameter, and indicates that the returned query results are sorted in a descending order.
  • If the ASC or DESC parameters aren't used, then the default sorting order is ascending.
• If ORDER BY has multiple columns, then the results are first sorted by the first column, and then sorted by the second column, and so on.
• The columns used in the ORDER BY parameters must be present in either the SHOW, BY or OVER parameter list.

• LIMIT { number }
• LIMIT accepts one parameter, number, where number is a number that represents how many rows that you want the query to return.
• LIMIT enables you to understand the data in each column without returning all of the data in the table. This is useful for larger tables where queries can take longer to return values. You can also use LIMIT with ORDER BY to create lists of the top or bottom-most X. For example, the top five products.

The time functions are abstracted so you don’t have to keep track of which date field corresponds to the grain of the available datasets. The following date fields are available as time dimensions in ShopifyQL:

You can use the following date range operators as well as a named date range operator in SINCE and UNTIL statements.

SINCE and UNTIL, DURING, and COMPARE TO accept any of the following named date range date operators below:

Relative operators return the same length of time as the base date range, shifted back by the specified period. COMPARE TO accepts the following relative date range operators:

The following table demonstrates how previous_period and previous_year work with base periods specified by DURING:

The following table demonstrates how previous_period and previous_year work with base periods specified by SINCE and UNTIL:

You can use the following comparison operators in WHERE statements.

You can use one or more of the following logical operators in WHERE statements.

You can use the following mathematical operators on numerical data.

You can use the following functions to aggregate columns, and group columns by dimensions.

The sum(), min(), max(), and avg() functions can only be used with numerical values, while count() can be used to count different instances of dimensional attributes. You can't use aggregated fields as arguments in the functions.

The following query returns the aggregated sum of net sales and ordered quantity as a result of the sum() function, and the count of cities as a result of the count() function. These metrics are broken down by region for all regions in the United States in 2021.

You can perfrom conditional counting by applying count() to a condition. The following query counts the number of inventory items that were out of stock at the end of the day, during the last month, at each location.

Single line comments start with -- and end at the end of the line.

Multi-line comments start with /* and end with */.

You can use comments to explain sections of ShopifyQL statements, or to prevent the execution of a ShopifyQL statement. Any text within a comment will be ignored during execution time.