Monitor

SQL best practices

View as Markdown

Boards are built from widgets, and each widget is powered by a SQL query. The SQL dialect here is column-oriented and tuned for fast aggregations over large call datasets, so some of its functions and conventions differ from standard PostgreSQL or MySQL and may be new to you.

This page explains how to write and review the SQL behind a widget: where to write it, how to confirm the schema for your organization, which patterns to use, and how to generate a draft query with AI. For the surrounding UI flow, start at Boards and Create a widget.

The SQL editor

You write and run widget queries in the SQL editor. The following actions are available while editing:

ActionHow
RunClick Run or press ⌘/Ctrl+Enter
SchemaBrowse the live databases, tables, and column types for your organization
Generate with AIPress / or click Generate with AI
Insert filterUse the filter menu, or type {{filter_name}} directly
BeautifyFormat the SQL with standard keyword casing
CopyCopy the query to share or version it elsewhere

Browse the schema

Before writing a query, open Schema to see the databases, tables, and column types available to your organization. Table and column names vary between organizations, so confirm them here rather than assuming names from documentation or from an AI suggestion. Using the exact names from Schema is the most common way to avoid a query that fails to run or returns no rows.

Read-only access

The editor allows read-style queries only. The following statements are blocked and underlined in the editor, and will not run:

DELETE · DROP · TRUNCATE · ALTER · INSERT · UPDATE

Write your widgets with SELECT. Common table expressions (CTEs) and subqueries are supported as long as they only read data.

Write efficient queries

Queries are fastest when you filter early and aggregate in the database rather than returning raw rows to the widget. The following steps produce queries that stay fast and map cleanly onto a chart.

1

Confirm the schema

Open Schema and use the exact table and column names for your organization. Do not invent names from documentation or from an AI draft.

2

Filter early

Push time and entity filters into the WHERE clause, ideally through board filter tokens so the widget stays reusable:

1WHERE start_datetime >= {{start_date}}
2  AND start_datetime < {{end_date}}
3  AND agent_id = {{agent_id}}
3

Aggregate for charts

Return rollups rather than raw rows. Aggregating in the database keeps result sets small and charts responsive:

1SELECT
2  toDate(start_datetime) AS day,
3  count() AS calls,
4  countIf(connectivity_status = 'connected') AS connected
5FROM conversations
6WHERE start_datetime >= {{start_date}}
7GROUP BY day
8ORDER BY day
4

Match the result to the visualization

Shape the output to fit the widget type:

  • Number — one row with one numeric column
  • Bar / Line — a category or time value on the X axis, a measure on the Y axis
  • Table — an intentional set of columns, not SELECT *

Frequently used patterns include toDate(...) and toStartOfHour(...) for time bucketing, countIf(condition) and avgIf(column, condition) for conditional aggregation, and ORDER BY ... DESC LIMIT 20 to return only the top results.

Generate SQL with AI

If you are new to this SQL dialect, you can describe the question in plain language and let Generate with AI produce a draft query for you to review.

1

Open AI assist

Press / or click Generate with AI.

2

Describe the question

State the metric in plain language, for example: average handle time per agent for the last 7 days.

3

Generate the draft

While the draft is being written, the editor shows Writing your query…. You can click Stop to cancel.

4

Review, then accept

Toggle Diff to compare the current query with the AI suggestion. Refine the prompt and Regenerate, or click Accept to keep the query and run it.

Treat AI output as a starting point and confirm it before saving.

  • First drafts of aggregations and time series
  • Rewrites after you change the question
  • Helping teammates who are new to the SQL syntax
  • Table and column names against Schema
  • Filter tokens ({{filters}}) for reusable boards
  • Metric definitions (connected vs. attempted vs. engaged)
  • Read-only access — blocked statements will not run
  • Spot-checks against Agent Analytics when the metric overlaps

Before you save

Confirm each of the following before saving a widget query:

  1. The query is SELECT-only and contains no blocked statements.
  2. Time and entity filters use board filter tokens where you need control over operators.
  3. The result shape matches the widget type (Table, Number, Bar, or Line).
  4. Table and column names match the schema for your organization.
  5. Filter defaults are set so the board is not empty on first open. See Filters.
  6. The widget title describes the metric in plain language.

Next

Create a widget

Put your query on a board and choose a visualization.

Filters

Parameterize queries with {{tokens}} and set defaults.

Boards

Understand the board layout and how widgets fit together.

Agent Analytics

Cross-check widget metrics against prebuilt analytics.