more demo files

wilmaontherun · wilmaontherun · commit b10f7a471783 · 2026-03-19T15:40:32.000-07:00
diff --git a/python/felderize/spark/data/demo/json_combined.sql b/python/felderize/spark/data/demo/json_combined.sql
@@ -0,0 +1,43 @@
+-- Demo: JSON parsing and VARIANT field access
+-- Covers: get_json_object, from_json, json_tuple → PARSE_JSON + bracket access
+
+CREATE TABLE IF NOT EXISTS raw_events (
+  event_id STRING NOT NULL,
+  event_type STRING,
+  payload STRING,
+  occurred_at TIMESTAMP,
+  CONSTRAINT raw_events_pk PRIMARY KEY (event_id)
+)
+USING DELTA;
+
+CREATE TABLE IF NOT EXISTS user_profiles (
+  user_id STRING NOT NULL,
+  metadata STRING,
+  CONSTRAINT user_profiles_pk PRIMARY KEY (user_id)
+)
+USING DELTA;
+
+-- Extract typed fields from a JSON payload column
+CREATE OR REPLACE TEMP VIEW parsed_events AS
+SELECT
+  event_id,
+  event_type,
+  occurred_at,
+  get_json_object(payload, '$.user_id')     AS user_id,
+  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
+FROM raw_events;
+
+-- Aggregate per user, parsing nested JSON
+CREATE OR REPLACE TEMP VIEW user_event_summary AS
+SELECT
+  get_json_object(payload, '$.user_id') AS user_id,
+  COUNT(*)                              AS event_count,
+  SUM(CAST(get_json_object(payload, '$.amount') AS DOUBLE)) AS total_amount,
+  MIN(occurred_at)                      AS first_event,
+  MAX(occurred_at)                      AS last_event
+FROM raw_events
+WHERE event_type = 'purchase'
+GROUP BY get_json_object(payload, '$.user_id');
diff --git a/python/felderize/spark/data/demo/topk_combined.sql b/python/felderize/spark/data/demo/topk_combined.sql
@@ -0,0 +1,50 @@
+-- Demo: TopK pattern, QUALIFY clause, and TIMESTAMPDIFF
+-- Covers: ROW_NUMBER/RANK in subquery (TopK), QUALIFY, DATEDIFF → TIMESTAMPDIFF
+
+CREATE TABLE IF NOT EXISTS employee (
+  emp_id STRING NOT NULL,
+  dept STRING,
+  salary DECIMAL(12, 2),
+  hire_date TIMESTAMP,
+  CONSTRAINT employee_pk PRIMARY KEY (emp_id)
+)
+USING DELTA;
+
+CREATE TABLE IF NOT EXISTS review (
+  emp_id STRING NOT NULL,
+  review_date TIMESTAMP NOT NULL,
+  score INT,
+  CONSTRAINT review_pk PRIMARY KEY (emp_id, review_date)
+)
+USING DELTA;
+
+-- Top 3 earners per department using ROW_NUMBER subquery (TopK pattern)
+CREATE OR REPLACE TEMP VIEW top_earners_per_dept AS
+SELECT dept, emp_id, salary, rank_in_dept
+FROM (
+  SELECT
+    dept,
+    emp_id,
+    salary,
+    ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rank_in_dept
+  FROM employee
+) ranked
+WHERE rank_in_dept <= 3;
+
+-- Latest review per employee using QUALIFY
+CREATE OR REPLACE TEMP VIEW latest_review AS
+SELECT
+  emp_id,
+  review_date,
+  score
+FROM review
+QUALIFY ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY review_date DESC) = 1;
+
+-- 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
+FROM employee;
