Fix demo files: remove unsupported patterns, add dates and arithmetic examples

wilmaontherun · claude · wilmaontherun · commit d1b0c953a9a2 · 2026-03-24T22:11:27.000-07:00
- windows_query: remove ROWS BETWEEN frame (unsupported in Feldera)
- aggregations_query: replace PERCENTILE_APPROX with STDDEV (no Feldera equivalent)
- json_combined: replace $.items[0] array path with scalar path (array paths unsupported)
- topk_combined: replace Feldera 3-arg DATEDIFF with Spark 2-arg datediff (Spark input)
- Add dates_combined: to_date / date_format Spark input demo
- Add arithmetic_combined: pmod / try_divide / try_subtract Spark input demo

Co-Authored-By: Claude Sonnet 4.6 &lt;noreply@anthropic.com&gt;
diff --git a/python/felderize/spark/data/demo/aggregations_query.sql b/python/felderize/spark/data/demo/aggregations_query.sql
@@ -4,7 +4,7 @@ SELECT
   COUNT(DISTINCT page_url) AS unique_pages,
   COLLECT_LIST(page_url) AS visited_pages,
   AVG(view_duration) AS avg_duration,
-  PERCENTILE_APPROX(view_duration, 0.95) AS p95_duration,
+  STDDEV(view_duration) AS stddev_duration,
   MIN(view_time) AS first_seen,
   MAX(view_time) AS last_seen,
   COUNT(CASE WHEN device_type = 'mobile' THEN 1 END) AS mobile_views
diff --git a/python/felderize/spark/data/demo/arithmetic_combined.sql b/python/felderize/spark/data/demo/arithmetic_combined.sql
@@ -0,0 +1,18 @@
+-- Demo: pmod, try_divide, try_subtract
+-- Covers: positive modulo, NULL-on-zero division, safe subtraction
+
+CREATE TABLE metrics (
+  metric_id  BIGINT,
+  value      BIGINT,
+  bucket     BIGINT,
+  divisor    BIGINT,
+  baseline   BIGINT
+) USING parquet;
+
+CREATE OR REPLACE TEMP VIEW metric_results AS
+SELECT
+  metric_id,
+  pmod(value, bucket)           AS bucketed,
+  try_divide(value, divisor)    AS safe_ratio,
+  try_subtract(value, baseline) AS delta
+FROM metrics;
diff --git a/python/felderize/spark/data/demo/dates_combined.sql b/python/felderize/spark/data/demo/dates_combined.sql
@@ -0,0 +1,17 @@
+-- Demo: to_date and date_format patterns
+-- Covers: date string parsing, date-only formatting, time component formatting
+
+CREATE TABLE raw_events (
+  event_id    BIGINT,
+  user_id     BIGINT,
+  event_date  STRING,
+  occurred_at TIMESTAMP
+) USING parquet;
+
+CREATE OR REPLACE TEMP VIEW event_labels AS
+SELECT
+  event_id,
+  to_date(event_date, 'yyyy-MM-dd')            AS parsed_date,
+  date_format(occurred_at, 'yyyy-MM-dd')       AS day_label,
+  date_format(occurred_at, 'yyyy-MM-dd HH:mm') AS minute_label
+FROM raw_events;
diff --git a/python/felderize/spark/data/demo/json_combined.sql b/python/felderize/spark/data/demo/json_combined.sql
@@ -27,7 +27,7 @@ SELECT
   get_json_object(payload, '$.amount')      AS amount_str,
   CAST(get_json_object(payload, '$.amount') AS DOUBLE) AS amount,
   get_json_object(payload, '$.currency')    AS currency,
-  get_json_object(payload, '$.items[0]')    AS first_item
+  get_json_object(payload, '$.item_type')   AS item_type
 FROM raw_events;
 
 -- Aggregate per user, parsing nested JSON
diff --git a/python/felderize/spark/data/demo/topk_combined.sql b/python/felderize/spark/data/demo/topk_combined.sql
@@ -1,5 +1,5 @@
--- Demo: TopK pattern, QUALIFY clause, and TIMESTAMPDIFF
--- Covers: ROW_NUMBER/RANK in subquery (TopK), QUALIFY, DATEDIFF → TIMESTAMPDIFF
+-- Demo: TopK pattern, QUALIFY clause, and datediff
+-- Covers: ROW_NUMBER in subquery (TopK), QUALIFY, datediff → DATEDIFF(unit, start, end)
 
 CREATE TABLE IF NOT EXISTS employee (
   emp_id STRING NOT NULL,
@@ -40,11 +40,11 @@ SELECT
 FROM review
 QUALIFY ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY review_date DESC) = 1;
 
--- Employee tenure in years using DATEDIFF
+-- Employee tenure in years using datediff
 CREATE OR REPLACE TEMP VIEW employee_tenure AS
 SELECT
   emp_id,
   dept,
   hire_date,
-  DATEDIFF(year, hire_date, CURRENT_TIMESTAMP) AS tenure_years
+  datediff(CURRENT_DATE, hire_date) AS tenure_days
 FROM employee;
diff --git a/python/felderize/spark/data/demo/windows_query.sql b/python/felderize/spark/data/demo/windows_query.sql
@@ -4,6 +4,5 @@ SELECT
   account_id,
   amount,
   LAG(amount) OVER (PARTITION BY account_id ORDER BY txn_time) AS prev_amount,
-  SUM(amount) OVER (PARTITION BY account_id ORDER BY txn_time
-    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
+  SUM(amount) OVER (PARTITION BY account_id) AS total_account_amount
 FROM transactions;
