:hedgehog: PostHog is the leading platform for building self-driving products. Our developer tools – AI observability, analytics, session replay, flags, experiments, error tracking, logs, and more – capture all the context agents need to diagnose problems, uncover opportunities, and ship fixes. Steer it all from Slack, web, desktop, or the MCP.
Python ab-testingai-analyticsanalyticscdpdata-warehouseexperimentsfeature-flagsjavascriptproduct-analyticspythonreactsession-replaysurveystypescriptweb-analytics
import { parseBucketStart } from '@/lib/dates'
import { asRecord, isRecord } from '@/lib/records'
import type { MetricChartKind, MetricInterval, MetricPoint } from '../types'
import type {
InsightPresentation,
InsightSeriesView,
RankedValueView,
RankingView,
SqlCell,
SqlColumnView,
SqlTableView,
} from '../views'
// A SQL insight is a `DataVisualizationNode` with a `HogQLQuery` source. Its result is rows of cells plus the column
// names and ClickHouse types. The rules below port `dataVisualizationLogic.ts` in
// `frontend/src/queries/nodes/DataVisualization/`, so the phone resolves the same display and axes as the web.
type JsonRecord = Record<string, unknown>
export type SqlPhoneDisplay = 'number' | 'table' | 'chart' | 'bars' | 'web'
export interface SqlResponseParts {
result?: unknown
columns?: unknown
types?: unknown
hasMore?: unknown
}
export interface SqlAxes {
x: SqlColumnView | null
y: SqlColumnView[]
}
// `Metric` stays on the web: it shows the latest, total or mean of the first y series by date, which is not ported.
const NUMBER_DISPLAYS: ReadonlySet<string> = new Set(['BoldNumber'])
const LINE_DISPLAYS: ReadonlySet<string> = new Set([
'ActionsLineGraph',
'ActionsLineGraphCumulative',
'ActionsAreaGraph',
])
const BAR_DISPLAYS: ReadonlySet<string> = new Set(['ActionsBar', 'ActionsStackedBar', 'ActionsUnstackedBar'])
// Displays that total one value per x value. `ActionsBar` joins them when its x axis is not a date.
const RANKING_DISPLAYS: ReadonlySet<string> = new Set([
'ActionsBarValue',
'ActionsPie',
'ActionsDonut',
'ActionsProportionBar',
])
const NUMERICAL_SCALARS: ReadonlySet<string> = new Set(['INTEGER', 'FLOAT', 'DECIMAL'])
const DATE_SCALARS: ReadonlySet<string> = new Set(['DATE', 'DATETIME'])
// A plain decimal number. `Number()` alone would also read hex, binary and "Infinity" in a text column.
const NUMERIC_TEXT = /^\s*[-+]?(?:\d+\.?\d*|\.\d+)(?:e[-+]?\d+)?\s*$/i
const HOUR_MS = 3_600_000
const DAY_MS = 24 * HOUR_MS
/**
* A cell as a number, or undefined, which a chart draws as a gap. The server's JSON writes a ClickHouse Decimal as a
* string, and a wide integer can arrive as one too, so a numeric string counts.
*/
export function sqlCellNumber(cell: unknown): number | undefined {
if (typeof cell === 'number') {
return Number.isFinite(cell) ? cell : undefined
}
if (typeof cell === 'string' && NUMERIC_TEXT.test(cell)) {
const parsed = Number(cell)
return Number.isFinite(parsed) ? parsed : undefined
}
return undefined
}
function sourceOf(query: unknown): JsonRecord | undefined {
return asRecord(asRecord(query)?.source)
}
export function isSqlQuery(query: unknown): boolean {
return sourceOf(query)?.kind === 'HogQLQuery'
}
/** The saved display. Without one a SQL insight is a table, as on the web; null is `Auto`, which the result decides. */
function savedDisplay(query: unknown): string | null {
const display = asRecord(query)?.display
if (display === 'Auto') {
return null
}
return typeof display === 'string' ? display : 'ActionsTable'
}
function chartSettingsOf(query: unknown): JsonRecord | undefined {
return asRecord(asRecord(query)?.chartSettings)
}
function isTransposed(query: unknown): boolean {
return asRecord(asRecord(query)?.tableSettings)?.transpose === true
}
function breakdownColumnOf(query: unknown): string | null {
const column = chartSettingsOf(query)?.seriesBreakdownColumn
return typeof column === 'string' && column !== '' ? column : null
}
const WRAPPER = /^(?:Nullable|LowCardinality)\((.*)\)$/
/** The ClickHouse type as the web's scalar name: `Nullable(DateTime64(6, 'UTC'))` is `DATETIME`. */
export function sqlScalar(type: string | null | undefined): string {
if (!type) {
return 'UNKNOWN'
}
let inner = type.trim()
for (let match = WRAPPER.exec(inner); match?.[1] !== undefined; match = WRAPPER.exec(inner)) {
inner = match[1].trim()
}
if (inner.startsWith('Array')) {
return 'ARRAY'
}
if (inner.startsWith('Tuple')) {
return 'TUPLE'
}
if (inner.startsWith('Int') || inner.startsWith('UInt')) {
return 'INTEGER'
}
if (inner.startsWith('Float')) {
return 'FLOAT'
}
if (inner.startsWith('Decimal')) {
return 'DECIMAL'
}
if (inner === 'Bool' || inner === 'Boolean') {
return 'BOOLEAN'
}
if (inner.startsWith('DateTime')) {
return 'DATETIME'
}
if (inner.startsWith('Date')) {
return 'DATE'
}
if (inner.includes('String')) {
return 'STRING'
}
return inner
}
function decimalPlacesOf(value: unknown): number | undefined {
return typeof value === 'number' && Number.isInteger(value) && value >= 0 && value <= 100 ? value : undefined
}
function textOf(value: unknown): string {
return typeof value === 'string' ? value : ''
}
/**
* A SQL column's `settings.formatting`, or null without one. A percent column is a fraction scaled by 100, as the
* web's SQL table, charts and heatmap all do, so 0.25 reads 25%.
*/
export function sqlPresentation(settings: unknown): InsightPresentation | null {
const formatting = asRecord(asRecord(settings)?.formatting)
if (!formatting) {
return null
}
const style = typeof formatting.style === 'string' ? formatting.style : null
return {
kind: 'sql',
style,
prefix: textOf(formatting.prefix),
suffix: textOf(formatting.suffix),
decimalPlaces: decimalPlacesOf(formatting.decimalPlaces),
...(style === 'percent' ? { scale: 100 } : {}),
}
}
function settingsFor(list: unknown, column: string): unknown {
if (!Array.isArray(list)) {
return undefined
}
return list.filter(isRecord).find((entry) => entry.column === column)?.settings
}
/**
* The result's columns with their scalar types. A cell format comes from `tableSettings.columns`, else from the
* matching `chartSettings.yAxis` entry, so a column without table settings reads as the chart shows it.
*/
export function sqlColumns(columns: unknown, types: unknown, query: unknown): SqlColumnView[] {
if (!Array.isArray(columns)) {
return []
}
const typeList = Array.isArray(types) ? types : []
const tableColumns = asRecord(asRecord(query)?.tableSettings)?.columns
const yAxis = chartSettingsOf(query)?.yAxis
return columns.map((column, index) => {
const name = typeof column === 'string' ? column : String(column)
const pair = typeList[index]
const type = Array.isArray(pair) && typeof pair[1] === 'string' ? pair[1] : null
const scalar = sqlScalar(type)
return {
name,
scalar,
isNumerical: NUMERICAL_SCALARS.has(scalar),
isDate: DATE_SCALARS.has(scalar),
format: sqlPresentation(settingsFor(tableColumns, name)) ?? sqlPresentation(settingsFor(yAxis, name)),
}
})
}
function cellOf(value: unknown): SqlCell {
if (value === null || value === undefined) {
return null
}
if (typeof value === 'string' || typeof value === 'number' || typeof value === 'boolean') {
return value
}
return JSON.stringify(value) ?? null
}
export function sqlTableView(query: unknown, response: SqlResponseParts): SqlTableView | null {
if (!isSqlQuery(query) || !Array.isArray(response.result)) {
return null
}
return {
columns: sqlColumns(response.columns, response.types, query),
rows: response.result.filter(Array.isArray).map((row: unknown[]) => row.map(cellOf)),
hasMore: response.hasMore === true,
}
}
export function autoSqlDisplay(columns: SqlColumnView[], rowCount: number): string {
const numerical = columns.filter((column) => column.isNumerical)
if (columns.some((column) => column.isDate) && numerical.length > 0 && rowCount > 1) {
return 'ActionsLineGraph'
}
if (columns.filter((column) => column.scalar === 'STRING').length >= 2 && numerical.length > 0) {
return 'TwoDimensionalHeatmap'
}
if (numerical.length === 1 && columns.length === 1) {
return 'BoldNumber'
}
return numerical.length > 0 ? 'ActionsBar' : 'ActionsTable'
}
/** The saved display, or the Auto display of the result. Null for Auto before a result exists. */
export function effectiveSqlDisplay(query: unknown, table: SqlTableView | null): string | null {
const saved = savedDisplay(query)
if (saved) {
return saved
}
return table ? autoSqlDisplay(table.columns, table.rows.length) : null
}
/**
* The x and y columns. Unset, x is the first date column, else the first column that is not a number, and y is every
* number column. As the web clears saved axes that no longer fit the result, a saved x column that is missing, or a
* saved y column that is missing or not a number, drops both back to the defaults. The x column and the breakdown
* column are never a y series.
*/
export function sqlAxes(query: unknown, columns: SqlColumnView[]): SqlAxes {
const settings = chartSettingsOf(query)
const byName = (name: unknown): SqlColumnView | undefined => columns.find((column) => column.name === name)
const savedXName = asRecord(settings?.xAxis)?.column
const savedX = typeof savedXName === 'string' ? byName(savedXName) : null
const savedYNames = Array.isArray(settings?.yAxis)
? settings.yAxis.filter(isRecord).map((entry) => entry.column)
: []
const savedY = savedYNames.map(byName).filter((column): column is SqlColumnView => column?.isNumerical === true)
const valid = savedX !== undefined && savedY.length === savedYNames.length
const x =
(valid ? savedX : null) ??
columns.find((column) => column.isDate) ??
columns.find((column) => !column.isNumerical) ??
null
const chosenY = valid && savedY.length > 0 ? savedY : columns.filter((column) => column.isNumerical)
const breakdown = breakdownColumnOf(query)
const y = chosenY.filter((column) => column.name !== x?.name && column.name !== breakdown)
return { x, y }
}
/**
* The format of a SQL insight's values, for its big number, chart and bar list. A big number uses the first result
* column's table settings, as HogQLBoldNumber does on the web. Any other display uses the first saved y axis entry's
* settings, as the web's tooltip and axis do, else the first y column's own format.
*/
export function sqlValuePresentation(query: unknown, columns: unknown, types: unknown): InsightPresentation {
const raw: InsightPresentation = { kind: 'sql', style: null, prefix: '', suffix: '' }
const names = Array.isArray(columns) ? columns : []
if (savedDisplay(query) === 'BoldNumber') {
const settings = asRecord(asRecord(query)?.tableSettings)?.columns
const entries = Array.isArray(settings) ? settings.filter(isRecord) : []
const entry =
entries.find((candidate) => candidate.column === names[0]) ??
(names.length === 0 && entries.length === 1 ? entries[0] : undefined)
return sqlPresentation(entry?.settings) ?? raw
}
const yAxis = chartSettingsOf(query)?.yAxis
const saved = sqlPresentation(Array.isArray(yAxis) ? yAxis.find(isRecord)?.settings : undefined)
if (saved) {
return saved
}
return sqlAxes(query, sqlColumns(columns, types, query)).y[0]?.format ?? raw
}
/**
* The phone's display from the saved query alone, so a tile keeps one display while it loads. Auto is a table until
* the result arrives, since the phone can draw any result as a table.
*/
export function sqlQueryDisplay(query: unknown): SqlPhoneDisplay {
if (isTransposed(query)) {
return 'web'
}
const display = savedDisplay(query)
if (display === null || display === 'ActionsTable') {
return 'table'
}
if (NUMBER_DISPLAYS.has(display)) {
return 'number'
}
if (LINE_DISPLAYS.has(display) || BAR_DISPLAYS.has(display)) {
return 'chart'
}
return RANKING_DISPLAYS.has(display) ? 'bars' : 'web'
}
/**
* The phone's display once the result is known. A chart over time needs a date x axis; `ActionsBar` over any other x
* axis ranks its values as bars. A chart the phone cannot build from the columns opens on the web.
*/
export function sqlResultDisplay(query: unknown, table: SqlTableView): SqlPhoneDisplay {
if (isTransposed(query)) {
return 'web'
}
const display = effectiveSqlDisplay(query, table)
if (display === null || display === 'ActionsTable') {
return 'table'
}
if (NUMBER_DISPLAYS.has(display)) {
return 'number'
}
const { x, y } = sqlAxes(query, table.columns)
if (!x || y.length === 0) {
return 'web'
}
if ((LINE_DISPLAYS.has(display) || BAR_DISPLAYS.has(display)) && x.isDate) {
return 'chart'
}
// A breakdown column or a second y column gives several values per x value, which one bar per row cannot show.
if ((display === 'ActionsBar' || RANKING_DISPLAYS.has(display)) && breakdownColumnOf(query) === null) {
return y.length === 1 ? 'bars' : 'web'
}
return 'web'
}
function displayFor(query: unknown, table: SqlTableView | null): SqlPhoneDisplay {
return table ? sqlResultDisplay(query, table) : sqlQueryDisplay(query)
}
export function sqlChartKind(query: unknown, table: SqlTableView | null): MetricChartKind | null {
if (displayFor(query, table) !== 'chart') {
return null
}
const display = effectiveSqlDisplay(query, table)
const yAxis = chartSettingsOf(query)?.yAxis
const first = Array.isArray(yAxis) ? asRecord(yAxis[0]) : undefined
const displayType = asRecord(asRecord(first?.settings)?.display)?.displayType
return (display !== null && BAR_DISPLAYS.has(display)) || displayType === 'bar' ? 'bar' : 'line'
}
/** The bucket size from the median gap between distinct dates. A few hours of slack keeps DST days daily. */
export function sqlInterval(points: readonly Pick<MetricPoint, 'at'>[]): MetricInterval {
const times = [...new Set(points.map((point) => parseBucketStart(point.at)).filter(Number.isFinite))].sort(
(a, b) => a - b
)
const gaps = times.slice(1).map((time, index) => time - (times[index] ?? time))
if (gaps.length === 0) {
return 'day'
}
gaps.sort((a, b) => a - b)
const median = gaps[Math.floor(gaps.length / 2)] ?? DAY_MS
if (median < DAY_MS - 3 * HOUR_MS) {
return 'hour'
}
if (median < 6 * DAY_MS) {
return 'day'
}
return median < 28 * DAY_MS ? 'week' : 'month'
}
// A date, or a datetime as the API writes it: `T` or a space, optional seconds and fraction, and an offset.
const DATE_CELL = /^(\d{4}-\d{2}-\d{2})(?:[T ](\d{2}:\d{2})(:\d{2})?(?:\.\d+)?)?(?:Z|[+-]\d{2}(?::?\d{2})?)?$/
/**
* The x value as a bucket start, or null when the cell is no date. A datetime keeps the wall clock the server wrote,
* without its offset or fraction, so `parseBucketStart` reads it in local time like a trends bucket and like the
* table shows it.
*/
function dateKey(cell: SqlCell | undefined): string | null {
const match = typeof cell === 'string' ? DATE_CELL.exec(cell.trim()) : null
if (!match?.[1]) {
return null
}
const at = match[2] ? `${match[1]} ${match[2]}${match[3] ?? ':00'}` : match[1]
return Number.isFinite(parseBucketStart(at)) ? at : null
}
function cellLabel(cell: SqlCell): string {
return cell === null ? 'None' : String(cell)
}
function groupValue(cell: SqlCell | undefined): string | number | null {
if (cell === undefined) {
return null
}
return typeof cell === 'boolean' ? String(cell) : cell
}
function runningSum(points: MetricPoint[]): MetricPoint[] {
let total = 0
return points.map((point) => {
if (!Number.isFinite(point.value)) {
return point
}
total += point.value
return { ...point, value: total }
})
}
function columnIndex(table: SqlTableView, column: SqlColumnView | null | undefined): number {
return column ? table.columns.findIndex((candidate) => candidate.name === column.name) : -1
}
/** True when a chart over time has something to draw: a y column and a row with a date. Cheaper than the series. */
export function hasSqlChartSeries(query: unknown, table: SqlTableView): boolean {
if (sqlResultDisplay(query, table) !== 'chart') {
return false
}
const { x, y } = sqlAxes(query, table.columns)
const xIndex = columnIndex(table, x)
return y.length > 0 && table.rows.some((row) => dateKey(row[xIndex]) !== null)
}
/**
* The series of a chart over time, one per y column, or one per y column and breakdown value. Every series has a
* point at every date of the result, in date order, because the chart pairs series by index. A date with no finite
* value is NaN, and rows that share a date and a series add up. Empty when the display is not a chart over time.
*/
export function sqlSeriesViews(query: unknown, table: SqlTableView): InsightSeriesView[] {
if (sqlResultDisplay(query, table) !== 'chart') {
return []
}
const { x, y } = sqlAxes(query, table.columns)
const xIndex = columnIndex(table, x)
const breakdown = breakdownColumnOf(query)
const breakdownIndex = breakdown ? table.columns.findIndex((column) => column.name === breakdown) : -1
const yIndexes = y.map((column) => columnIndex(table, column))
const dates = new Map<string, number>()
// One map per y column: group, then date, then the sum. A Map keeps the groups in the order they first appear.
const sums = y.map(() => new Map<string | number | null, Map<string, number>>())
const groups = new Set<string | number | null>()
for (const row of table.rows) {
const at = dateKey(row[xIndex])
if (at !== null && !dates.has(at)) {
dates.set(at, parseBucketStart(at))
}
const group = breakdownIndex >= 0 ? groupValue(row[breakdownIndex]) : null
groups.add(group)
if (at === null) {
continue
}
yIndexes.forEach((yIndex, seriesIndex) => {
const value = sqlCellNumber(row[yIndex])
if (value === undefined) {
return
}
const byGroup = sums[seriesIndex]
const byDate = byGroup?.get(group) ?? new Map<string, number>()
byDate.set(at, (byDate.get(at) ?? 0) + value)
byGroup?.set(group, byDate)
})
}
const axis = [...dates.entries()].sort((a, b) => a[1] - b[1]).map(([at]) => at)
const cumulative = effectiveSqlDisplay(query, table) === 'ActionsLineGraphCumulative'
return y.flatMap((column, seriesIndex) =>
[...groups].map((group): InsightSeriesView => {
const byDate = sums[seriesIndex]?.get(group)
const points = axis.map((at) => ({ at, value: byDate?.get(at) ?? Number.NaN }))
const label =
breakdownIndex < 0
? column.name
: y.length > 1
? `${column.name} - ${cellLabel(group)}`
: cellLabel(group)
return {
// JSON keeps NULL and the empty string apart.
key: `sql:${column.name}:${JSON.stringify(group)}`,
label,
points: cumulative ? runningSum(points) : points,
compare: null,
seriesIndex,
breakdownValue: group,
}
})
)
}
/**
* One bar per row: the x cell is the label and the first y column the value. Rows keep the server's order, because a
* SQL author orders them on purpose. Null when the display is not bars.
*/
export function sqlRankingView(query: unknown, table: SqlTableView): RankingView | null {
if (sqlResultDisplay(query, table) !== 'bars') {
return null
}
const { x, y } = sqlAxes(query, table.columns)
const xIndex = columnIndex(table, x)
const yIndex = columnIndex(table, y[0])
const rows = table.rows.flatMap((row, index): RankedValueView[] => {
const value = sqlCellNumber(row[yIndex])
if (value === undefined) {
return []
}
const label = cellLabel(row[xIndex] ?? null)
return [{ key: `${index}:${label}`, label, value }]
})
return { rows, total: rows.reduce((total, row) => total + row.value, 0) }
}