> For the complete documentation index, see [llms.txt](https://userflux.gitbook.io/userflux-docs/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://userflux.gitbook.io/userflux-docs/feature-guides/analytics/insights/custom-sql.md).

# 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)

```sql
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.