mirror of
https://github.com/Kaelio/ktx.git
synced 2026-06-07 07:55:13 +02:00
60 lines
2.1 KiB
Bash
Executable file
60 lines
2.1 KiB
Bash
Executable file
#!/usr/bin/env bash
|
|
# Complex CTE Runtime Join Demo
|
|
#
|
|
# Shows how two SQL sources with internal CTEs (customer_lifetime_value, churn_risk)
|
|
# are joined at runtime through the join graph to a dimension table (regions),
|
|
# triggering chasm trap detection and aggregate locality.
|
|
|
|
set -euo pipefail
|
|
cd "$(dirname "$0")/.."
|
|
|
|
MODEL="demos/complex_cte_join.yaml"
|
|
|
|
echo "============================================"
|
|
echo " Demo 1: Chasm Trap — Two CTE metrics + regions dimension"
|
|
echo "============================================"
|
|
echo ""
|
|
echo "Query: Average LTV and average churn risk by region,"
|
|
echo " for customers with churn score > 0.7"
|
|
echo ""
|
|
|
|
echo '{
|
|
"measures": ["customer_lifetime_value.avg_ltv", "churn_risk.avg_risk"],
|
|
"dimensions": ["regions.name"],
|
|
"filters": ["churn_risk.score > 0.7"]
|
|
}' | uv run python -m semantic_layer.cli --model "$MODEL" --json --plan
|
|
|
|
echo ""
|
|
echo "============================================"
|
|
echo " Demo 2: Single CTE metric enriched with regions"
|
|
echo "============================================"
|
|
echo ""
|
|
echo "Query: LTV breakdown by region and customer segment,"
|
|
echo " only customers with 6+ active months"
|
|
echo ""
|
|
|
|
echo '{
|
|
"measures": [
|
|
"customer_lifetime_value.avg_ltv",
|
|
"customer_lifetime_value.avg_active_months",
|
|
{"expr": "count(customer_lifetime_value.customer_id)", "name": "customer_count"}
|
|
],
|
|
"dimensions": ["regions.name", "customers.segment"],
|
|
"filters": ["customer_lifetime_value.active_months >= 6"]
|
|
}' | uv run python -m semantic_layer.cli --model "$MODEL" --json --plan
|
|
|
|
echo ""
|
|
echo "============================================"
|
|
echo " Demo 3: Runtime aggregation on CTE columns + cross-source join"
|
|
echo "============================================"
|
|
echo ""
|
|
echo "Query: P90 churn score and max LTV by region continent"
|
|
echo ""
|
|
|
|
echo '{
|
|
"measures": [
|
|
{"expr": "percentile(churn_risk.score, 0.9)", "name": "p90_churn"},
|
|
{"expr": "max(customer_lifetime_value.ltv_estimate)", "name": "max_ltv"}
|
|
],
|
|
"dimensions": ["regions.continent"]
|
|
}' | uv run python -m semantic_layer.cli --model "$MODEL" --json --plan
|