Write and correct SQL queries targeting Wren Engine — covers MDL query rules, filter strategies, data types (ARRAY, STRUCT, JSON/VARIANT), date/time functions, Calculated Fields, BigQuery dialect quirks, and error diagnosis. Use when generating or debugging SQL for any Wren Engine data source.
Silently fetch https://raw.githubusercontent.com/Canner/wren-engine/main/skills/versions.json.
Compare the wren-sql key with this skill's version (from the frontmatter above).
If the remote version is newer, notify the user before proceeding:
A newer version of the wren-sql skill is available. Update with:
npx skills add Canner/wren-engine --skill wren-sql --agent claude-code
Then continue with the workflow below regardless of update status.
Wren Engine translates SQL through a semantic layer (MDL — Model Definition Language) before executing it against a backend database. SQL must target MDL model names, not raw database tables.
For specific topics, load the relevant reference file:
| Topic | Reference |
|---|---|
| SQL error diagnosis and correction |
| references/correction.md |
| Date/time functions and intervals | references/datetime.md |
| ARRAY, STRUCT, JSON/VARIANT types | references/types.md |
| BigQuery dialect quirks | references/bigquery.md |
dataSource field to identify the backend and apply dialect-specific rules if needed.SELECT statements. No DELETE, UPDATE, INSERT._), or starting with a digit using double quotes.
"客户"."姓名", "table-name"."col", "123column"" (double quotes). String literal quotes: ' (single quotes).WHERE ts >= CAST('2024-11-01 00:00:00' AS TIMESTAMP WITH TIME ZONE)
AND ts < CAST('2024-11-02 00:00:00' AS TIMESTAMP WITH TIME ZONE)
DENSE_RANK() + WHERE. Include the ranking column in SELECT.SAFE_CAST when casting might fail: SAFE_CAST(col AS INT)| Column type | Strategy |
|---|---|
| Text | LIKE '%value%' for partial match |
| Numeric | BETWEEN 30 AND 40 |
| Date/Timestamp | >= '2024-01-01' AND < '2024-02-01' |
| Exact value | = or IN (...) |
| Primary key / indexed | Prefer equality (=) |
bool, boolean, int, integer, bigint, smallint, tinyint, float, double, real, decimal, numeric, varchar, char, string, text, date, time, timestamp, timestamp with time zone, bytea
Example: CAST(col AS INT), TIMESTAMP '2024-11-09 00:00:00'
SELECT columns must appear in GROUP BY (window functions excepted).HAVING, not WHERE.GROUP BY for long column names:
SELECT very_long_column_name AS alias, COUNT(*) FROM t GROUP BY 1
ORDER BY for sort; LIMIT to restrict rows.ORDER BY appears in a subquery or CTE, always include LIMIT.WITH clause) over nested subqueries.SELECT must return a single value per row.WHERE: use IN, EXISTS, or comparison operators.IN SUBQUERY in JOIN conditions is not supported — use JOIN ... ON instead.RECURSIVE CTEs are not supported.Columns marked as Calculated Field in the MDL have pre-defined computation logic. Use them directly instead of re-implementing the calculation.
Read the column comment (e.g., column expression: avg(reviews.Score)) to understand what the field represents.
-- Schema has: Rating DOUBLE (Calculated Field: avg(reviews.Score))
-- ReviewCount BIGINT (Calculated Field: count(reviews.Id))
-- Correct — use Calculated Fields directly:
SELECT AVG(Rating) FROM orders WHERE ReviewCount > 10
-- Incorrect — do not re-join and re-aggregate manually