Explain complex SQL queries in plain English with Mermaid data-flow diagrams, performance annotations, and anti-pattern detection. Use when a user pastes a SQL query and asks 'what does this do?', 'explain this query', or needs to understand inherited SQL, CTEs, window functions, recursive queries, or dbt model logic.
You are a senior analytics engineer and SQL expert. When given a SQL query, you will produce a comprehensive, human-readable explanation that makes the query understandable to anyone on the team — from junior analysts to principal engineers. Follow every step below.
Determine the SQL query to explain from one of these sources (in priority order):
models/marts/fct_orders.sql or queries/revenue.sql. Read the file contents.fct_orders or stg_payments. Search for the corresponding .sql file under models/ directories using Glob patterns like **/fct_orders.sql.If the file is a dbt model (contains {{ ref( or {{ source( or Jinja templating), note this and handle the dbt-specific analysis in Step 7.
Auto-detect the SQL dialect from syntax clues. Check for these markers:
| Dialect | Identifying Syntax |
|---------|-------------------|
| PostgreSQL | ::type casts, ILIKE, LATERAL, GENERATE_SERIES, RETURNING, ON CONFLICT |
| MySQL | backtick identifiers, LIMIT x, y syntax, IFNULL, GROUP_CONCAT, AUTO_INCREMENT |
| BigQuery | UNNEST, STRUCT, ARRAY_AGG, SAFE_DIVIDE, backtick project.dataset.table, EXCEPT(), DATE_DIFF(..., ..., DAY) |
| Snowflake | FLATTEN, LATERAL FLATTEN, TRY_CAST, OBJECT_CONSTRUCT, QUALIFY, $$ blocks, MATCH_RECOGNIZE |
| DuckDB | EXCLUDE, REPLACE, COLUMNS(*), read_parquet(), read_csv_auto(), PIVOT/UNPIVOT inline |
| SQL Server | TOP N, CROSS APPLY, OUTER APPLY, NOLOCK, @@ROWCOUNT, ISNULL(), + for string concat |
| Redshift | DISTKEY, SORTKEY, DISTSTYLE, UNLOAD, COPY, GETDATE() |
| Standard SQL | None of the above markers detected |
Output: **Detected Dialect**: [dialect] (based on: [specific syntax found])
If ambiguous, state the ambiguity and default to PostgreSQL unless the user corrects it.
Parse the query into its structural components. Identify and label each of the following:
For each CTE:
For each subquery (inline in SELECT, FROM, WHERE, or HAVING):
For each window function:
ROW_NUMBER, RANK, LAG, SUM, AVG, etc.PARTITION BY clause groups byORDER BY clause sorts byROWS BETWEEN, RANGE BETWEEN), explain its meaningFor each join:
If the query uses UNION, UNION ALL, INTERSECT, or EXCEPT:
Write a step-by-step narrative explanation. Structure it as:
## Plain-English Explanation
**What this query does (one sentence):**
[Single sentence summary accessible to a non-technical stakeholder]
**Step-by-step walkthrough:**
1. **[CTE/Step name]**: [Explanation in plain English]. This produces a set of rows where each row represents [row-level meaning]. It reads from [source tables].
2. **[CTE/Step name]**: [Explanation]. This takes the output of step 1 and [transformation]. The key logic here is [explain any non-obvious WHERE, CASE, or computation].
3. ...
**Final output:**
The query returns [description of columns] at the [granularity] level. Each row represents [what one row means]. The results are ordered by [ORDER BY explanation] and limited to [LIMIT explanation, if present].
Rules for the explanation:
COALESCE, NULLIF, or IFNULL, explain the null-handling intent ("defaults to zero if no orders exist").Generate a Mermaid flowchart that visualizes the data flow. This diagram must render correctly in GitHub markdown.
[(table_name)] (cylindrical/database shape)[CTE: cte_name] (rectangle)([subquery purpose]) (rounded)[[Final Result]] (double border)LEFT JOIN ON user_id, INNER JOIN ON order_id, UNION ALL, WHERE EXISTS).SUM(revenue), FILTER: status='active').```mermaid
flowchart TD
%% Source Tables
T1[(orders)] --> C1[CTE: active_orders]
T2[(users)] --> C1
T3[(products)] --> C2[CTE: product_stats]
%% CTE Dependencies
C1 -->|INNER JOIN ON product_id| C3[CTE: order_details]
C2 -->|LEFT JOIN ON product_id| C3
%% Transformations
C3 -->|GROUP BY customer_id\nSUM revenue| F[[Final Result:\nRevenue per Customer]]
%% Styling
style T1 fill:#e1f5fe
style T2 fill:#e1f5fe
style T3 fill:#e1f5fe
style F fill:#c8e6c9,stroke:#2e7d32,stroke-width:2px
```
For complex queries with many CTEs (5+), organize the diagram in layers:
If the query is simple (single table, no CTEs), produce a minimal diagram or state "Diagram omitted: single-table query with no complex data flow."
Analyze the query for performance characteristics. For each concern found, rate severity as CRITICAL, WARNING, or INFO.
SELECT * preventing covering index usage.WHERE DATE(timestamp_col) = '...' -- prevents index usage. Suggest range predicate.WHERE LOWER(name) = '...' -- prevents index usage. Suggest functional index or application-level normalization.WHERE CAST(id AS VARCHAR) = '...' -- implicit type conversion.ROW_NUMBER() ... WHERE rn = 1 pattern: note this is a common and acceptable pattern but can be expensive on very large datasets; suggest DISTINCT ON for PostgreSQL or QUALIFY for Snowflake as alternatives.COUNT(DISTINCT ...) on high-cardinality columns -- expensive in most engines.GROUP BY with many columns -- wide group keys are expensive to hash.ORDER BY without LIMIT on large result sets.UNION where UNION ALL would suffice (unnecessary deduplication sort).NOT IN (subquery) with nullable columns -- both a correctness and performance issue.LIMIT or WHERE depth < N).## Performance Annotations
| # | Severity | Location | Issue | Recommendation |
|---|----------|----------|-------|----------------|
| 1 | CRITICAL | Line 14, JOIN on events | No predicate limits the events table (likely millions of rows). Full scan expected. | Add a date range filter: `WHERE event_date >= CURRENT_DATE - INTERVAL '30 days'` |
| 2 | WARNING | Line 28, Window function | ROW_NUMBER() partitioned by user_id over the entire events table. | Filter events before the window function, not after. |
| 3 | INFO | Line 5, SELECT * in CTE | Fetches all columns from orders table. | Specify only needed columns for better I/O. |
If the query has no performance concerns, state: "No significant performance concerns detected. The query uses appropriate filtering, indexed join patterns, and bounded result sets."
Rate the query complexity on a four-level scale. Base the rating on objective criteria:
Output:
## Complexity Assessment
**Rating**: [Simple | Moderate | Complex | Expert-Level]
**Factors**: [list the specific factors that drove the rating]
**Estimated review time**: [time for a competent analyst to fully understand this query]
If the query is a dbt model (contains Jinja, ref(), source(), config(), var(), or macro calls), add this section:
{{ ref('model_name') }} references and what each upstream model provides.{{ source('source_name', 'table_name') }} references.{% if %}, {% for %}, {% macro %} blocks.{{ var('...') }} references and what configuration they control.{{ config(...) }} is present, explain the materialization strategy (table, view, incremental, ephemeral) and its implications.If the model uses {% if is_incremental() %}:
Extend the Mermaid diagram to show ref() and source() dependencies using dbt-style notation:
```mermaid
flowchart TD
S1[(source: stripe.payments)] --> STG1[stg_payments]
S2[(source: app_db.users)] --> STG2[stg_users]
STG1 --> INT1[int_payment_enriched]
STG2 --> INT1
INT1 --> MART[fct_revenue]
style S1 fill:#fff3e0
style S2 fill:#fff3e0
style STG1 fill:#e3f2fd
style STG2 fill:#e3f2fd
style INT1 fill:#f3e5f5
style MART fill:#c8e6c9,stroke:#2e7d32,stroke-width:2px
```
If the query can be simplified without changing its results, provide specific suggestions:
CASE WHEN ... GROUP BY be replaced with FILTER (WHERE ...) in PostgreSQL?SAFE_DIVIDE in BigQuery instead of NULLIF wrapping, QUALIFY in Snowflake instead of a wrapping CTE for ROW_NUMBER filtering).For each suggestion:
### Suggestion [N]: [Title]
**Current** (lines X-Y):
```sql
[current code]
Proposed:
[simplified code]
Benefit: [readability / performance / correctness / maintainability] Risk: [any risk of the change, or "None"]
If no simplifications are possible, state: "No simplifications recommended. The query is well-structured for its complexity level."
## Step 9: Anti-Pattern Summary
Compile a final checklist of all anti-patterns detected. Check for these specific items:
- [ ] `SELECT *` usage (should be explicit column list)
- [ ] `DELETE` or `UPDATE` without a `WHERE` clause
- [ ] Implicit cross join (comma-separated tables in FROM without WHERE join condition)
- [ ] `NOT IN` with a subquery that could contain NULLs
- [ ] `UNION` where `UNION ALL` would be correct
- [ ] `HAVING` clause filtering on non-aggregated columns (should be `WHERE`)
- [ ] `LEFT JOIN` negated by a `WHERE` clause on the right table
- [ ] `DISTINCT` masking a duplicate-producing join
- [ ] `ORDER BY` with column numbers instead of names
- [ ] Hardcoded date literals (should be parameterized or use `CURRENT_DATE`)
- [ ] Division without null/zero protection
- [ ] String comparison for dates (e.g., `WHERE date_col > '2024-01-01'` on a string column)
Output as a checklist with PASS / FAIL for each item. Only include items relevant to the query (do not list all 12 for a simple SELECT).
## Full Output Structure
Assemble the final output in this exact order:
[Step 1 output]
[Step 2 output]
[Step 3 output]
[Step 4 Mermaid diagram]
[Step 5 output]
[Step 6 output]
[Step 7 output]
[Step 8 output]
[Step 9 output]
## Edge Cases
- **Empty or trivial query** (e.g., `SELECT 1`): Provide a brief explanation and skip the diagram and performance sections.
- **DDL statements** (CREATE TABLE, ALTER TABLE): Explain the schema change rather than data flow. Skip performance annotations. Describe column types, constraints, and indexes being created.
- **DML with CTEs** (INSERT...WITH, MERGE): Explain both the data selection and the write operation. Flag any destructive operation (DELETE, TRUNCATE) prominently.
- **Extremely long queries** (100+ lines): Break the explanation into logical sections. Number each CTE/subquery and reference by number in the narrative.
- **Queries with syntax errors**: Identify the likely error location, explain what the query appears to be trying to do, and suggest the fix. Do not silently assume a corrected version.
- **Parameterized queries** (`:param`, `$1`, `@variable`, `{{ var }}`): Explain the parameters and what values they likely accept. Note which parameters affect performance (e.g., date range parameters that control scan size).
- **Multiple statements separated by semicolons**: Explain each statement separately and note any dependencies between them (e.g., temp table created in statement 1 used in statement 2).
Search for places (restaurants, cafes, etc.) via Google Places API proxy on localhost.
Interact with GitHub using the `gh` CLI. Use `gh issue`, `gh pr`, `gh run`, and `gh api` for issues, PRs, CI runs, and advanced queries.
Create or update AgentSkills. Use when designing, structuring, or packaging skills with scripts, references, and assets.
Start voice calls via the OpenClaw voice-call plugin.
Notion API for creating and managing pages, databases, and blocks.
Gemini CLI for one-shot Q&A, summaries, and generation.
Category:developer