The SQL notebook is the right tool when a question needs joins, reusable calculations, window functions, deduplication, or a result shape that the collection visualizer cannot express.
This guide covers the notebook workflow. For ClickHouse syntax and query examples, start with Run SQL queries and the SQL cookbook.
Open a notebook#
Open Queries and create a new query, or select an existing saved query.
The notebook has four working areas:
- the SQL editor
- Table results
- Visualization settings and preview
- Settings for the saved query
GraphJSON runs a new query automatically when the page first loads. Select Run after editing, or use Command+Enter on macOS and Control+Enter on Windows or Linux.
Write a result contract#
Design the result table before choosing a graph. Each result column should have one clear role:
- timestamp or x-axis
- numeric value
- category label
- series split
- supporting table field
For example:
SELECT
toUnixTimestamp(toStartOfDay(toDateTime(timestamp))) AS day,
JSONExtractString(json, 'plan') AS plan,
uniqExact(JSONExtractString(json, 'account_id')) AS active_accounts
FROM product_events
WHERE
JSONExtractString(json, 'event') = 'feature_used'
AND timestamp >= now() - INTERVAL 30 DAY
GROUP BY day, plan
ORDER BY day, plan
This result makes the intended mapping explicit:
x-axis: day
value: active_accounts
split: plan
Use Unix seconds for a time-series x-axis. Give calculated columns stable aliases; a visualization saved against active_accounts will not understand an unexplained later rename to accounts.
Run and read the table#
Select Run and inspect Table before configuring a chart.
Check:
- column names are unique and meaningful
- numeric values are actually numeric
- time-series timestamps are Unix seconds
- category columns contain a manageable number of values
- nulls and empty strings have an intentional meaning
- the row count and ordering match the question
GraphJSON wraps SQL results with an outer limit of 2,000 rows. A result that needs more than 2,000 rows should usually be aggregated further, filtered to a narrower period, or exported through a purpose-built data path.
Mutation statements are blocked. The notebook is for analytical reads, not updates to stored events.
Save a query#
Select Save. For a new query, provide:
- a name
- a description
Use a name that identifies the metric and grain:
weekly_active_accounts_by_plan
checkout_failure_rate_hourly
latest_subscription_state_by_account
The description should make the result reproducible:
Unique accounts with at least one feature_used event per UTC week.
Excludes internal accounts and test environments. Owned by Product Analytics.
For an existing query, Save persists:
- SQL text
- graph type
- selected columns
- visualization customizations
Saving does not create a snapshot of the result. The query runs against current retained data when it is opened or embedded.
Format SQL#
Select Format to normalize indentation and clause layout. Format before review so changes to logic are visible and version-like comparisons are easier.
Formatting does not validate field names or change query semantics intentionally. Run the formatted query and compare the result before considering the edit complete.
Choose a visualization#
Open Visualization after the table result is correct.
| Graph type | Required mapping | Result shape |
|---|---|---|
| Single Line | timestamp column, value column | one row per time bucket |
| Multi Line | timestamp, value, split | one row per time bucket and series |
| Stacked Line | timestamp, value, split | same as Multi Line; values shown as composition |
| Single Value | value column | one or more numeric rows; design the query to return the intended KPI |
| Pie Chart | label, value | one row per category |
| Bar Chart | label, value | one row per category |
| Funnel | label, value | one ordered row per funnel step |
| Table | no required chart mapping | rows and columns are rendered directly |
GraphJSON checks that the selected value column can be parsed as numeric. A string-formatted currency such as "$49.00" is display text, not a value column. Return 4900 or 49.00 and add formatting in the visualization.
Single line#
Return exactly one row per timestamp:
SELECT
toUnixTimestamp(toStartOfDay(toDateTime(timestamp))) AS day,
count() AS signups
FROM product_events
WHERE JSONExtractString(json, 'event') = 'signup_completed'
GROUP BY day
ORDER BY day
Map day to the timestamp column and signups to the value column.
Multi-line and stacked-line#
Return one row for each timestamp and split value. Keep the number of split values small enough to read.
If a query can return thousands of customer IDs, filter to a deliberate cohort or aggregate to a stable category such as plan or region.
Bar, pie, and funnel#
Return a label and numeric value:
SELECT
JSONExtractString(json, 'plan') AS label,
uniqExact(JSONExtractString(json, 'account_id')) AS value
FROM product_events
WHERE JSONExtractString(json, 'event') = 'signup_completed'
GROUP BY label
ORDER BY value DESC
For a funnel, add an explicit ordering column or build the rows with an ordered UNION ALL. Alphabetical order is rarely the intended funnel order.
Table#
Use a table when the exact rows matter more than a visual trend. Keep personal data out of the result unless the viewer is authorized and the field is necessary.
Customize and export#
Use Customize to set the title, colors, number formatting, axis visibility, and other options supported by the selected graph type.
Then use Export to:
- save the visualization to a dashboard
- generate a static iframe
- generate a visualization API request
- retrieve the result through the Data API
- download supported table output
The exported configuration includes the SQL text and column mappings. Keep generated embed URLs private when the result contains non-public data.
See Visualization reference for the full chart matrix and Export, delete, and move your data for data-handling guidance.
Rename or delete a saved query#
Open Settings to change the name or description.
Before renaming, check documentation and recurring review notes that refer to the old name. The query URL changes after a rename.
Before deleting:
- identify dashboards and embeds generated from the query
- record the metric definition elsewhere if it remains authoritative
- confirm no recurring workflow depends on reopening the notebook
- delete the query
Deleting a saved query does not delete event data. Previously generated embeds may continue to represent their exported SQL configuration; do not assume query deletion revokes every distributed URL.
Long-running and cancelled queries#
GraphJSON starts SQL work as an asynchronous job and polls for completion. The notebook waits up to 15 minutes.
Starting another run makes the newer run authoritative in the current page. Leaving the page or cancelling the browser request stops waiting for that result; it is not a transaction rollback.
If a query is still running after 15 minutes:
- narrow the time range
- filter before joining or grouping
- avoid repeatedly extracting the same JSON field
- reduce high-cardinality splits
- pre-aggregate each collection before joining
- use SQL performance and troubleshooting
A review checklist#
Before putting a saved query on a shared dashboard:
- The description defines the metric, grain, time zone, and exclusions.
- Identifiers and money use the correct types.
- The table result was reviewed before the chart.
- Time-series rows use Unix seconds and deterministic ordering.
- The result stays below the 2,000-row limit.
- High-cardinality splits are bounded.
- The title makes sense without the notebook open.
- The owner and definition-change process are recorded.