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. 3 Click "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. 5 Use "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