> ## Documentation Index
> Fetch the complete documentation index at: https://docs.weaveos.com/llms.txt
> Use this file to discover all available pages before exploring further.
# Custom queries
> Ask questions in plain English or write SQL to build your own charts on custom dashboards
Custom queries let you chart anything in your Weave data that the built-in charts don't cover. Ask a question in plain English and Weave's AI writes the SQL for you, or write the SQL yourself. You can preview the result, pick a chart type, and save it as a widget on a [custom dashboard](/features/custom-dashboards).
## Who can use it?
* **Admin** and **Manager** roles can add, edit, and delete custom query widgets
* Anyone who can view a dashboard can view the custom query widgets on it
* You can only add widgets to dashboards you can edit: organization dashboards, or restricted dashboards you created
## Adding a custom query widget
1. Open a custom dashboard and click the **pencil icon** to enter edit mode
2. Click **Add widget**
3. Under **Custom**, choose **Custom query**
The **Custom query** modal opens. If you close it without saving, the empty widget is removed.
Unlike other charts, you can add as many custom query widgets to a dashboard as you like.
## Generating SQL with AI
Type your question in the **Question** box, for example "Weekly merged PRs by team over the last quarter" or "AI spend per engineer this month". Press Enter or click **Generate SQL**. Use Shift+Enter for a new line.
A **Thinking** panel shows each step the agent takes: inspecting tables, running test queries, and revising its answer. A run stops after 5 minutes if the agent hasn't finished.
When the agent is done, the SQL, chart type, and chart settings are filled in. If you left **Widget title** blank, the agent suggests one.
To refine the result, reword your question and click **Generate SQL** again, or edit the SQL by hand. Each generation starts fresh, so put everything you need into the question.
Specific questions work best. Name the metric ("code output", "story points completed", "AI tool cost"), the grouping ("by engineer", "by repository"), and the time axis ("per week") you want. You don't need to include a date range, since the dashboard's date filter applies automatically.
If the agent can't answer, it tells you why, for example "No matching data found — check for typos or try rephrasing your question" or "The requested data category isn't tracked by Weave".
## Writing SQL manually
Click **Write SQL manually** to skip the AI and write the query yourself. A widget title is required in this mode. Click **Use AI** to switch back.
Custom queries use the [ClickHouse SQL dialect](https://clickhouse.com/docs/en/sql-reference). See [SQL rules](#sql-rules) for what's supported.
## Previewing and saving
1. Click **Preview** to run the SQL. The preview shows up to 10,000 rows, with dashboard filters already applied
2. Adjust the chart type and settings (see [Chart types](#chart-types))
3. Optionally add a **Description**, shown beneath the chart title
4. Click **Save to dashboard**
You must preview the current SQL before you can save. Editing the SQL clears the preview. Then click **Done** on the dashboard to save the layout.
## Editing and removing a widget
In dashboard edit mode, each custom query widget has:
* A **chart type switcher** in its header to change the chart type
* An **Edit query** button to reopen the modal with the question and SQL filled in
* An inline title and description you can click to edit
* An **X** button in the top-right corner to remove it from the dashboard
## Chart types
| Chart type | Best for |
| - | - |
| Table | Row-level lists, such as every PR merged this quarter |
| Bar | Comparing a measure across people, teams, or categories |
| Line | Trends over time, one line per category |
| Pie | Share of a total across a few categories |
| Scatter | Comparing two measures per entity, such as PR count vs output |
Weave picks a default based on the query: scatter when the query has x and y columns, line when it has a time column, bar when the AI provided chart settings, and table otherwise. Scatter queries can switch between table and scatter. All other queries can switch between table, bar, line, and pie. A line chart with no time column falls back to bar.
### Chart settings
For bar, line, pie, and scatter charts, you can change:
| Setting | What it does |
| - | - |
| Aggregation | How rows in each group combine: **Sum**, **Average**, **Count of rows**, or **Ratio** (scatter supports Sum and Average) |
| Unit | The label shown next to values, such as `$`, `%`, `hours`, or `PRs`. Leave blank to infer it from the column name |
**Ratio** divides the sum of a **Numerator** column by the sum of a **Denominator** column in each group, for example bugs opened divided by PRs merged. You can also raise the summed numerator to a **Numerator power** of 2 or 3. Ratio needs at least two numeric columns in the preview.
The grouping column, value column, time column, and top-N limit are set by the AI. To change them, edit the question or the SQL.
Bar and line charts grouped by people or teams have a sort menu: **Alphabetical**, **Value**, or **Random**.
### Tables
For tables, **Group by** collapses rows into one summary row per value of a text column. Expand a group to see its rows. The chart's value column uses the widget's aggregation (Sum, Average, or Count; Ratio shows as a sum), other numeric columns are summed by default, and text columns show a count of distinct values. Click any column header to sort.
### Headline number
Bar and line charts show a headline number in the top-right corner: a total for Sum and Count, or an average for Average and Ratio. When the chart has a time axis and a date filter is set, a pill shows the change against the previous period of the same length.
## Dashboard filters and granularity
Dashboard filters apply to a custom query only when its results include a matching column:
| Dashboard filter | Matching columns |
| - | - |
| Date range | Any date or datetime column (a row matches if any is in range) |
| User | Account columns such as `account_id` or `author_account_id`, or code platform user columns such as `author_id` |
| Team | `team_id`, `parent_team_id`, or user columns via team membership |
| Repository | `repository_id` |
| Project | `project_id` |
| Jira space | `space_id` |
| Aha! product | `initiative_id` |
| AI tool | `ai_tool`, `ai_code_assistant` |
| Tags | `tag_id`, `label_id` |
If a query has no matching column for a filter, that filter doesn't affect the widget and no warning is shown. The AI includes these columns by default. If you write SQL yourself, keep them in your `SELECT` so filters work, and leave date ranges out of the `WHERE` clause.
Charts with a time column group rows by the dashboard's granularity (day, week, month, quarter, year) in your organization's timezone. See [Filters and granularity](/features/filters).
Queries that use a relative time axis, such as "weeks since each engineer's first PR", ignore the date filter.
## Drill-down and export
In view mode, click a bar, line point, pie slice, or scatter point to see the underlying rows. Drill-down shows the rows your SQL returned, so a query that returns one row per PR drills down to individual PRs, while a pre-aggregated query drills down to its aggregate rows. Drill-down requires drill-down permission for your role.
Admins can click **Export as CSV** in a widget's menu to download what's on screen. See [CSV export](/export/csv).
## Available data
Custom queries can read Weave's analytics tables for your organization. Commonly used tables include:
| Table | Contents |
| - | - |
| `pr_output` | [Code output](/metrics/code-output) per PR and engineer |
| `pr_output_by_ai` | Code output split by AI contributor, for AI output percentage |
| `tool_costs` | AI tool spend per account, tool, and model |
| `repository_pull_requests` | Pull requests |
| `repository_pull_request_reviews` | PR reviews |
| `repository_pull_request_review_comments` | PR review comments |
| `repository_pull_request_labels` | PR labels |
| `pr_categories` | PR categories |
| `pr_locs` | Raw lines of code added and deleted per PR |
| `pr_turnovers` | [Code turnover](/metrics/code-turnover) per PR |
| `proj_management_tasks` | Tasks from Jira, Linear, and other project management tools, including story points and status |
| `proj_management_projects` | Projects and epics |
| `proj_management_task_tags` | Task tags |
| `incidents` | Incidents |
| `repository_members` | Code platform users |
| `teams` | Teams |
There are also tables for AI tool usage (Claude Code, Cursor, GitHub Copilot, Codex, Gemini, Windsurf, and others), CI/CD pipelines, calendars, and connected accounts. The AI can inspect any table's columns while it works, so you don't need to know table names to ask a question.
### Names and privacy
Columns that hold people, repository, team, or PR IDs display as names and avatars. This works only when the column keeps its original name, such as `author_account_id`, `repository_id`, or `team_id`. Renaming an ID column with `AS` shows the raw ID instead.
To protect sensitive content, some columns can't be queried, including email addresses, usernames, display names, PR descriptions, comment bodies, chat message text, and AI prompt content.
## SQL rules
Custom queries accept a single ClickHouse `SELECT` statement.
* **Supported:** joins, subqueries, CTEs (`WITH`), `GROUP BY`, `HAVING`, `UNION ALL`, and window functions (`row_number`, `rank`, `dense_rank`)
* **Not supported:** anything other than `SELECT`, `UNION`/`INTERSECT`/`EXCEPT`, `WITH RECURSIVE`, and comma-separated tables in `FROM` (use explicit `JOIN`s)
* **Functions:** common aggregate, math, string, date, type conversion, conditional, array, and null-handling functions are allowed. For example `count`, `sum`, `avg`, `countIf`, `uniqExact`, `round`, `lower`, `toStartOfWeek`, `dateDiff`, `if`, `multiIf`, `coalesce`, and `arrayJoin`. Other functions return `function "x" is not allowed`. For conditional aggregates other than `countIf` and `sumIf`, use `max(if(condition, column, NULL))`
* **Reserved words:** quote columns such as `url`, `type`, `key`, and `status` when you prefix them with a table alias, for example `pr."url"`
* **Order:** if you don't add `ORDER BY`, results are sorted by the first timestamp column, newest first
Return one row per entity (per PR, per task, per engineer-day) and let the chart settings do the aggregation. That keeps drill-down useful and lets the dashboard's filters and granularity work. Queries that pre-aggregate into fixed buckets in SQL don't respond to granularity changes.
## Limits
| Limit | Value |
| - | - |
| Preview rows | 10,000 |
| Saved table rows | 1,000,000 |
| Saved bar/line/pie/scatter rows | 100,000 |
| Query run time | 10 seconds |
| Data scanned per query | 1 GB |
| Response size | 20 MB |
| Scatter points shown | 3,000 |
| Series per chart | 30 |
Row limits are applied as a `LIMIT`, so a higher `LIMIT` in your SQL is lowered to the cap. If a query runs too long or scans too much data, narrow the date range or add filters.
## Data freshness
When you save a widget, Weave runs the query and stores the result. Saved widgets refresh about every hour as new data syncs, and the dashboard shows the stored result between refreshes. A newly saved widget may briefly show "Widget data is still being prepared. It will appear on the next refresh."
## Privacy
Your question is sent to Weave's AI model provider to generate the SQL. If your organization has opted out of prompt retention, the question text isn't stored with the saved widget.
## Troubleshooting
The query's results don't include a column that filter can match. Edit the query and add the column, for example `author_account_id` for the user filter or `repository_id` for the repository filter. See [Dashboard filters and granularity](#dashboard-filters-and-granularity).
The ID column was renamed with `AS`. Keep the original column name, such as `author_account_id` or `team_id`, and set the widget title or unit instead.
The query hit the 10-second or 1 GB limit. Narrow the dashboard's date range, add filters, or select fewer columns.
The function isn't on the allowed list, or the column is blocked for privacy. See [SQL rules](#sql-rules) and [Names and privacy](#names-and-privacy).
The results don't fit the selected chart type. For example, bar and line charts need a category or time column plus a numeric column, and scatter charts need x and y columns. Switch to **Table** to inspect the results, or regenerate the query.
This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.