Custom SQL

Overview

Custom SQL unlocks full querying power for advanced analyses that exceed the point‑and‑click builders—complex joins, bespoke metrics, detailed exports, or data validation.

---

Common Use Cases

• Top N events or users over a custom window

• DAU / MAU or retention calculations with window functions

• Joining profiles to events for LTV or cohort reports

• Exporting a CSV list (e.g. high‑value customers) for marketing

---

Interface Walkthrough

Element	Description
SQL Editor	Write multi‑line queries; autocomplete for tables/columns.
Run Query	Execute and preview results instantly.
Save Query	Store as a reusable insight.
Variables	Parameterise dates, IDs, event names for easy reuse.
Changelog	View and revert past edits.

---

Quick Example (Daily Session Duration)

SELECT 
    toDate(session_start_time) AS date,
    median(session_duration / 60) AS average_session_duration_minutes
FROM (
    SELECT 
        toDate(timestamp) as date,
        sessionId,
        min(timestamp) AS session_start_time,
        toUnixTimestamp(max(timestamp)) - toUnixTimestamp(min(timestamp)) AS session_duration
    FROM eventsTable
    WHERE timestamp >= dateAdd(day, -30, today())
    GROUP BY date, sessionId
    HAVING session_duration > 0
)
GROUP BY date
ORDER BY date

1. Paste into editor → Run Query.

2. Table shows days and median session duration.

3. Save as “Daily Session Duration”; add a variable for the day interval if you want quick toggles.