Custom SQL Editor

Overview

The Custom SQL Editor lets you override the auto-generated query for any widget with your own ClickHouse SQL. Instead of selecting metrics and dimensions from the schema picker, you write SQL directly — enabling cross-table joins, window functions, complex calculations, and more.

How to Activate

1. 1Open a widget's properties panel
2. 2Expand the Query section
3. 3Click "Customize Query" (snapshots current auto-generated SQL) or "Write Custom SQL" (starts from a template)
4. 4Toggle custom SQL ON/OFF to switch between custom and auto mode
5. 5Use "Reset" to restore the snapshotted auto SQL, or "Revert to Auto" to clear custom SQL entirely

Placeholder Reference

Custom SQL supports these placeholders that are resolved at execution time:

• {table} — Table name (required for FROM clause)
• {where_date} — Date range filter based on dashboard date picker
• {where_filters} — All dashboard filters combined (required for filters to work)
• {where_accounts} — Account filter only
• {where_channels} — Channel filter only
• {where_campaigns} — Campaign filter only
• {dateFrom} — Start date string (e.g., 2024-01-01) for cross-table joins
• {dateTo} — End date string (e.g., 2024-01-31) for cross-table joins
• {date_grouping} — Date bucketing expression (auto-selects day/week/month)

Important: All {where_*} placeholders resolve to AND ... conditions. Place them after WHERE 1=1. If {where_filters} is missing, dashboard filters won't apply to the widget.

Starter Template

Every custom SQL query starts with this pattern:

SELECT * FROM {table} WHERE 1=1{where_date}{where_filters} LIMIT 1000

Examples by Widget Type

Chart — Time Series with Calculated Metrics

SELECT toDate(date) as date, SUM(spend) as spend, SUM(revenue) as revenue, SUM(revenue) / NULLIF(SUM(spend), 0) as roas FROM {table} WHERE 1=1{where_date}{where_filters} GROUP BY date ORDER BY date

Table — Grouped Report

SELECT data_source_type as channel, campaign_name, SUM(spend) as spend, SUM(clicks) as clicks, SUM(spend) / NULLIF(SUM(clicks), 0) as cpc FROM {table} WHERE 1=1{where_date}{where_filters} GROUP BY channel, campaign_name ORDER BY spend DESC LIMIT 500

KPI — Single Value

SELECT SUM(revenue) as value FROM {table} WHERE 1=1{where_date}{where_filters}

Cross-Table Join

SELECT a.campaign_name, SUM(a.spend) as ad_spend, SUM(b.conversions) as crm_conversions FROM paid_media a LEFT JOIN crm_data b ON a.campaign_name = b.utm_campaign AND toDate(a.date) = toDate(b.date) WHERE a.date >= '{dateFrom}' AND a.date <= '{dateTo}' GROUP BY a.campaign_name ORDER BY ad_spend DESC

Note: Cross-table joins use {dateFrom} / {dateTo} instead of {where_date} because the join involves multiple tables.

Tips

• Always use NULLIF(denominator, 0) to prevent division by zero
• Use WHERE 1=1{where_date}{where_filters} pattern for proper filter support
• For KPI widgets, alias your metric as value for reliable detection
• For charts, return a date column (or set dateField in config to match your column name)
• Add LIMIT to prevent performance issues with large datasets
• If {where_filters} is omitted, dashboard filters won't apply to the widget