With SQL databases, we can use an existing question as the basis for a new query, or as a common table expression (CTE). For example, let’s say we have a lot of data spread across a number of tables, but our users are most interested in a subset of that data. We can perform a complicated query once to return those results, which people can refer to in their queries just like they would with any other table.
Here’s how it works. First, create and save a question that returns the result set you’d like to make available for people to query. Using the sample dataset included with Metabase as an example, let’s say we want to provide a result set (a “table”) that only has orders from 2019, and only includes orders for products in the Gizmo category.
We could create this question using the notebook editor, like so:
.
Alternatively, we could create that question using SQL:
SELECT *
FROM orders AS o
INNER JOIN products AS p
ON o.product_id = p.id
WHERE p.category = 'Gizmo'
AND o.created_at BETWEEN '2019-01-01' AND '2019-12-31'
We’ll save that question as “Gizmo orders in 2019”.
Now let’s refer to “Gizmo orders in 2019” in a new query. To keep it simple, let’s say we just want to count all of those Gizmo orders from 2019. We can use the #
symbol to refer to a saved question in a query.
If we type out:
SELECT count(*)
FROM {{#
Metabase will slide out a sidebar where we can select a question to reference. We’ll search for our “Gizmo orders in 2019” question:
We’ll select that question, and Metabase will update our code with the question’s ID, 5
:
SELECT count(*)
FROM {{#5}}
This query returns the number of rows in our saved question.
The same syntax can be used in Common Table Expressions (CTEs) (with SQL databases that support CTEs):
WITH 2019_gizmo_orders AS {{#5}}
SELECT count(*)
FROM 2019_gizmo_orders
When this query is run, the {{#5}}
tag will be substituted with the SQL query of the referenced question, surrounded by parentheses. So it’ll look like this under the hood:
WITH 2019_gizmo_orders AS (SELECT *
FROM orders AS o
INNER JOIN products AS p
ON o.product_id = p.id
WHERE p.category = 'Gizmo'
AND o.created_at BETWEEN '2019-01-01' AND '2019-12-31')
SELECT count(*)
FROM 2019_gizmo_orders
/question/
. E.g., for https://metabase.example.com/question/12345
, the question’s ID would be 12345
.For other ways to standardize analytics, check out:
Learn about how to easily and quickly see automatic explorations of your data with Metabase’s powerful X-ray feature.