SQL Analytics

SQL Analytics allows you to carry out advanced manipulation of data using a SQL query before you visualize it.

The analytics editor provides full featured SQL support and is powered by DuckDB—an in-process database management system designed for efficient analytical query processing.

What you can do with SQL Analytics

SQL Analytics lets you transform, aggregate, and combine data before visualizing it. Common use cases include:

• Calculate the average time difference between when a ticket is created and resolved using SQL, then visualize the Mean Time To Resolution (MTTR).
• Query AWS CloudWatch data to find the 95th percentile response time of a microservice, and display it as a Service Level Indicator (SLI).
• Combine entry points from different sources, such as Azure Cost data and Azure Monitor data, to analyze the potential cost savings of turning off unused virtual machines.

Where to access the analytics editor

In the tile editor, there are two ways to access the SQL Analytics editor:

• Select SQL from the Filter | Group | Sort tab to open the SQL editor, where you can use SQL to shape and aggregate the current dataset.
  

• Enable the Multiple datasets toggle at the top of the screen. This switches from the standard tile editor to the Multiple datasets editor, where you can use SQL to shape and aggregate data from multiple datasets.
  
  Note

  If you exit out of the SQL Analytics mode, reverting to using the default tile editor for a tile, then you lose your SQL queries and datasets, as only the first dataset is saved.

Using the SQL editor

This uses standard Structured Query Language (SQL) and is powered by DuckDB.

By default, the Query tab in the SQL section of the SQL Analytics Editor contains a query that returns everything from the first dataset. Edit this query to return and manipulate your data as needed.

Select * FROM tickets

Query editor buttons

Use the following buttons to interact with the Enter your query window.

IntelliSense

The SQL query editor comes packed with IntelliSense, designed to streamline query creation. Each keystroke triggers the assistant and and provides:

• Context-aware auto-complete: The editor suggests relevant SQL syntax, table names, and column fields based on your current input, reducing the chance of errors and speeding up query writing.
• Real-time error detection: Common mistakes such as syntax errors or missing keywords are flagged as you type, helping you correct issues early.

Querying datasets

How you reference data in your SQL query depends on where you're writing it:

• Filter | Group | Sort > SQL: The query operates on the tile's current dataset, which is always referenced as dataset. For example:
  Copy

  SELECT
      priority,
      COUNT(*) AS TicketCount
  FROM dataset
  GROUP BY priority
  ORDER BY TicketCount DESC;

• Multiple datasets mode: Each configured dataset is exposed as a table. By default these are named dataset1, dataset2, and so on (although you can rename them to something more meaningful). For example:
  Copy

  SELECT
      priority,
      COUNT(*) AS TicketCount
  FROM dataset1
  GROUP BY priority
  ORDER BY TicketCount DESC;

Both queries return the number of tickets for each priority level. The difference is the dataset name you reference.

In Filter | Group | Sort > SQL, use dataset, which represents the current dataset. In Multiple datasets mode, reference the dataset you want to query (dataset1, dataset2, etc.).

Multiple datasets mode

After enabling the Multiple datasets toggle, the tile editor view updates to facilitate the use of multiple datasets. From the left-hand pane, you can add / edit / delete each dataset you want to use, and these display below the visualization alongside the SQL output.

If you had already selected a data stream and objects in the tile editor before enabling the toggle, then it is displayed as dataset1 and the SQL > Query tab immediately opens.

Renaming datasets

Datasets can be renamed by clicking the tab of the dataset to enable editing – hit Enter to save the change, hit Escape to cancel. For example, if you are looking at Zendesk tickets you might rename dataset1 to tickets.

Dataset buttons

Use the following buttons to create new, copy existing and delete data sets.

Querying multiple datasets

Multiple datasets mode lets you leverage SQL to query data from more than one dataset at a time, and even combine datasets by using a JOIN, etc.

For example, the following query compares the number of new and solved tickets per day by combining data from two different datasets.

WITH created AS (
                 SELECT `count` AS NewTickets,
                 created_at_byDay AS TDay
                 FROM dataset1
                 ORDER BY TDay),

     solved AS (
                 SELECT `count` AS SolvedTickets,
                 solved_at_byDay AS TDay
                 FROM dataset2
                 ORDER BY TDay)

SELECT TDay, 
       COALESCE(NewTickets, 0) AS Created, 
       COALESCE(SolvedTickets,0) AS Solved
FROM (
      SELECT * from created
      OUTER JOIN solved on solved.TDay = created.TDay
     )
ORDER BY TDay

Was this article helpful?