Wren SQL

Wren SQL

Version check

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:

Context

• You are querying a semantic layer, not a database directly.
• Only use model/view/column names defined in the MDL — never raw database table references.
• Wren Engine uses a generic SQL dialect similar to ANSI SQL (DataFusion/Postgres/DuckDB), but with differences.
• Check the dataSource field to identify the backend and apply dialect-specific rules if needed.

Core SQL Rules

• Only SELECT statements. No DELETE, UPDATE, INSERT.
• Only use tables and columns from the MDL schema.
• Do not include comments in generated SQL.
• Prefer CTEs over subqueries.
• Identifiers are case-sensitive. Quote identifiers containing unicode, special characters (except _), or starting with a digit using double quotes.
  • Examples: "客户"."姓名", "table-name"."col", "123column"
• Identifier quotes: " (double quotes). String literal quotes: ' (single quotes).
• For specific date queries, use a range:

  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)
  

• For ranking, use DENSE_RANK() + WHERE. Include the ranking column in SELECT.
• Avoid correlated subqueries — use JOINs instead.
• Use SAFE_CAST when casting might fail: SAFE_CAST(col AS INT)

Filter Strategies

Supported Cast Types

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'

Aggregation

• All non-aggregated SELECT columns must appear in GROUP BY (window functions excepted).
• Aggregate conditions go in HAVING, not WHERE.
• Prefer ordinal GROUP BY for long column names:

  SELECT very_long_column_name AS alias, COUNT(*) FROM t GROUP BY 1
  

Sorting and Limiting

• ORDER BY for sort; LIMIT to restrict rows.
• When ORDER BY appears in a subquery or CTE, always include LIMIT.

Subquery Patterns

• Prefer CTEs (WITH clause) over nested subqueries.
• Subquery in SELECT must return a single value per row.
• Subquery in WHERE: use IN, EXISTS, or comparison operators.
• IN SUBQUERY in JOIN conditions is not supported — use JOIN ... ON instead.
• RECURSIVE CTEs are not supported.

Calculated Fields

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