ktx Analytics Workflow

You have access to ktx MCP tools for data discovery, semantic-layer analysis, raw read-only SQL, wiki context, and memory ingest. Follow this workflow.

<sql_craft> Heuristics for writing correct (not merely runnable) SQL. Each is a default plus the reason it holds on any database; apply judgment to the question and the data.

Schema discovery before writing SQL

• Sample before you compose. Inspect representative rows of every table you will touch (entity_details plus a small sql_execution sample) to confirm date/time encoding (YYYYMMDD integer vs ISO text vs epoch), null prevalence in join/filter keys, and the real set of categorical/enum values. Assumptions about encoding and nullability are the most common source of silently-wrong filters.
• Cast to the real type before comparing. Compare a column against a literal of its actual type in WHERE/JOIN. A string column compared to a numeric literal (or the reverse) can silently match nothing instead of raising an error.
• Parse text-encoded numerics before doing math on them. When a column the question treats as a number is stored as text, sample its distinct values (the Sample before you compose habit) to learn the encodings actually present — unit suffixes (K/M/B), currency symbols, thousands separators, percent signs, and non-numeric sentinels (-, N/A, empty) — and never infer the format from the column name. Why: aggregated or compared as-is the text sorts lexically ('100' < '9') and a naive cast collapses formatted values to 0/NULL, so the query runs but the number is silently wrong instead of erroring.
• Strip, scale, and cast in one early CTE. Strip currency/separator/percent characters, multiply by the suffix scale (K=10^3, M=10^6, B=10^9), map sentinels to 0 or NULL (by the Default by additivity rule below), then cast to a numeric type — all in a single early CTE so every layer above sees clean numbers. This is the meaning-is-numeric complement to Cast to the real type before comparing. Why: one clean conversion at the base keeps the lexical-sort-and-cast-to-0 failure out of every downstream layer.
• Confirm the parse covered every value. After parsing, count the non-sentinel rows that failed to parse — a failed parse should surface as NULL, visible only with a failure-detecting cast from sql_dialect_notes (a plain CAST errors on some engines and on sqlite silently returns 0/partial, so an IS NULL check is meaningless there). Why: an encoding the sample missed would otherwise vanish into 0/NULL instead of being caught.
• Parse code/dependency text by its real grammar, not one broad regex. When a question extracts imported/required/loaded packages or modules from stored source text or dependency manifests, parse by the language or format, not a single pattern: Java import/import static — drop the terminal class/member, keep the package path, and allow valid identifier segments with underscores and mixed case (e.g. com.planet_ink.coffee_mud); Python — handle both import a, b as c and from a.b import c, stripping aliases; R — handle library(...) and require(...); notebooks (.ipynb) — parse the JSON and read each cell's source lines before applying the language rules (never regex the raw notebook file, whose prose contains the words "import"/"from"); JSON/manifest files — PARSE_JSON and flatten the dependency object's keys (e.g. require). Strip comments/prose lines first and split multi-import lines so each declared dependency is counted once. Why: a single lowercase-segment regex silently drops real identifiers and matches prose, so the ranking is wrong though the query runs.
• Decide the counting population explicitly when a table is deduplicated. If the source table is de-duplicated and carries a documented copy/occurrence count (e.g. a copies column = "repositories sharing this exact content"), the count grain is a real modeling choice: weight by that column only when the question's population is clearly the represented files/repositories; otherwise count the distinct stored rows. State which population the question names and match it — do not default to one silently. Why: on a deduplicated table COUNT(*) and SUM(copies) give different rankings, so the right metric depends on the population the question asks about, not on which is larger.

-- "Total trade volume" where value_text holds '1.2K', '3M', '$1,200', '-'.
-- WRONG: a naive cast collapses the formatted values ('1.2K'->1.2, '$1,200'->0,
-- '-'->0) instead of erroring, so the SUM comes back silently far too low.
SELECT SUM(CAST(value_text AS REAL)) AS total_volume FROM metrics;

-- RIGHT: strip symbols/suffixes, scale by the K/M/B suffix, map sentinels to 0, and
-- cast once in an early CTE; the SUM then runs over clean numbers.
WITH parsed AS (
  SELECT CASE WHEN value_text IN ('-', 'N/A', '') THEN 0
    ELSE CAST(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(value_text,
                '$', ''), ',', ''), 'K', ''), 'M', ''), 'B', '') AS DECIMAL(18, 4))
         * CASE WHEN value_text LIKE '%K' THEN 1000
                WHEN value_text LIKE '%M' THEN 1000000
                WHEN value_text LIKE '%B' THEN 1000000000 ELSE 1 END
    END AS volume
  FROM metrics
)
SELECT SUM(volume) AS total_volume FROM parsed;

• Canonicalize observed URL-path variants before page-level analysis. When a question groups, filters, or sequences web pages by a path/url column, sample its distinct values first. If the data itself shows route-label variants — /route and /route/ for the same page context — define a canonical page-path expression in an early CTE and use it everywhere above that CTE: preserve / as root, strip trailing slashes only from non-root paths, and map an observed empty path to / only when the column is a URL path and the sampled rows show blank root-page events. Do not merge different route names (/input ≠ /regist/input), strip query strings/fragments/host/scheme, lowercase paths, or canonicalize at all when the question asks for the raw stored URL/path or for slash-vs-no-slash differences. Why: raw request logs routinely store the same user-visible page both with and without a trailing slash, so grouping or sequencing the raw labels silently splits one page into several — but inventing aliases the data doesn't show would just as silently merge distinct pages.

Composition

• Build incrementally. Assemble complex queries one CTE at a time, checking each layer's output on a small sample before stacking the next; a wrong intermediate layer is far cheaper to catch early than to debug in the final number.
• Avoid fan-out joins — the danger is cumulative. Any one-to-many hop on the path between a measure's owning table and the aggregate inflates that measure, even when the offending join sits several hops below the SUM/COUNT and is easy to miss. The fix is the single-hop one applied per measure-owning table along the whole chain: pre-aggregate each coarse-grained measure to its own grain in a CTE, then join the already-aggregated result.
• Verify the grain holds across each join. As you compose, confirm a join you intend to be one-to-one / many-to-one did not change the grain you aggregate at — e.g. the row count (or the count of the aggregate's key) is unchanged across it. When a join is genuinely one-to-many, reach for the default fix (pre-aggregate to grain); for a pure count, COUNT(DISTINCT key) is an acceptable escape hatch. A SUM/AVG of a fanned-out measure must pre-aggregate — DISTINCT cannot de-duplicate a sum.
• A join that only attaches a label must not drop rows — LEFT JOIN it, and key the aggregate on the fact column. Fan-out's mirror image is just as silent: when you join a dimension table only to fetch a display attribute (a name for an id, a category for a product), an incomplete dimension — and dimensions are routinely incomplete: trimmed catalogs, late-arriving rows, slowly-changing-dimension gaps — makes a plain inner JOIN quietly discard every fact row whose key has no parent, shrinking the counts, sums, and the universe over which any share / average / median is computed (a measure halves with no error and no empty result). Two guards: (1) inner-join a dimension only when you intend it as a filter — you want exactly the rows that have a parent — never merely to read a column off it; for pure enrichment use LEFT JOIN. (2) Key the aggregation and GROUP BY on the fact column (sales.prod_id), not the dimension column (products.prod_id), so an unmatched key yields a NULL label on its own row rather than dropping or collapsing it. Use the same row-count check as above, but for an enrichment join confirm the fact row count is unchanged (not merely un-inflated); if a dimension you only wanted a name from removed rows, that is the bug.
• Source each filter, date, and measure from the table that OWNS it at the question's grain. When two joined fact tables carry similarly-named columns at different grains — a parent (one row per order: its status, placement created_at, num_of_item) and its child (one row per line item: line created_at, sale_price, cost) — read each predicate/measure from the table whose grain the question names, not from whichever is in scope after the join. "Orders that are Complete", "for each month of the orders", "the order's creation date" are order-grain, so the status filter and the month bucket come from the parent order row, even though the child also has status/created_at columns; line price and cost come from the child. Why: the parent's and child's copies of a column diverge (an item's placement month or status can differ from its order's), so anchoring an order-grain filter or calendar on the line table silently buckets/filters the wrong rows. The mirror at metric grain: never combine a parent-grain count with child rows after the join (e.g. num_of_item * SUM(line_price) once per line) — compute each measure at its own grain (sum line prices to the order, take num_of_item once per order) before combining.

-- "How many orders per region contain a returned item?" — count each order once.
-- WRONG: order_lines is joined to apply the line-level filter, which multiplies
-- orders; an order with two returned lines is counted twice, three joins below
-- the COUNT, where the inflation is easy to miss.
SELECT r.region_id, COUNT(*) AS n_orders
FROM regions r
JOIN stores s      ON s.region_id = r.region_id
JOIN orders o      ON o.store_id  = s.store_id
JOIN order_lines l ON l.order_id  = o.order_id
WHERE l.status = 'returned'
GROUP BY r.region_id;

-- RIGHT: collapse order_lines to one row per qualifying order first, then join up
-- so each order contributes exactly once.
WITH returned_orders AS (
  SELECT order_id FROM order_lines WHERE status = 'returned' GROUP BY order_id
)
SELECT r.region_id, COUNT(*) AS n_orders
FROM regions r
JOIN stores s           ON s.region_id = r.region_id
JOIN orders o           ON o.store_id  = s.store_id
JOIN returned_orders ro ON ro.order_id = o.order_id
GROUP BY r.region_id;
-- A pure count could also use COUNT(DISTINCT o.order_id); a SUM/AVG of an
-- order-level measure fanned out this way must pre-aggregate — DISTINCT can't
-- de-duplicate a sum.

Ordering & aggregation determinism

• Make the ordering deterministic. Give every ranking/ordering window a complete tie-breaker by appending unique key column(s) to ORDER BY, so RANK/ROW_NUMBER/LAG results are stable instead of flickering between runs.
• Order inside string/array aggregation. When concatenating rows into a delimited string or building an ordered array (GROUP_CONCAT / string_agg / array_agg), the element order is undefined unless you specify it — put an explicit ORDER BY on the aggregate. Be deliberate about collation: the default text sort is binary/case-sensitive (so 'BBQ' sorts before 'Bacon' because uppercase code points precede lowercase), which differs from a case-insensitive sort; pick the one the question implies and apply it consistently (ORDER BY ... COLLATE NOCASE for case-insensitive). Why: an unordered or differently-collated concatenation produces a string with the right elements in the wrong order — runnable but not matching the expected text.
• Emit a list-valued answer cell as a delimited STRING, not a raw ARRAY/repeated column. When the answer needs several values in one cell (a set of names/codes/tags for an entity), build a delimited scalar with STRING_AGG(x, ',' ORDER BY x) (or ARRAY_TO_STRING(ARRAY_AGG(x ORDER BY x), ',')) — do not return a SQL ARRAY/repeated column. Why: an array column serializes to an engine-specific representation (e.g. ['a' 'b'] or ["a","b"]) that won't compare equal to a plain delimited list (a,b), so a values-correct answer still mismatches when materialized to rows.
• Filter after the window, not before, for sequence / "first" / "most recent" / "since" questions: compute the window over the full partition, then keep the rows you want. A pre-filter shrinks the partition the window ranks over, so "first"/"most recent" is measured against the wrong set.

-- "Each customer's first order, restricted to orders since 2024-01-01."
-- Wrong: the filter runs before the window, so it ranks only 2024 rows and
-- misses customers whose true first order was earlier.
SELECT customer_id, order_id,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date, order_id) AS seq
FROM orders
WHERE order_date >= '2024-01-01';   -- then keep seq = 1

-- Right: rank the full partition in a CTE, then filter in the outer query.
WITH ranked AS (
  SELECT customer_id, order_id, order_date,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date, order_id) AS seq
  FROM orders
)
SELECT customer_id, order_id, order_date
FROM ranked
WHERE seq = 1 AND order_date >= '2024-01-01';

• Cumulative / running total. Use an explicit frame — SUM(x) OVER (PARTITION BY k ORDER BY t ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) — with a complete tie-breaker on the ORDER BY (per the deterministic-ordering rule above). Why: a bare ORDER BY defaults to a RANGE-based frame bounded at the current row, which on ties in the order key folds every tied peer into one cumulative value — it runs and looks plausible, but the running total jumps at each tie boundary.
• Rolling window over calendar time, plus minimum periods. "Rolling N days/months" spans a calendar range, not a fixed row count: a ROWS BETWEEN n-1 PRECEDING frame silently measures the wrong span when days are missing. Two sanctioned paths — (a) build a gap-free date spine first (the Series idiom from sql_dialect_notes) so one row exists per calendar unit, then a ROWS BETWEEN n-1 PRECEDING AND CURRENT ROW frame equals the intended span (fully portable); or (b) where the engine supports it, a native calendar range frame — or a date-keyed self-join — expresses the window directly: get the rolling-window idiom from sql_dialect_notes, do not inline it. For minimum periods ("only after N periods of data"), emit NULL until the window is full — guard on COUNT(*) OVER (<same frame>) = N, counting non-null observations instead when "N periods" means N data points rather than N calendar slots. Why: a row-count frame over missing dates measures the wrong span, and a partial early window is not the requested metric.
• Period-over-period. Compare against the prior period with LAG(metric) OVER (PARTITION BY k ORDER BY period); compute growth as (cur - prev) / prev at full precision, rounding only in the final projection (per the round-at-the-end rule below), and guard the divide against a zero or absent prior — e.g. … / NULLIF(prev, 0). Why: without LAG, or ordered against the wrong neighbor, the comparison lands on the wrong period, and an unguarded ratio errors or returns garbage when the prior period is zero or missing.

-- "Each account's running balance over time" — a cumulative sum of net per
-- account, in date order.
-- WRONG: a bare ORDER BY defaults to a RANGE-based frame, so two txns dated the
-- same day share one inflated balance (every tied peer folds into that value).
SELECT account_id, txn_date, net,
       SUM(net) OVER (PARTITION BY account_id ORDER BY txn_date) AS running_balance
FROM account_txns;

-- RIGHT: an explicit ROWS frame accumulates row by row, and a complete tie-breaker
-- (txn_id) makes the order — and the running total — deterministic across ties.
SELECT account_id, txn_date, net,
       SUM(net) OVER (PARTITION BY account_id ORDER BY txn_date, txn_id
                      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_balance
FROM account_txns;

Numeric precision

• Integer division truncates on postgres/sqlite/tsql. The / operator between two integers does integer division on postgres, sqlite, and SQL Server — 5 / 2 is 2, wins / games is 0 — so a rate, share, or SUM(a) / COUNT(*) silently floors to an integer. Cast one operand to a fractional type before dividing: wins * 1.0 / games, CAST(wins AS REAL) / games, or SUM(a)::numeric / COUNT(*), then round at the end. mysql and bigquery already return a fractional result from / (on bigquery prefer SAFE_DIVIDE to also guard a zero denominator).
• Round only at the end. Compute at full precision and round in the final projection, never inside intermediate CTEs. Be explicit about truncation: an integer cast (CAST(x AS INT)) truncates toward zero, so use explicit rounding when rounding is what you mean.
• Macro vs micro average. Match the average to the wording. "Average of per-group averages" is AVG(group_metric); an "overall" or "weighted" average is SUM(numerator) / SUM(denominator). The two diverge whenever group sizes differ.