Skip to main content

SQL query

Last updated 06/25/2026

In addition to the analysis models described earlier, you can use SQL statements to perform advanced analysis that is hard to achieve with analysis models, and run custom queries on the data of all projects in the current cluster. If you find something valuable in the SQL IDE, you can also save it as a report and display it in a dashboard like other model reports.

Write a query in the editor​

The AE system uses the Trino query engine, so you can write queries in standard SQL. Here is the simplest example:

SELECT
"$part_date"
, count(DISTINCT "#user_id")
FROM
ta.v_event_1
WHERE ("$part_date" BETWEEN '2023-01-01' AND '2023-01-07') AND ("$part_event" = 'login')
GROUP BY "$part_date"
ORDER BY "$part_date" ASC

Note the following when you write queries:

  • Enclose field names in double quotation marks " ". You can also omit them, but if the field name you query contains special characters (such as $ or #), you must use double quotation marks
  • Strings must be enclosed in single quotation marks ' '
  • You can use SELECT statements and WITH clauses

While writing a query, you can refer to the information in Table structure and copy table names or the field names in a table. You can also use Table parsing to automatically insert a query that includes all fields into the editor.

You can also save the content of the current editor as a bookmark so that unfinished work isn't lost. You can find saved bookmarks in Bookmark.

If you need to use dynamic time in a query, or want other members to be able to dynamically adjust parts of the query, add dynamic parameters.

Unlike other analysis models, the SQL IDE lets you use data from all projects that you have permissions for. For example, you can query the DAU of project 1 and project 2 at the same time:

SELECT
  a."$part_date"
, "Project_1_DAU"
, "Project_2_DAU"
FROM
  ((
   SELECT
     "$part_date"
   , count(DISTINCT "#user_id") "Project_1_DAU"
   FROM
     ta.v_event_1
   WHERE (("$part_date" BETWEEN '2023-01-01' AND '2023-01-07') AND ("$part_event" = 'login'))
   GROUP BY "$part_date"
)  a
INNER JOIN (
   SELECT
     "$part_date"
   , count(DISTINCT "#user_id") "Project_2_DAU"
   FROM
     ta.v_event_2
   WHERE (("$part_date" BETWEEN '2023-01-01' AND '2023-01-07') AND ("$part_event" = 'login'))
   GROUP BY "$part_date"
)  b ON (a."$part_date" = b."$part_date"))
ORDER BY a."$part_date" ASC

In addition to the event table and user table, you can also use the following data in queries:

  • Tag and cohort table: ta.user_result_cluster_{project_id}
  • History tag table: ta.history_tag_{project_id}
  • Historical exchange rate table: ta_dim.ta_exchange
  • Dimension table
  • Temporary table

If you need to use the following data in queries, contact your customer success manager for details:

  • User daily snapshot table: at midnight in the server time zone every day, the AE system backs up the user state at that moment. By default, user table snapshots from the last 180 days are kept and can be used in the SQL IDE
  • Import custom tables with the secondary development tool
  • Connect external data sources with Trino Connectors

View query results and save them as a report​

After you click Calculate, you can view the results of the SQL statement. Up to 20000 rows of detailed data are displayed. To view all the data, download the CSV file, which supports up to 1 million rows. You can also save the query results as a Temporary table, which can be used in later queries.

In addition to viewing the data details directly, you can display data with chart types such as line charts and pie charts in the visualization module. You can also save the current query and visualization settings as a report and share it with other members.

As with other model reports, the data that different members can see in an SQL report is affected by data permissions. For example, a member who only has permission for the iOS channel sees results that contain only the behavior data of iOS channel users.

In addition to data permissions, you can also set event permissions for an SQL report. If a member doesn't have permission to use the selected events, the report doesn't display any data (Figure 1).

Note that the report is saved only in the current project, regardless of whether the query uses data from other projects. If the query contains tables that don't belong to the current project, you also need to confirm whether viewers must have permissions for all the projects involved in the query (Figure 2).

Because the queries of SQL reports are often complex, you can set a display cache (Figure 3). After calculation completes, the results are cached, and the next time the same report is queried, the cached results are read directly without recalculation, which saves cluster resources.

Scheduled dashboard updates apply to all SQL reports: regardless of the settings, the reports are calculated during scheduled dashboard updates. You can set the cache of non-T+1 reports to 24 hours and turn on scheduled dashboard updates, so the reports only need to be calculated once a day.

If a query takes more than 300 seconds to execute, the report must use a dashboard cache, and you can't refresh the report manually (scheduled dashboard refreshes still apply). You can optimize the query or narrow the query's data range to speed it up.

Was this page helpful?
On this page