Skip to Content

SQL and Python

Build a metric with SQL

Write a SELECT query in the metric builder, run it, and turn the result into a chart.

Before you start

  • Your role has the SQL View and Edit permissions. The default Editor and Developer roles have them.
  • You are in the metric builder. See Create a metric.

Steps

  1. In the metric builder, select the SQL tab above the schema list.

    Metric builder sidebar with the SQL tab highlighted next to Schema, above the databrain_dev1 schema.
    The SQL tab sits next to Schema, above the table list.
  2. Keep Query language on SQL.

  3. Write a SELECT or WITH query in the Query editor. Use the schema list on the left to find tables and columns.

  4. Select Run query, or press Cmd+Enter (Ctrl+Enter on Windows).

    SQL editor with a SELECT query grouping total sale value by product category, the Run query button highlighted, and five result rows.
    Run query shows the rows under the editor.
  5. Check the result in the output. Choose a Row limit to see the top rows only.

  6. Select Use results to put the query in the metric, then pick the chart and save the metric.

    Query result header with the Use results button highlighted next to Download as CSV.
    Use results puts the query in the metric.

Select Format query to tidy the SQL. Select Exit SQL mode to go back to the drag-and-drop builder.

Filter by client

For a customer-facing metric, add the client filter, or every client sees every row:

SELECT "o"."status" AS "status", COUNT(*) AS "orders"
FROM "public"."orders" AS "o"
WHERE "o"."client_id" = 'client_id_variable'
GROUP BY "o"."status"

Databrain replaces client_id_variable with the client ID from the guest token. With database tenancy, use {{DATABASE_NAME}} in the table name instead, for example FROM {{DATABASE_NAME}}.public.orders. See SQL guidelines.

If something goes wrong

  • please add valid select query. Only one SELECT or WITH query is allowed. Remove INSERT, UPDATE, DELETE, CREATE and other statements that change data.
  • There’s no column called ... Check the column name and its quotes.
  • ... has to be grouped or aggregated. Add the column to GROUP BY, or wrap it in SUM, COUNT or another aggregate.
  • Databrain does not run queries on system tables. Query your own tables, not information_schema or pg_catalog.
  • Can’t preview a query that still contains ... Swap the variable for a real value to test the query, then put the variable back before you save.

On this page