{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Featurizer Tutorial: Custom Primitives\n", "\n", "This notebook demonstrates how to extend Featurizer with custom aggregation and transformation primitives using a financial analytics scenario.\n", "\n", "> **Note:** Since the primitives expansion (commit `6b25339`), several primitives demonstrated here — Median, P95, Range, Log, ZScore — are now built-in. This tutorial is retained to show the **registration pattern** for creating truly custom primitives." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 1. Built-in Primitives That Cover Common Needs\n", "\n", "Before writing custom primitives, check if your need is already covered. Featurizer now ships with 43 aggregations and 71 transformations." ] }, { "cell_type": "code", "execution_count": 1, "metadata": { "execution": { "iopub.execute_input": "2026-06-21T19:29:56.767327Z", "iopub.status.busy": "2026-06-21T19:29:56.767155Z", "iopub.status.idle": "2026-06-21T19:29:56.992453Z", "shell.execute_reply": "2026-06-21T19:29:56.992038Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Built-in aggregations (69):\n", " acf_1, age_in_system, all, any, bbox_area, burstiness, cosinor_amplitude_weekly, count, cross_type_latency, cv, distance_travelled, entropy, event_rate, first_passage_time, gap_cv, gap_max, gap_mean, gap_min, gap_stddev, geometric_mean, gini, harmonic_mean, hhi, inter_event_hazard_proxy, iqr, kl_drift, kurtosis, longest_streak, markov_conditional_entropy, max, max_transition_prob, mean, mean_deviation, median, median_absolute_deviation, min, min_max_scale, mode, ngram_2_freq, ngram_3_freq, nunique, p10, p25, p75, p90, p95, p99, radius_of_gyration, range, recency, recurrence_interval, rework_count, right_censoring_indicator, sequence_entropy, skewness, spatial_std, state_volatility, stddev, sum, tenure, theil, time_in_current_state, time_span, transition_matrix_summary, trimmed_mean_10, variance, variance_ratio, wasserstein_drift, z_score\n", "\n", "Built-in transformations (83):\n", " abs, avg_word_length, caps_ratio, cbrt, cdf, ceil, century, cross_entity_percentile, cross_entity_zscore, cum_count, cum_max, cum_mean, cum_min, cum_sum, cumprod, cusum, cyclic_day, cyclic_hour, cyclic_month, daily_bin, day, diff, diff2, diff3, digit_ratio, dom, dow, doy, ema_14, ema_7, exclamation_count, exp, first, floor, holt_winters_level_14, holt_winters_level_7, holt_winters_trend_14, holt_winters_trend_7, hour, hourly_bin, identity, in_array, is_null, lag_1, lag_3, lag_7, last, ln, log, mean_shift_ratio_14, mean_shift_ratio_7, month, ntile, num_chars, num_sentences, num_words, pct_change_1, pct_change_3, percent_rank, previous, punct_ratio, quarter, question_count, rolling_iqr_14, rolling_iqr_7, rolling_mean_14, rolling_mean_3, rolling_mean_7, rolling_median_5, rolling_median_7, rolling_std_14, rolling_std_3, rolling_std_7, sign, sqrt, time_since_previous, trunc, tz, tz_offset, unique_word_ratio, week, week_of_year, year\n" ] } ], "source": [ "import sys\n", "from pathlib import Path\n", "\n", "sys.path.insert(0, str(Path.cwd().parent.parent))\n", "\n", "from featurizer.primitives.utils import list_aggregations, list_transformations\n", "\n", "aggs = sorted(list_aggregations())\n", "transforms = sorted(list_transformations())\n", "\n", "print(f\"Built-in aggregations ({len(aggs)}):\")\n", "print(f\" {', '.join(aggs)}\")\n", "print(f\"\\nBuilt-in transformations ({len(transforms)}):\")\n", "print(f\" {', '.join(transforms)}\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Built-in Equivalents of This Example's Custom Primitives\n", "\n", "| Custom (this example) | Built-in name | Type |\n", "|---|---|---|\n", "| `Median` | `median` | aggregation |\n", "| `Percentile95` | `p95` | aggregation |\n", "| `Range` | `range` | aggregation |\n", "| `Log` | `ln` | transformation |\n", "| `ZScore` | `cross_entity_zscore` | transformation |\n", "\n", "To use built-in versions, no custom code is needed — just reference them by name." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 2. The Custom Registration Pattern\n", "\n", "When you need a truly custom primitive (e.g., domain-specific financial metrics), Featurizer provides a registration API. Let's walk through the pattern." ] }, { "cell_type": "code", "execution_count": 2, "metadata": { "execution": { "iopub.execute_input": "2026-06-21T19:29:57.004436Z", "iopub.status.busy": "2026-06-21T19:29:57.004317Z", "iopub.status.idle": "2026-06-21T19:29:57.006077Z", "shell.execute_reply": "2026-06-21T19:29:57.005755Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\"\"\"Custom aggregation and transformation primitives for financial analytics.\n", "\n", "These demonstrate the current primitive API and emit PostgreSQL-valid SQL:\n", "\n", "- An **aggregation** subclasses ``Aggregator`` (or a richer base such as\n", " ``OrderedSetAggregator``) and overrides ``_build_aggregate_expression`` to\n", " return the SQL aggregate; the base wraps it into a ``Feature``. Interval\n", " variants pass ``interval`` so the expression can add the ``daterange`` filter.\n", "- A **transformation** subclasses ``Transformer`` and overrides\n", " ``_build_transformer_call`` to return a scalar SQL expression over the\n", " current row; the base wraps it into a ``Feature``.\n", "\n", "Register *instances* (not classes) via ``register_aggregation`` /\n", "``register_transformer``, then select them by name in ``config.yaml``.\n", "\"\"\"\n", "\n", "from featurizer.primitives.aggregations import Aggregator, OrderedSetAggregator\n", "from featurizer.primitives.transformations import Transformer\n", "from featurizer.primitives.utils import register_aggregation, register_transformer\n", "\n", "# ============================================================================\n", "# Custom Aggregations\n", "# ============================================================================\n", "\n", "\n", "class Range(Aggregator):\n", " \"\"\"Spread of a value: ``max(x) - min(x)``.\n", "\n", " PostgreSQL's ``FILTER`` clause attaches to a single aggregate, so for an\n", " interval window each of ``max`` and ``min`` is filtered independently.\n", " \"\"\"\n", "\n", " def __init__(self):\n", " super().__init__(name=\"range\")\n", "\n", " def _agg(self, fn, feature, interval):\n", " expr = f\"{fn}({feature.name})\"\n", " if interval and feature.entity and feature.entity.temporal_ix:\n", " event_date = feature.entity.temporal_ix.name\n", " daterange = (\n", " f\"daterange((aod.as_of_date - interval '{interval}')::date, \"\n", " f\"aod.as_of_date::date, '[]')\"\n", " )\n", " expr += f\" filter (where {daterange} @> {event_date}::date)\"\n", " return expr\n", "\n", " def _build_aggregate_expression(self, feature, interval=None):\n", " return f\"({self._agg('max', feature, interval)} - {self._agg('min', feature, interval)})\"\n", "\n", "\n", "class Percentile95(OrderedSetAggregator):\n", " \"\"\"95th percentile via the ordered-set base (handles WITHIN GROUP + interval).\"\"\"\n", "\n", " def __init__(self):\n", " super().__init__(name=\"p95\", aggregate=\"percentile_cont\", direct_argument=0.95)\n", "\n", "\n", "# ============================================================================\n", "# Custom Transformations\n", "# ============================================================================\n", "\n", "\n", "class Log1p(Transformer):\n", " \"\"\"Natural log with a +1 offset: ``ln(x + 1)``.\n", "\n", " Guarded with a CASE: the logarithm is undefined for ``x + 1 <= 0``, and this\n", " data has negative values (withdrawals), so those map to NULL rather than\n", " raising. (Without the guard PostgreSQL errors on the whole query.)\n", " \"\"\"\n", "\n", " def __init__(self):\n", " super().__init__(name=\"log1p\")\n", "\n", " def _build_transformer_call(self, feature):\n", " return f\"case when {feature.name} + 1 > 0 then ln({feature.name} + 1) end\"\n", "\n", "\n", "class ZScore(Transformer):\n", " \"\"\"Z-score standardization across the rows in scope (``OVER ()``).\"\"\"\n", "\n", " def __init__(self):\n", " super().__init__(name=\"zscore\")\n", "\n", " def _build_transformer_call(self, feature):\n", " return (\n", " f\"(({feature.name} - avg({feature.name}) over ()) \"\n", " f\"/ nullif(stddev({feature.name}) over (), 0))\"\n", " )\n", "\n", "\n", "class BinCount(Transformer):\n", " \"\"\"Discretize into 5 equal-width bins (0–4) across the rows in scope.\"\"\"\n", "\n", " def __init__(self):\n", " super().__init__(name=\"bin\", output_type=\"numeric\")\n", "\n", " def _build_transformer_call(self, feature):\n", " f = feature.name\n", " return (\n", " f\"case when {f} is null then null \"\n", " f\"else cast(floor(5 * ({f} - min({f}) over ()) \"\n", " f\"/ nullif(max({f}) over () - min({f}) over (), 0)) as integer) end\"\n", " )\n", "\n", "\n", "# ============================================================================\n", "# Registration\n", "# ============================================================================\n", "\n", "\n", "def register_all_custom_primitives():\n", " \"\"\"Register all custom primitives with the feature system.\"\"\"\n", " register_aggregation(\"range\", Range())\n", " register_aggregation(\"p95\", Percentile95())\n", "\n", " register_transformer(\"log1p\", Log1p())\n", " register_transformer(\"zscore\", ZScore())\n", " register_transformer(\"bin\", BinCount())\n", "\n", " print(\"✓ Registered custom primitives:\")\n", " print(\" Aggregations: range, p95\")\n", " print(\" Transformations: log1p, zscore, bin\")\n", "\n" ] } ], "source": [ "with open(\"custom_primitives.py\") as f:\n", " print(f.read())" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Key Steps:\n", "1. **Subclass** `Aggregation` or `Transformation` from `featurizer.primitives.abstractions`\n", "2. **Implement** `to_sql(feature, alias)` returning a SQL expression\n", "3. **Register** via `register_aggregation()` or `register_transformation()`\n", "4. **Call** `register_all_custom_primitives()` before creating the Featurizer\n", "\n", "**Important**: Transformations must return new `Feature` instances (never mutate) to preserve hashing semantics." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 3. Setup and Registration" ] }, { "cell_type": "code", "execution_count": 3, "metadata": { "execution": { "iopub.execute_input": "2026-06-21T19:29:57.007079Z", "iopub.status.busy": "2026-06-21T19:29:57.007016Z", "iopub.status.idle": "2026-06-21T19:29:57.015423Z", "shell.execute_reply": "2026-06-21T19:29:57.015081Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "✓ Registered custom primitives:\n", " Aggregations: range, p95\n", " Transformations: log1p, zscore, bin\n" ] } ], "source": [ "import sys\n", "from pathlib import Path\n", "\n", "# This tutorial is database-free: it loads the config and inspects the\n", "# synthesized features and the generated SQL — none of which touch a\n", "# database. To execute the features against PostgreSQL, run\n", "# `just example 04-custom-primitives` (create_data.py + run_example.py).\n", "sys.path.insert(0, str(Path.cwd().parent.parent))\n", "\n", "# Custom primitives must be registered BEFORE creating the Featurizer.\n", "from custom_primitives import register_all_custom_primitives\n", "\n", "register_all_custom_primitives()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 4. Create Featurizer with Custom Primitives" ] }, { "cell_type": "code", "execution_count": 4, "metadata": { "execution": { "iopub.execute_input": "2026-06-21T19:29:57.016442Z", "iopub.status.busy": "2026-06-21T19:29:57.016388Z", "iopub.status.idle": "2026-06-21T19:29:57.022832Z", "shell.execute_reply": "2026-06-21T19:29:57.022353Z" } }, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "\u001b[32m2026-06-21 13:29:57.017\u001b[0m | \u001b[34m\u001b[1mDEBUG \u001b[0m | \u001b[36mfeaturizer.planner\u001b[0m:\u001b[36mplan\u001b[0m:\u001b[36m230\u001b[0m - \u001b[34m\u001b[1mStarting feature build for target accounts\u001b[0m\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "\u001b[32m2026-06-21 13:29:57.018\u001b[0m | \u001b[34m\u001b[1mDEBUG \u001b[0m | \u001b[36mfeaturizer.planner\u001b[0m:\u001b[36m_build_features\u001b[0m:\u001b[36m256\u001b[0m - \u001b[34m\u001b[1mbuild_features(accounts) depth=0\u001b[0m\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "\u001b[32m2026-06-21 13:29:57.018\u001b[0m | \u001b[34m\u001b[1mDEBUG \u001b[0m | \u001b[36mfeaturizer.planner\u001b[0m:\u001b[36m_build_features\u001b[0m:\u001b[36m256\u001b[0m - \u001b[34m\u001b[1mbuild_features(transactions) depth=1\u001b[0m\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "\u001b[32m2026-06-21 13:29:57.018\u001b[0m | \u001b[1mINFO \u001b[0m | \u001b[36mfeaturizer.planner\u001b[0m:\u001b[36m_build_features\u001b[0m:\u001b[36m276\u001b[0m - \u001b[1mMaximum recursion depth reached at depth 2; materializing transactions without traversing further.\u001b[0m\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "\u001b[32m2026-06-21 13:29:57.018\u001b[0m | \u001b[34m\u001b[1mDEBUG \u001b[0m | \u001b[36mfeaturizer.planner\u001b[0m:\u001b[36m_build_aggregations\u001b[0m:\u001b[36m1014\u001b[0m - \u001b[34m\u001b[1mProcessing backward relationship Entity(accounts).account_id -> Entity(transactions).account_id\u001b[0m\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "\u001b[32m2026-06-21 13:29:57.019\u001b[0m | \u001b[33m\u001b[1mWARNING \u001b[0m | \u001b[36mfeaturizer.planner\u001b[0m:\u001b[36m_apply_direct_roles\u001b[0m:\u001b[36m1155\u001b[0m - \u001b[33m\u001b[1mDirect variable 'accounts.account_type' (type: categorical) will pass through as a raw string column and is likely to crash a downstream encoder. Set role: categorical (with a declared vocabulary or a PostgreSQL ENUM) to one-hot encode it, or role: identifier to exclude it.\u001b[0m\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "Target entity: accounts\n", "Max depth: 2\n", "Intervals: ['P7D', 'P30D']\n", "Generated features: 187\n" ] } ], "source": [ "from featurizer import Featurizer\n", "\n", "featurizer = Featurizer(\"config.yaml\")\n", "\n", "print(f\"Target entity: {featurizer.target.alias}\")\n", "print(f\"Max depth: {featurizer.max_depth}\")\n", "print(f\"Intervals: {featurizer.intervals}\")\n", "\n", "target_features = featurizer.features[featurizer.target.alias]\n", "print(f\"Generated features: {len(target_features)}\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 5. Examining Features from Custom Primitives\n", "\n", "Let's see which features use our custom primitives." ] }, { "cell_type": "code", "execution_count": 5, "metadata": { "execution": { "iopub.execute_input": "2026-06-21T19:29:57.023771Z", "iopub.status.busy": "2026-06-21T19:29:57.023710Z", "iopub.status.idle": "2026-06-21T19:29:57.025608Z", "shell.execute_reply": "2026-06-21T19:29:57.025354Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Features using custom primitives: 171\n", " - \"BIN(accounts.COUNT(transactions.transaction_date))\"\n", " - \"BIN(accounts.COUNT(transactions.transaction_date|interval=P30D))\"\n", " - \"BIN(accounts.COUNT(transactions.transaction_date|interval=P7D))\"\n", " - \"BIN(accounts.COUNT(transactions.transaction_id))\"\n", " - \"BIN(accounts.COUNT(transactions.transaction_id|interval=P30D))\"\n", " - \"BIN(accounts.COUNT(transactions.transaction_id|interval=P7D))\"\n", " - \"BIN(accounts.COUNT(transactions.transaction_type))\"\n", " - \"BIN(accounts.COUNT(transactions.transaction_type|interval=P30D))\"\n", " - \"BIN(accounts.COUNT(transactions.transaction_type|interval=P7D))\"\n", " - \"BIN(accounts.MEAN(transactions.BIN(transactions.amount)))\"\n", " - \"BIN(accounts.MEAN(transactions.BIN(transactions.amount)|interval=P30D))\"\n", " - \"BIN(accounts.MEAN(transactions.BIN(transactions.amount)|interval=P7D))\"\n", " - \"BIN(accounts.MEAN(transactions.LOG1P(transactions.amount)))\"\n", " - \"BIN(accounts.MEAN(transactions.LOG1P(transactions.amount)|interval=P30D))\"\n", " - \"BIN(accounts.MEAN(transactions.LOG1P(transactions.amount)|interval=P7D))\"\n" ] } ], "source": [ "custom_names = [\"median\", \"p95\", \"range\", \"log\", \"zscore\", \"bin\"]\n", "custom_features = [\n", " f for f in target_features if any(prim in f.name.lower() for prim in custom_names)\n", "]\n", "\n", "print(f\"Features using custom primitives: {len(custom_features)}\")\n", "for f in sorted(custom_features, key=lambda x: x.name)[:15]:\n", " print(f\" - {f.name}\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 6. SQL Comparison: Custom vs Built-in\n", "\n", "Let's compare the SQL generated by a custom primitive vs its built-in equivalent." ] }, { "cell_type": "code", "execution_count": 6, "metadata": { "execution": { "iopub.execute_input": "2026-06-21T19:29:57.026450Z", "iopub.status.busy": "2026-06-21T19:29:57.026400Z", "iopub.status.idle": "2026-06-21T19:29:57.028333Z", "shell.execute_reply": "2026-06-21T19:29:57.027949Z" } }, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "\u001b[32m2026-06-21 13:29:57.026\u001b[0m | \u001b[34m\u001b[1mDEBUG \u001b[0m | \u001b[36mfeaturizer.sql\u001b[0m:\u001b[36mrender\u001b[0m:\u001b[36m40\u001b[0m - \u001b[34m\u001b[1mRendered SQL for target 'accounts': 5 CTEs, 60572 chars\u001b[0m\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "Generated SQL (with custom primitives):\n", "================================================================================\n", "\n", " select aod.as_of_date, t.*\n", " from as_of_dates as aod\n", " cross join lateral (\n", "\n", " with\n", "\n", " \n", " -- sythetize aggregations and direct features for transactions\n", " transactions_synth as (\n", " select\n", " transactions.transaction_id, transactions.transaction_date, transactions.account_id, amount, transaction_type\n", " from transactions\n", " \n", " \n", " )\n", " ,\n", " -- transform transactions\n", " transactions_transform as (\n", " select\n", " transaction_id, transaction_date, account_id, case when amount is null then null else cast(floor(5 * (amount - min(amount) over ()) / nullif(max(amount) over () - min(amount) over (), 0)) as integer) end as \"BIN(transactions.amount)\" , case when amount + 1 > 0 then ln(amount + 1) end as \"LOG1P(transactions.amount)\" , ((amount - avg(amount) over ()) / nullif(stddev(amount) over (), 0)) as \"ZSCORE(transactions.amount)\" , amount as amount, transaction_type as transaction_type\n", " from transactions_synth _ego\n", " )\n", " ,\n", " -- Aggregate for accounts\n", " transactions_aggs_for_accounts as (\n", " select\n", " transactions_transform.account_id,\n", " count( transaction_date ) as \"COUNT(transactions.transaction_date)\" ,count( transaction_date ) filter (where daterange((aod.as_of_date - interval 'P30D')::date, aod.as_of_date::date, '[]') @> transaction_date::date) as \"COUNT(transactions.transaction_date|interval=P30D)\" ,count( transaction_date ) filter (where daterange((aod.as_of_date - interval 'P7D')::date, aod.as_of_date::date, '[]') @> transaction_date::date) as \"COUNT(transactions.transaction_date|interval=P7D)\" ,count( transaction_id ) as \"COUNT(transactions.transaction_id)\" ,count( transaction_id ) filter (where daterange((aod.as_of_date - interval 'P30D')::date, aod.as_of_date::date, '[]') @> transaction_date::date) as \"COUNT(transactions.transaction_id|interval=P30D)\" ,count( transaction_id ) filter (where daterange((aod.as_of_date - interval 'P7D')::date, aod.as_of_date::date, '[]') @> transaction_date::date) as \"COUNT(transactions.transaction_id|interval=P7D)\" ,count( transaction_type ) as \"COUNT(transactions.transaction_type)\" ,count( transaction_type ) filter (where daterange((aod.as_of_date - interval 'P30D')::date, aod.as_of_date::date, '[]') @> transaction_date::date) as \"COUNT(transactions.transaction_type|interval=P30D)\" ,count( transaction_type ) filter (where daterange((aod.as_of_date - interval 'P7D')::date, aod.as_of_date::date, '[]') @> transaction_date::date) as \"COUNT(transactions.transaction_type|interval=P7D)\" ,avg( \"BIN(transactions.amount)\" ) as \"MEAN(transactions.BIN(transactions.amount))\" ,avg( \"BIN(transactions.amount)\" ) filter (where daterange((aod.as_of_date - interval 'P30D')::date, aod.as_of_date::date, '[]') @> transaction_date::date) as \"MEAN(transactions.BIN(transactions.amount)|interval=P30D)\" ,avg( \"BIN(transactions.amount)\" ) filter (where daterange((aod.as_of_date - interval 'P7D')::date, aod.as_of_date::date, '[]') @> transaction_date::date) as \"MEAN(transactions.BIN(transactions.amount)|interval=P7D)\" ,avg( \"LOG1P(transactions.amount)\" ) as \"MEAN(transactions.LOG1P(transactions.amount))\" ,avg( \"LOG1P(transactions.amount)\" ) filter (where daterange((aod.as_of_date - interval 'P30D')::date, aod.as_of_date::date, '[]') @> transaction_date::date) as \"MEAN(transactions.LOG1P(transactions.amount)|interval=P30D)\" ,avg( \"LOG1P(transactions.amount)\" ) filter (where daterange((aod.as_of_date - interval 'P7D')::date, aod.as_of_date::date, '[]') @> transaction_date::date) as \"MEAN(transactions.LOG1P(transactions.amount)|interval=P7D)\" ,avg( \"ZSCORE(transactions.amount)\" ) as \"MEAN(transactions.ZSCORE(transactions.amount))\" ,avg( \"ZSCORE(transactions.amount)\" ) filter (where daterange((aod.as_of_date - interval 'P30D')::date, aod.as_of_date::date, '[]') @> transaction_date::date) as \"MEAN(transactions.ZSCORE(transactions.amount)|interval=P30D)\" ,avg( \"ZSCORE(transactions.amount)\" ) filter (where daterange((aod.as_of_date - interval 'P7D')::date, aod.as_of_date::date, '[]') @> transaction_date::date) as \"MEAN(transactions.ZSCORE(transactions.amount)|interval=P7D)\" ,avg( amount ) as \"MEAN(transactions.amount)\" ,avg( amount ) filter (where daterange((aod.as_of_date - interval 'P30D')::date, aod.as_of_date::date, '[]') @> transaction_date::date) as \"MEAN(transactions.amount|interval=P30D)\" ,avg( amount ) filter (where daterange((aod.as_of_date - interval 'P7D')::date, aod.as_of_date::date, '[]') @> transaction_date::date) as \"MEAN(transactions.amount|interval=P7D)\" ,percentile_cont(0.95) within group(order by \"BIN(transactions.amount)\") as \"P95(transactions.BIN(transactions.amount))\" ,percentile_cont(0.95) within group(order by \"BIN(transactions.amount)\") filter (where daterange((aod.as_of_date - interval 'P30D')::date, aod.as_of_date::date, '[]') @> transaction_date::date) as \"P95(transactions.BIN(transactions.amount)|interval=P30D)\" ,percentile_cont(0.95) within group(order by \"BIN(transactions.amount)\") filter (where daterange((aod.as_of_date - interval 'P7D')::date, aod.as_of_date::date, '[]') @> transaction_date::date) as \"P95(transactions.BIN(transactions.amount)|interval=P7D)\" ,percentile_cont(0.95) within group(order by \"LOG1P(transactions.amount)\") as \"P95(transactions.LOG1P(transactions.amount))\" ,percentile_cont(0.95) within group(order by \"LOG1P(transactions.amount)\") filter (where daterange((aod.as_of_date - interval 'P30D')::date, aod.as_of_date::date, '[]') @> transaction_date::date) as \"P95(transactions.LOG1P(transactions.amount)|interval=P30D)\" ,percentile_cont(0.95) within group(order by \"LOG1P(transactions.amount)\") filter (where daterange((aod.as_of_date - interval 'P7D')::date, aod.as_of_date::date, '[]') @> transaction_date::date) as \"P95(transactions.LOG1P(transactions.amount)|interval=P7D)\" ,percentile_cont(0.95) within group(order by \"ZSCORE(transactions.amount)\") as \"P95(transactions.ZSCORE(transactions.amount))\" ,percentile_cont(0.95) within group(order by \"ZSCORE(transactions.amount)\") filter (where daterange((aod.as_of_date - interval 'P30D')::date, aod.as_of_date::date, '[]') @> transaction_date::date) as \"P95(transactions.ZSCORE(transactions.amount)|interval=P30D)\" ,percentile_cont(0.95) within group(order by \"ZSCORE(transactions.amount)\") filter (where daterange((aod.as_of_date - interval 'P7D')::date, aod.as_of_date::date, '[]') @> transaction_date::date) as \"P95(transactions.ZSCORE(transactions.amount)|interval=P7D)\" ,percentile_cont(0.95) within group(order by amount) as \"P95(transactions.amount)\" ,percentile_cont(0.95) within group(order by amount) filter (where daterange((aod.as_of_date - interval 'P30D')::date, aod.as_of_date::date, '[]') @> transaction_date::date) as \"P95(transactions.amount|interval=P30D)\" ,percentile_cont(0.95) within group(order by amount) filter (where daterange((aod.as_of_date - interval 'P7D')::date, aod.as_of_date::date, '[]') @> transaction_date::date) as \"P95(transactions.amount|interval=P7D)\" ,(max(\"BIN(transactions.amount)\") - min(\"BIN(transactions.amount)\")) as \"RANGE(transactions.BIN(transactions.amount))\" ,(max(\"BIN(transactions.amount)\") filter (where daterange((aod.as_of_date - interval 'P30D')::date, aod.as_of_date::date, '[]') @> transaction_date::date) - min(\"BIN(transactions.amount)\") filter (where daterange((aod.as_of_date - interval 'P30D')::date, aod.as_of_date::date, '[]') @> transaction_date::date)) as \"RANGE(transactions.BIN(transactions.amount)|interval=P30D)\" ,(max(\"BIN(transactions.amount)\") filter (where daterange((aod.as_of_date - interval 'P7D')::date, aod.as_of_date::date, '[]') @> transaction_date::date) - min(\"BIN(transactions.amount)\") filter (where daterange((aod.as_of_date - interval 'P7D')::date, aod.as_of_date::date, '[]') @> transaction_date::date)) as \"RANGE(transactions.BIN(transactions.amount)|interval=P7D)\" ,(max(\"LOG1P(transactions.amount)\") - min(\"LOG1P(transactions.amount)\")) as \"RANGE(transactions.LOG1P(transactions.amount))\" ,(max(\"LOG1P(transactions.amount)\") filter (where daterange((aod.as_of_date - interval 'P30D')::date, aod.as_of_date::date, '[]') @> transaction_date::date) - min(\"LOG1P(transactions.amount)\") filter (where daterange((aod.as_of_date - interval 'P30D')::date, aod.as_of_date::date, '[]') @> transaction_date::date)) as \"RANGE(transactions.LOG1P(transactions.amount)|interval=P30D)\" ,(max(\"LOG1P(transactions.amount)\") filter (where daterange((aod.as_of_date - interval 'P7D')::date, aod.as_of_date::date, '[]') @> transaction_date::date) - min(\"LOG1P(transactions.amount)\") filter (where daterange((aod.as_of_date - interval 'P7D')::date, aod.as_of_date::date, '[]') @> transaction_date::date)) as \"RANGE(transactions.LOG1P(transactions.amount)|interval=P7D)\" ,(max(\"ZSCORE(transactions.amount)\") - min(\"ZSCORE(transactions.amount)\")) as \"RANGE(transactions.ZSCORE(transactions.amount))\" ,(max(\"ZSCORE(transactions.amount)\") filter (where daterange((aod.as_of_date - interval 'P30D')::date, aod.as_of_date::date, '[]') @> transaction_date::date) - min(\"ZSCORE(transactions.amount)\") filter (where daterange((aod.as_of_date - interval 'P30D')::date, aod.as_of_date::date, '[]') @> transaction_date::date)) as \"RANGE(transactions.ZSCORE(transactions.amount)|interval=P30D)\" ,(max(\"ZSCORE(transactions.amount)\") filter (where daterange((aod.as_of_date - interval 'P7D')::date, aod.as_of_date::date, '[]') @> transaction_date::date) - min(\"ZSCORE(transactions.amount)\") filter (where daterange((aod.as_of_date - interval 'P7D')::date, aod.as_of_date::date, '[]') @> transaction_date::date)) as \"RANGE(transactions.ZSCORE(transactions.amount)|interval=P7D)\" ,(max(amount) - min(amount)) as \"RANGE(transactions.amount)\" ,(max(amount) filter (where daterange((aod.as_of_date - interval 'P30D')::date, aod.as_of_date::date, '[]') @> transaction_date::date) - min(amount) filter (where daterange((aod.as_of_date - interval 'P30D')::date, aod.as_of_date::date, '[]') @> transaction_date::date)) as \"RANGE(transactions.amount|interval=P30D)\" ,(max(amount) filter (where daterange((aod.as_of_date - interval 'P7D')::date, aod.as_of_date::date, '[]') @> transaction_date::date) - min(amount) filter (where daterange((aod.as_of_date - interval 'P7D')::date, aod.as_of_date::date, '[]') @> transaction_date::date)) as \"RANGE(transactions.amount|interval=P7D)\" \n", " from transactions_transform\n", " where transaction_date <= aod.as_of_date\n", " group by account_id\n", " )\n", " ,\n", " -- sythetize aggregations and direct features for accounts\n", " accounts_synth as (\n", " select\n", " accounts.account_id, accounts.open_date, \"COUNT(transactions.transaction_date)\", \"COUNT(transactions.transaction_date|interval=P30D)\", \"COUNT(transactions.transaction_date|interval=P7D)\", \"COUNT(transactions.transaction_id)\", \"COUNT(transactions.transaction_id|interval=P30D)\", \"COUNT(transactions.transaction_id|interval=P7D)\", \"COUNT(transactions.transaction_type)\", \"COUNT(transactions.transaction_type|interval=P30D)\", \"COUNT(transactions.transaction_type|interval=P7D)\", \"MEAN(transactions.BIN(transactions.amount))\", \"MEAN(transactions.BIN(transactions.amount)|interval=P30D)\", \"MEAN(transactions.BIN(transactions.amount)|interval=P7D)\", \"MEAN(transactions.LOG1P(transactions.amount))\", \"MEAN(transactions.LOG1P(transactions.amount)|interval=P30D)\", \"MEAN(transactions.LOG1P(transactions.amount)|interval=P7D)\", \"MEAN(transactions.ZSCORE(transactions.amount))\", \"MEAN(transactions.ZSCORE(transactions.amount)|interval=P30D)\", \"MEAN(transactions.ZSCORE(transactions.amount)|interval=P7D)\", \"MEAN(transactions.amount)\", \"MEAN(transactions.amount|interval=P30D)\", \"MEAN(transactions.amount|interval=P7D)\", \"P95(transactions.BIN(transactions.amount))\", \"P95(transactions.BIN(transactions.amount)|interval=P30D)\", \"P95(transactions.BIN(transactions.amount)|interval=P7D)\", \"P95(transactions.LOG1P(transactions.amount))\", \"P95(transactions.LOG1P(transactions.amount)|interval=P30D)\", \"P95(transactions.LOG1P(transactions.amount)|interval=P7D)\", \"P95(transactions.ZSCORE(transactions.amount))\", \"P95(transactions.ZSCORE(transactions.amount)|interval=P30D)\", \"P95(transactions.ZSCORE(transactions.amount)|interval=P7D)\", \"P95(transactions.amount)\", \"P95(transactions.amount|interval=P30D)\", \"P95(transactions.amount|interval=P7D)\", \"RANGE(transactions.BIN(transactions.amount))\", \"RANGE(transactions.BIN(transactions.amount)|interval=P30D)\", \"RANGE(transactions.BIN(transactions.amount)|interval=P7D)\", \"RANGE(transactions.LOG1P(transactions.amount))\", \"RANGE(transactions.LOG1P(transactions.amount)|interval=P30D)\", \"RANGE(transactions.LOG1P(transactions.amount)|interval=P7D)\", \"RANGE(transactions.ZSCORE(transactions.amount))\", \"RANGE(transactions.ZSCORE(transactions.amount)|interval=P30D)\", \"RANGE(transactions.ZSCORE(transactions.amount)|interval=P7D)\", \"RANGE(transactions.amount)\", \"RANGE(transactions.amount|interval=P30D)\", \"RANGE(transactions.amount|interval=P7D)\", account_type, credit_limit\n", " from accounts\n", " left join \n", " transactions_aggs_for_accounts on transactions_aggs_for_accounts.account_id = accounts.account_id \n", " )\n", " ,\n", " -- transform accounts\n", " accounts_transform as (\n", " select\n", " account_id, open_date, case when \"COUNT(transactions.transaction_date)\" is null then null else cast(floor(5 * (\"COUNT(transactions.transaction_date)\" - min(\"COUNT(transactions.transaction_date)\") over ()) / nullif(max(\"COUNT(transactions.transaction_date)\") over () - min(\"COUNT(transactions.transaction_date)\") over (), 0)) as integer) end as \"BIN(accounts.COUNT(transactions.transaction_date))\" , case when \"COUNT(transactions.transaction_date|interval=P30D)\" is null then null else cast(floor(5 * (\"COUNT(transactions.transaction_date|interval=P30D)\" - min(\"COUNT(transactions.transaction_date|interval=P30D)\") over ()) / nullif(max(\"COUNT(transactions.transaction_date|interval=P30D)\") over () - min(\"COUNT(transactions.transaction_date|interval=P30D)\") over (), 0)) as integer) end as \"BIN(accounts.COUNT(transactions.transaction_date|interval=P30D))\" , case when \"COUNT(transactions.transaction_date|interval=P7D)\" is null then null else cast(floor(5 * (\"COUNT(transactions.transaction_date|interval=P7D)\" - min(\"COUNT(transactions.transaction_date|interval=P7D)\") over ()) / nullif(max(\"COUNT(transactions.transaction_date|interval=P7D)\") over () - min(\"COUNT(transactions.transaction_date|interval=P7D)\") over (), 0)) as integer) end as \"BIN(accounts.COUNT(transactions.transaction_date|interval=P7D))\" , case when \"COUNT(transactions.transaction_id)\" is null then null else cast(floor(5 * (\"COUNT(transactions.transaction_id)\" - min(\"COUNT(transactions.transaction_id)\") over ()) / nullif(max(\"COUNT(transactions.transaction_id)\") over () - min(\"COUNT(transactions.transaction_id)\") over (), 0)) as integer) end as \"BIN(accounts.COUNT(transactions.transaction_id))\" , case when \"COUNT(transactions.transaction_id|interval=P30D)\" is null then null else cast(floor(5 * (\"COUNT(transactions.transaction_id|interval=P30D)\" - min(\"COUNT(transactions.transaction_id|interval=P30D)\") over ()) / nullif(max(\"COUNT(transactions.transaction_id|interval=P30D)\") over () - min(\"COUNT(transactions.transaction_id|interval=P30D)\") over (), 0)) as integer) end as \"BIN(accounts.COUNT(transactions.transaction_id|interval=P30D))\" , case when \"COUNT(transactions.transaction_id|interval=P7D)\" is null then null else cast(floor(5 * (\"COUNT(transactions.transaction_id|interval=P7D)\" - min(\"COUNT(transactions.transaction_id|interval=P7D)\") over ()) / nullif(max(\"COUNT(transactions.transaction_id|interval=P7D)\") over () - min(\"COUNT(transactions.transaction_id|interval=P7D)\") over (), 0)) as integer) end as \"BIN(accounts.COUNT(transactions.transaction_id|interval=P7D))\" , case when \"COUNT(transactions.transaction_type)\" is null then null else cast(floor(5 * (\"COUNT(transactions.transaction_type)\" - min(\"COUNT(transactions.transaction_type)\") over ()) / nullif(max(\"COUNT(transactions.transaction_type)\") over () - min(\"COUNT(transactions.transaction_type)\") over (), 0)) as integer) end as \"BIN(accounts.COUNT(transactions.transaction_type))\" , case when \"COUNT(transactions.transaction_type|interval=P30D)\" is null then null else cast(floor(5 * (\"COUNT(transactions.transaction_type|interval=P30D)\" - min(\"COUNT(transactions.transaction_type|interval=P30D)\") over ()) / nullif(max(\"COUNT(transactions.transaction_type|interval=P30D)\") over () - min(\"COUNT(transactions.transaction_type|interval=P30D)\") over (), 0)) as integer) end as \"BIN(accounts.COUNT(transactions.transaction_type|interval=P30D))\" , case when \"COUNT(transactions.transaction_type|interval=P7D)\" is null then null else cast(floor(5 * (\"COUNT(transactions.transaction_type|interval=P7D)\" - min(\"COUNT(transactions.transaction_type|interval=P7D)\") over ()) / nullif(max(\"COUNT(transactions.transaction_type|interval=P7D)\") over () - min(\"COUNT(transactions.transaction_type|interval=P7D)\") over (), 0)) as integer) end as \"BIN(accounts.COUNT(transactions.transaction_type|interval=P7D))\" , case when \"MEAN(transactions.BIN(transactions.amount))\" is null then null else cast(floor(5 * (\"MEAN(transactions.BIN(transactions.amount))\" - min(\"MEAN(transactions.BIN(transactions.amount))\") over ()) / nullif(max(\"MEAN(transactions.BIN(transactions.amount))\") over () - min(\"MEAN(transactions.BIN(transactions.amount))\") over (), 0)) as integer) end as \"BIN(accounts.MEAN(transactions.BIN(transactions.amount)))\" , case when \"MEAN(transactions.BIN(transactions.amount)|interval=P30D)\" is null then null else cast(floor(5 * (\"MEAN(transactions.BIN(transactions.amount)|interval=P30D)\" - min(\"MEAN(transactions.BIN(transactions.amount)|interval=P30D)\") over ()) / nullif(max(\"MEAN(transactions.BIN(transactions.amount)|interval=P30D)\") over () - min(\"MEAN(transactions.BIN(transactions.amount)|interval=P30D)\") over (), 0)) as integer) end as \"BIN(accounts.MEAN(transactions.BIN(transactions.amount)|interval=P30D))\" , case when \"MEAN(transactions.BIN(transactions.amount)|interval=P7D)\" is null then null else cast(floor(5 * (\"MEAN(transactions.BIN(transactions.amount)|interval=P7D)\" - min(\"MEAN(transactions.BIN(transactions.amount)|interval=P7D)\") over ()) / nullif(max(\"MEAN(transactions.BIN(transactions.amount)|interval=P7D)\") over () - min(\"MEAN(transactions.BIN(transactions.amount)|interval=P7D)\") over (), 0)) as integer) end as \"BIN(accounts.MEAN(transactions.BIN(transactions.amount)|interval=P7D))\" , case when \"MEAN(transactions.LOG1P(transactions.amount))\" is null then null else cast(floor(5 * (\"MEAN(transactions.LOG1P(transactions.amount))\" - min(\"MEAN(transactions.LOG1P(transactions.amount))\") over ()) / nullif(max(\"MEAN(transactions.LOG1P(transactions.amount))\") over () - min(\"MEAN(transactions.LOG1P(transactions.amount))\") over (), 0)) as integer) end as \"BIN(accounts.MEAN(transactions.LOG1P(transactions.amount)))\" , case when \"MEAN(transactions.LOG1P(transactions.amount)|interval=P30D)\" is null then null else cast(floor(5 * (\"MEAN(transactions.LOG1P(transactions.amount)|interval=P30D)\" - min(\"MEAN(transactions.LOG1P(transactions.amount)|interval=P30D)\") over ()) / nullif(max(\"MEAN(transactions.LOG1P(transactions.amount)|interval=P30D)\") over () - min(\"MEAN(transactions.LOG1P(transactions.amount)|interval=P30D)\") over (), 0)) as integer) end as \"BIN(accounts.MEAN(transactions.LOG1P(transactions.amount)|interval=P30D))\" , case when \"MEAN(transactions.LOG1P(transactions.amount)|interval=P7D)\" is null then null else cast(floor(5 * (\"MEAN(transactions.LOG1P(transactions.amount)|interval=P7D)\" - min(\"MEAN(transactions.LOG1P(transactions.amount)|interval=P7D)\") over ()) / nullif(max(\"MEAN(transactions.LOG1P(transactions.amount)|interval=P7D)\") over () - min(\"MEAN(transactions.LOG1P(transactions.amount)|interval=P7D)\") over (), 0)) as integer) end as \"BIN(accounts.MEAN(transactions.LOG1P(transactions.amount)|interval=P7D))\" , case when \"MEAN(transactions.ZSCORE(transactions.amount))\" is null then null else cast(floor(5 * (\"MEAN(transactions.ZSCORE(transactions.amount))\" - min(\"MEAN(transactions.ZSCORE(transactions.amount))\") over ()) / nullif(max(\"MEAN(transactions.ZSCORE(transactions.amount))\") over () - min(\"MEAN(transactions.ZSCORE(transactions.amount))\") over (), 0)) as integer) end as \"BIN(accounts.MEAN(transactions.ZSCORE(transactions.amount)))\" , case when \"MEAN(transactions.ZSCORE(transactions.amount)|interval=P30D)\" is null then null else cast(floor(5 * (\"MEAN(transactions.ZSCORE(transactions.amount)|interval=P30D)\" - min(\"MEAN(transactions.ZSCORE(transactions.amount)|interval=P30D)\") over ()) / nullif(max(\"MEAN(transactions.ZSCORE(transactions.amount)|interval=P30D)\") over () - min(\"MEAN(transactions.ZSCORE(transactions.amount)|interval=P30D)\") over (), 0)) as integer) end as \"BIN(accounts.MEAN(transactions.ZSCORE(transactions.amount)|interval=P30D))\" , case when \"MEAN(transactions.ZSCORE(transactions.amount)|interval=P7D)\" is null then null else cast(floor(5 * (\"MEAN(transactions.ZSCORE(transactions.amount)|interval=P7D)\" - min(\"MEAN(transactions.ZSCORE(transactions.amount)|interval=P7D)\") over ()) / nullif(max(\"MEAN(transactions.ZSCORE(transactions.amount)|interval=P7D)\") over () - min(\"MEAN(transactions.ZSCORE(transactions.amount)|interval=P7D)\") over (), 0)) as integer) end as \"BIN(accounts.MEAN(transactions.ZSCORE(transactions.amount)|interval=P7D))\" , case when \"MEAN(transactions.amount)\" is null then null else cast(floor(5 * (\"MEAN(transactions.amount)\" - min(\"MEAN(transactions.amount)\") over ()) / nullif(max(\"MEAN(transactions.amount)\") over () - min(\"MEAN(transactions.amount)\") over (), 0)) as integer) end as \"BIN(accounts.MEAN(transactions.amount))\" , case when \"MEAN(transactions.amount|interval=P30D)\" is null then null else cast(floor(5 * (\"MEAN(transactions.amount|interval=P30D)\" - min(\"MEAN(transactions.amount|interval=P30D)\") over ()) / nullif(max(\"MEAN(transactions.amount|interval=P30D)\") over () - min(\"MEAN(transactions.amount|interval=P30D)\") over (), 0)) as integer) end as \"BIN(accounts.MEAN(transactions.amount|interval=P30D))\" , case when \"MEAN(transactions.amount|interval=P7D)\" is null then null else cast(floor(5 * (\"MEAN(transactions.amount|interval=P7D)\" - min(\"MEAN(transactions.amount|interval=P7D)\") over ()) / nullif(max(\"MEAN(transactions.amount|interval=P7D)\") over () - min(\"MEAN(transactions.amount|interval=P7D)\") over (), 0)) as integer) end as \"BIN(accounts.MEAN(transactions.amount|interval=P7D))\" , case when \"P95(transactions.BIN(transactions.amount))\" is null then null else cast(floor(5 * (\"P95(transactions.BIN(transactions.amount))\" - min(\"P95(transactions.BIN(transactions.amount))\") over ()) / nullif(max(\"P95(transactions.BIN(transactions.amount))\") over () - min(\"P95(transactions.BIN(transactions.amount))\") over (), 0)) as integer) end as \"BIN(accounts.P95(transactions.BIN(transactions.amount)))\" , case when \"P95(transactions.BIN(transactions.amount)|interval=P30D)\" is null then null else cast(floor(5 * (\"P95(transactions.BIN(transactions.amount)|interval=P30D)\" - min(\"P95(transactions.BIN(transactions.amount)|interval=P30D)\") over ()) / nullif(max(\"P95(transactions.BIN(transactions.amount)|interval=P30D)\") over () - min(\"P95(transactions.BIN(transactions.amount)|interval=P30D)\") over (), 0)) as integer) end as \"BIN(accounts.P95(transactions.BIN(transactions.amount)|interval=P30D))\" , case when \"P95(transactions.BIN(transactions.amount)|interval=P7D)\" is null then null else cast(floor(5 * (\"P95(transactions.BIN(transactions.amount)|interval=P7D)\" - min(\"P95(transactions.BIN(transactions.amount)|interval=P7D)\") over ()) / nullif(max(\"P95(transactions.BIN(transactions.amount)|interval=P7D)\") over () - min(\"P95(transactions.BIN(transactions.amount)|interval=P7D)\") over (), 0)) as integer) end as \"BIN(accounts.P95(transactions.BIN(transactions.amount)|interval=P7D))\" , case when \"P95(transactions.LOG1P(transactions.amount))\" is null then null else cast(floor(5 * (\"P95(transactions.LOG1P(transactions.amount))\" - min(\"P95(transactions.LOG1P(transactions.amount))\") over ()) / nullif(max(\"P95(transactions.LOG1P(transactions.amount))\") over () - min(\"P95(transactions.LOG1P(transactions.amount))\") over (), 0)) as integer) end as \"BIN(accounts.P95(transactions.LOG1P(transactions.amount)))\" , case when \"P95(transactions.LOG1P(transactions.amount)|interval=P30D)\" is null then null else cast(floor(5 * (\"P95(transactions.LOG1P(transactions.amount)|interval=P30D)\" - min(\"P95(transactions.LOG1P(transactions.amount)|interval=P30D)\") over ()) / nullif(max(\"P95(transactions.LOG1P(transactions.amount)|interval=P30D)\") over () - min(\"P95(transactions.LOG1P(transactions.amount)|interval=P30D)\") over (), 0)) as integer) end as \"BIN(accounts.P95(transactions.LOG1P(transactions.amount)|interval=P30D))\" , case when \"P95(transactions.LOG1P(transactions.amount)|interval=P7D)\" is null then null else cast(floor(5 * (\"P95(transactions.LOG1P(transactions.amount)|interval=P7D)\" - min(\"P95(transactions.LOG1P(transactions.amount)|interval=P7D)\") over ()) / nullif(max(\"P95(transactions.LOG1P(transactions.amount)|interval=P7D)\") over () - min(\"P95(transactions.LOG1P(transactions.amount)|interval=P7D)\") over (), 0)) as integer) end as \"BIN(accounts.P95(transactions.LOG1P(transactions.amount)|interval=P7D))\" , case when \"P95(transactions.ZSCORE(transactions.amount))\" is null then null else cast(floor(5 * (\"P95(transactions.ZSCORE(transactions.amount))\" - min(\"P95(transactions.ZSCORE(transactions.amount))\") over ()) / nullif(max(\"P95(transactions.ZSCORE(transactions.amount))\") over () - min(\"P95(transactions.ZSCORE(transactions.amount))\") over (), 0)) as integer) end as \"BIN(accounts.P95(transactions.ZSCORE(transactions.amount)))\" , case when \"P95(transactions.ZSCORE(transactions.amount)|interval=P30D)\" is null then null else cast(floor(5 * (\"P95(transactions.ZSCORE(transactions.amount)|interval=P30D)\" - min(\"P95(transactions.ZSCORE(transactions.amount)|interval=P30D)\") over ()) / nullif(max(\"P95(transactions.ZSCORE(transactions.amount)|interval=P30D)\") over () - min(\"P95(transactions.ZSCORE(transactions.amount)|interval=P30D)\") over (), 0)) as integer) end as \"BIN(accounts.P95(transactions.ZSCORE(transactions.amount)|interval=P30D))\" , case when \"P95(transactions.ZSCORE(transactions.amount)|interval=P7D)\" is null then null else cast(floor(5 * (\"P95(transactions.ZSCORE(transactions.amount)|interval=P7D)\" - min(\"P95(transactions.ZSCORE(transactions.amount)|interval=P7D)\") over ()) / nullif(max(\"P95(transactions.ZSCORE(transactions.amount)|interval=P7D)\") over () - min(\"P95(transactions.ZSCORE(transactions.amount)|interval=P7D)\") over (), 0)) as integer) end as \"BIN(accounts.P95(transactions.ZSCORE(transactions.amount)|interval=P7D))\" , case when \"P95(transactions.amount)\" is null then null else cast(floor(5 * (\"P95(transactions.amount)\" - min(\"P95(transactions.amount)\") over ()) / nullif(max(\"P95(transactions.amount)\") over () - min(\"P95(transactions.amount)\") over (), 0)) as integer) end as \"BIN(accounts.P95(transactions.amount))\" , case when \"P95(transactions.amount|interval=P30D)\" is null then null else cast(floor(5 * (\"P95(transactions.amount|interval=P30D)\" - min(\"P95(transactions.amount|interval=P30D)\") over ()) / nullif(max(\"P95(transactions.amount|interval=P30D)\") over () - min(\"P95(transactions.amount|interval=P30D)\") over (), 0)) as integer) end as \"BIN(accounts.P95(transactions.amount|interval=P30D))\" , case when \"P95(transactions.amount|interval=P7D)\" is null then null else cast(floor(5 * (\"P95(transactions.amount|interval=P7D)\" - min(\"P95(transactions.amount|interval=P7D)\") over ()) / nullif(max(\"P95(transactions.amount|interval=P7D)\") over () - min(\"P95(transactions.amount|interval=P7D)\") over (), 0)) as integer) end as \"BIN(accounts.P95(transactions.amount|interval=P7D))\" , case when \"RANGE(transactions.BIN(transactions.amount))\" is null then null else cast(floor(5 * (\"RANGE(transactions.BIN(transactions.amount))\" - min(\"RANGE(transactions.BIN(transactions.amount))\") over ()) / nullif(max(\"RANGE(transactions.BIN(transactions.amount))\") over () - min(\"RANGE(transactions.BIN(transactions.amount))\") over (), 0)) as integer) end as \"BIN(accounts.RANGE(transactions.BIN(transactions.amount)))\" , case when \"RANGE(transactions.BIN(transactions.amount)|interval=P30D)\" is null then null else cast(floor(5 * (\"RANGE(transactions.BIN(transactions.amount)|interval=P30D)\" - min(\"RANGE(transactions.BIN(transactions.amount)|interval=P30D)\") over ()) / nullif(max(\"RANGE(transactions.BIN(transactions.amount)|interval=P30D)\") over () - min(\"RANGE(transactions.BIN(transactions.amount)|interval=P30D)\") over (), 0)) as integer) end as \"BIN(accounts.RANGE(transactions.BIN(transactions.amount)|interval=P30D))\" , case when \"RANGE(transactions.BIN(transactions.amount)|interval=P7D)\" is null then null else cast(floor(5 * (\"RANGE(transactions.BIN(transactions.amount)|interval=P7D)\" - min(\"RANGE(transactions.BIN(transactions.amount)|interval=P7D)\") over ()) / nullif(max(\"RANGE(transactions.BIN(transactions.amount)|interval=P7D)\") over () - min(\"RANGE(transactions.BIN(transactions.amount)|interval=P7D)\") over (), 0)) as integer) end as \"BIN(accounts.RANGE(transactions.BIN(transactions.amount)|interval=P7D))\" , case when \"RANGE(transactions.LOG1P(transactions.amount))\" is null then null else cast(floor(5 * (\"RANGE(transactions.LOG1P(transactions.amount))\" - min(\"RANGE(transactions.LOG1P(transactions.amount))\") over ()) / nullif(max(\"RANGE(transactions.LOG1P(transactions.amount))\") over () - min(\"RANGE(transactions.LOG1P(transactions.amount))\") over (), 0)) as integer) end as \"BIN(accounts.RANGE(transactions.LOG1P(transactions.amount)))\" , case when \"RANGE(transactions.LOG1P(transactions.amount)|interval=P30D)\" is null then null else cast(floor(5 * (\"RANGE(transactions.LOG1P(transactions.amount)|interval=P30D)\" - min(\"RANGE(transactions.LOG1P(transactions.amount)|interval=P30D)\") over ()) / nullif(max(\"RANGE(transactions.LOG1P(transactions.amount)|interval=P30D)\") over () - min(\"RANGE(transactions.LOG1P(transactions.amount)|interval=P30D)\") over (), 0)) as integer) end as \"BIN(accounts.RANGE(transactions.LOG1P(transactions.amount)|interval=P30D))\" , case when \"RANGE(transactions.LOG1P(transactions.amount)|interval=P7D)\" is null then null else cast(floor(5 * (\"RANGE(transactions.LOG1P(transactions.amount)|interval=P7D)\" - min(\"RANGE(transactions.LOG1P(transactions.amount)|interval=P7D)\") over ()) / nullif(max(\"RANGE(transactions.LOG1P(transactions.amount)|interval=P7D)\") over () - min(\"RANGE(transactions.LOG1P(transactions.amount)|interval=P7D)\") over (), 0)) as integer) end as \"BIN(accounts.RANGE(transactions.LOG1P(transactions.amount)|interval=P7D))\" , case when \"RANGE(transactions.ZSCORE(transactions.amount))\" is null then null else cast(floor(5 * (\"RANGE(transactions.ZSCORE(transactions.amount))\" - min(\"RANGE(transactions.ZSCORE(transactions.amount))\") over ()) / nullif(max(\"RANGE(transactions.ZSCORE(transactions.amount))\") over () - min(\"RANGE(transactions.ZSCORE(transactions.amount))\") over (), 0)) as integer) end as \"BIN(accounts.RANGE(transactions.ZSCORE(transactions.amount)))\" , case when \"RANGE(transactions.ZSCORE(transactions.amount)|interval=P30D)\" is null then null else cast(floor(5 * (\"RANGE(transactions.ZSCORE(transactions.amount)|interval=P30D)\" - min(\"RANGE(transactions.ZSCORE(transactions.amount)|interval=P30D)\") over ()) / nullif(max(\"RANGE(transactions.ZSCORE(transactions.amount)|interval=P30D)\") over () - min(\"RANGE(transactions.ZSCORE(transactions.amount)|interval=P30D)\") over (), 0)) as integer) end as \"BIN(accounts.RANGE(transactions.ZSCORE(transactions.amount)|interval=P30D))\" , case when \"RANGE(transactions.ZSCORE(transactions.amount)|interval=P7D)\" is null then null else cast(floor(5 * (\"RANGE(transactions.ZSCORE(transactions.amount)|interval=P7D)\" - min(\"RANGE(transactions.ZSCORE(transactions.amount)|interval=P7D)\") over ()) / nullif(max(\"RANGE(transactions.ZSCORE(transactions.amount)|interval=P7D)\") over () - min(\"RANGE(transactions.ZSCORE(transactions.amount)|interval=P7D)\") over (), 0)) as integer) end as \"BIN(accounts.RANGE(transactions.ZSCORE(transactions.amount)|interval=P7D))\" , case when \"RANGE(transactions.amount)\" is null then null else cast(floor(5 * (\"RANGE(transactions.amount)\" - min(\"RANGE(transactions.amount)\") over ()) / nullif(max(\"RANGE(transactions.amount)\") over () - min(\"RANGE(transactions.amount)\") over (), 0)) as integer) end as \"BIN(accounts.RANGE(transactions.amount))\" , case when \"RANGE(transactions.amount|interval=P30D)\" is null then null else cast(floor(5 * (\"RANGE(transactions.amount|interval=P30D)\" - min(\"RANGE(transactions.amount|interval=P30D)\") over ()) / nullif(max(\"RANGE(transactions.amount|interval=P30D)\") over () - min(\"RANGE(transactions.amount|interval=P30D)\") over (), 0)) as integer) end as \"BIN(accounts.RANGE(transactions.amount|interval=P30D))\" , case when \"RANGE(transactions.amount|interval=P7D)\" is null then null else cast(floor(5 * (\"RANGE(transactions.amount|interval=P7D)\" - min(\"RANGE(transactions.amount|interval=P7D)\") over ()) / nullif(max(\"RANGE(transactions.amount|interval=P7D)\") over () - min(\"RANGE(transactions.amount|interval=P7D)\") over (), 0)) as integer) end as \"BIN(accounts.RANGE(transactions.amount|interval=P7D))\" , case when credit_limit is null then null else cast(floor(5 * (credit_limit - min(credit_limit) over ()) / nullif(max(credit_limit) over () - min(credit_limit) over (), 0)) as integer) end as \"BIN(accounts.credit_limit)\" , \"COUNT(transactions.transaction_date)\" as \"COUNT(transactions.transaction_date)\", \"COUNT(transactions.transaction_date|interval=P30D)\" as \"COUNT(transactions.transaction_date|interval=P30D)\", \"COUNT(transactions.transaction_date|interval=P7D)\" as \"COUNT(transactions.transaction_date|interval=P7D)\", \"COUNT(transactions.transaction_id)\" as \"COUNT(transactions.transaction_id)\", \"COUNT(transactions.transaction_id|interval=P30D)\" as \"COUNT(transactions.transaction_id|interval=P30D)\", \"COUNT(transactions.transaction_id|interval=P7D)\" as \"COUNT(transactions.transaction_id|interval=P7D)\", \"COUNT(transactions.transaction_type)\" as \"COUNT(transactions.transaction_type)\", \"COUNT(transactions.transaction_type|interval=P30D)\" as \"COUNT(transactions.transaction_type|interval=P30D)\", \"COUNT(transactions.transaction_type|interval=P7D)\" as \"COUNT(transactions.transaction_type|interval=P7D)\", case when \"COUNT(transactions.transaction_date)\" + 1 > 0 then ln(\"COUNT(transactions.transaction_date)\" + 1) end as \"LOG1P(accounts.COUNT(transactions.transaction_date))\" , case when \"COUNT(transactions.transaction_date|interval=P30D)\" + 1 > 0 then ln(\"COUNT(transactions.transaction_date|interval=P30D)\" + 1) end as \"LOG1P(accounts.COUNT(transactions.transaction_date|interval=P30D))\" , case when \"COUNT(transactions.transaction_date|interval=P7D)\" + 1 > 0 then ln(\"COUNT(transactions.transaction_date|interval=P7D)\" + 1) end as \"LOG1P(accounts.COUNT(transactions.transaction_date|interval=P7D))\" , case when \"COUNT(transactions.transaction_id)\" + 1 > 0 then ln(\"COUNT(transactions.transaction_id)\" + 1) end as \"LOG1P(accounts.COUNT(transactions.transaction_id))\" , case when \"COUNT(transactions.transaction_id|interval=P30D)\" + 1 > 0 then ln(\"COUNT(transactions.transaction_id|interval=P30D)\" + 1) end as \"LOG1P(accounts.COUNT(transactions.transaction_id|interval=P30D))\" , case when \"COUNT(transactions.transaction_id|interval=P7D)\" + 1 > 0 then ln(\"COUNT(transactions.transaction_id|interval=P7D)\" + 1) end as \"LOG1P(accounts.COUNT(transactions.transaction_id|interval=P7D))\" , case when \"COUNT(transactions.transaction_type)\" + 1 > 0 then ln(\"COUNT(transactions.transaction_type)\" + 1) end as \"LOG1P(accounts.COUNT(transactions.transaction_type))\" , case when \"COUNT(transactions.transaction_type|interval=P30D)\" + 1 > 0 then ln(\"COUNT(transactions.transaction_type|interval=P30D)\" + 1) end as \"LOG1P(accounts.COUNT(transactions.transaction_type|interval=P30D))\" , case when \"COUNT(transactions.transaction_type|interval=P7D)\" + 1 > 0 then ln(\"COUNT(transactions.transaction_type|interval=P7D)\" + 1) end as \"LOG1P(accounts.COUNT(transactions.transaction_type|interval=P7D))\" , case when \"MEAN(transactions.BIN(transactions.amount))\" + 1 > 0 then ln(\"MEAN(transactions.BIN(transactions.amount))\" + 1) end as \"LOG1P(accounts.MEAN(transactions.BIN(transactions.amount)))\" , case when \"MEAN(transactions.BIN(transactions.amount)|interval=P30D)\" + 1 > 0 then ln(\"MEAN(transactions.BIN(transactions.amount)|interval=P30D)\" + 1) end as \"LOG1P(accounts.MEAN(transactions.BIN(transactions.amount)|interval=P30D))\" , case when \"MEAN(transactions.BIN(transactions.amount)|interval=P7D)\" + 1 > 0 then ln(\"MEAN(transactions.BIN(transactions.amount)|interval=P7D)\" + 1) end as \"LOG1P(accounts.MEAN(transactions.BIN(transactions.amount)|interval=P7D))\" , case when \"MEAN(transactions.LOG1P(transactions.amount))\" + 1 > 0 then ln(\"MEAN(transactions.LOG1P(transactions.amount))\" + 1) end as \"LOG1P(accounts.MEAN(transactions.LOG1P(transactions.amount)))\" , case when \"MEAN(transactions.LOG1P(transactions.amount)|interval=P30D)\" + 1 > 0 then ln(\"MEAN(transactions.LOG1P(transactions.amount)|interval=P30D)\" + 1) end as \"LOG1P(accounts.MEAN(transactions.LOG1P(transactions.amount)|interval=P30D))\" , case when \"MEAN(transactions.LOG1P(transactions.amount)|interval=P7D)\" + 1 > 0 then ln(\"MEAN(transactions.LOG1P(transactions.amount)|interval=P7D)\" + 1) end as \"LOG1P(accounts.MEAN(transactions.LOG1P(transactions.amount)|interval=P7D))\" , case when \"MEAN(transactions.ZSCORE(transactions.amount))\" + 1 > 0 then ln(\"MEAN(transactions.ZSCORE(transactions.amount))\" + 1) end as \"LOG1P(accounts.MEAN(transactions.ZSCORE(transactions.amount)))\" , case when \"MEAN(transactions.ZSCORE(transactions.amount)|interval=P30D)\" + 1 > 0 then ln(\"MEAN(transactions.ZSCORE(transactions.amount)|interval=P30D)\" + 1) end as \"LOG1P(accounts.MEAN(transactions.ZSCORE(transactions.amount)|interval=P30D))\" , case when \"MEAN(transactions.ZSCORE(transactions.amount)|interval=P7D)\" + 1 > 0 then ln(\"MEAN(transactions.ZSCORE(transactions.amount)|interval=P7D)\" + 1) end as \"LOG1P(accounts.MEAN(transactions.ZSCORE(transactions.amount)|interval=P7D))\" , case when \"MEAN(transactions.amount)\" + 1 > 0 then ln(\"MEAN(transactions.amount)\" + 1) end as \"LOG1P(accounts.MEAN(transactions.amount))\" , case when \"MEAN(transactions.amount|interval=P30D)\" + 1 > 0 then ln(\"MEAN(transactions.amount|interval=P30D)\" + 1) end as \"LOG1P(accounts.MEAN(transactions.amount|interval=P30D))\" , case when \"MEAN(transactions.amount|interval=P7D)\" + 1 > 0 then ln(\"MEAN(transactions.amount|interval=P7D)\" + 1) end as \"LOG1P(accounts.MEAN(transactions.amount|interval=P7D))\" , case when \"P95(transactions.BIN(transactions.amount))\" + 1 > 0 then ln(\"P95(transactions.BIN(transactions.amount))\" + 1) end as \"LOG1P(accounts.P95(transactions.BIN(transactions.amount)))\" , case when \"P95(transactions.BIN(transactions.amount)|interval=P30D)\" + 1 > 0 then ln(\"P95(transactions.BIN(transactions.amount)|interval=P30D)\" + 1) end as \"LOG1P(accounts.P95(transactions.BIN(transactions.amount)|interval=P30D))\" , case when \"P95(transactions.BIN(transactions.amount)|interval=P7D)\" + 1 > 0 then ln(\"P95(transactions.BIN(transactions.amount)|interval=P7D)\" + 1) end as \"LOG1P(accounts.P95(transactions.BIN(transactions.amount)|interval=P7D))\" , case when \"P95(transactions.LOG1P(transactions.amount))\" + 1 > 0 then ln(\"P95(transactions.LOG1P(transactions.amount))\" + 1) end as \"LOG1P(accounts.P95(transactions.LOG1P(transactions.amount)))\" , case when \"P95(transactions.LOG1P(transactions.amount)|interval=P30D)\" + 1 > 0 then ln(\"P95(transactions.LOG1P(transactions.amount)|interval=P30D)\" + 1) end as \"LOG1P(accounts.P95(transactions.LOG1P(transactions.amount)|interval=P30D))\" , case when \"P95(transactions.LOG1P(transactions.amount)|interval=P7D)\" + 1 > 0 then ln(\"P95(transactions.LOG1P(transactions.amount)|interval=P7D)\" + 1) end as \"LOG1P(accounts.P95(transactions.LOG1P(transactions.amount)|interval=P7D))\" , case when \"P95(transactions.ZSCORE(transactions.amount))\" + 1 > 0 then ln(\"P95(transactions.ZSCORE(transactions.amount))\" + 1) end as \"LOG1P(accounts.P95(transactions.ZSCORE(transactions.amount)))\" , case when \"P95(transactions.ZSCORE(transactions.amount)|interval=P30D)\" + 1 > 0 then ln(\"P95(transactions.ZSCORE(transactions.amount)|interval=P30D)\" + 1) end as \"LOG1P(accounts.P95(transactions.ZSCORE(transactions.amount)|interval=P30D))\" , case when \"P95(transactions.ZSCORE(transactions.amount)|interval=P7D)\" + 1 > 0 then ln(\"P95(transactions.ZSCORE(transactions.amount)|interval=P7D)\" + 1) end as \"LOG1P(accounts.P95(transactions.ZSCORE(transactions.amount)|interval=P7D))\" , case when \"P95(transactions.amount)\" + 1 > 0 then ln(\"P95(transactions.amount)\" + 1) end as \"LOG1P(accounts.P95(transactions.amount))\" , case when \"P95(transactions.amount|interval=P30D)\" + 1 > 0 then ln(\"P95(transactions.amount|interval=P30D)\" + 1) end as \"LOG1P(accounts.P95(transactions.amount|interval=P30D))\" , case when \"P95(transactions.amount|interval=P7D)\" + 1 > 0 then ln(\"P95(transactions.amount|interval=P7D)\" + 1) end as \"LOG1P(accounts.P95(transactions.amount|interval=P7D))\" , case when \"RANGE(transactions.BIN(transactions.amount))\" + 1 > 0 then ln(\"RANGE(transactions.BIN(transactions.amount))\" + 1) end as \"LOG1P(accounts.RANGE(transactions.BIN(transactions.amount)))\" , case when \"RANGE(transactions.BIN(transactions.amount)|interval=P30D)\" + 1 > 0 then ln(\"RANGE(transactions.BIN(transactions.amount)|interval=P30D)\" + 1) end as \"LOG1P(accounts.RANGE(transactions.BIN(transactions.amount)|interval=P30D))\" , case when \"RANGE(transactions.BIN(transactions.amount)|interval=P7D)\" + 1 > 0 then ln(\"RANGE(transactions.BIN(transactions.amount)|interval=P7D)\" + 1) end as \"LOG1P(accounts.RANGE(transactions.BIN(transactions.amount)|interval=P7D))\" , case when \"RANGE(transactions.LOG1P(transactions.amount))\" + 1 > 0 then ln(\"RANGE(transactions.LOG1P(transactions.amount))\" + 1) end as \"LOG1P(accounts.RANGE(transactions.LOG1P(transactions.amount)))\" , case when \"RANGE(transactions.LOG1P(transactions.amount)|interval=P30D)\" + 1 > 0 then ln(\"RANGE(transactions.LOG1P(transactions.amount)|interval=P30D)\" + 1) end as \"LOG1P(accounts.RANGE(transactions.LOG1P(transactions.amount)|interval=P30D))\" , case when \"RANGE(transactions.LOG1P(transactions.amount)|interval=P7D)\" + 1 > 0 then ln(\"RANGE(transactions.LOG1P(transactions.amount)|interval=P7D)\" + 1) end as \"LOG1P(accounts.RANGE(transactions.LOG1P(transactions.amount)|interval=P7D))\" , case when \"RANGE(transactions.ZSCORE(transactions.amount))\" + 1 > 0 then ln(\"RANGE(transactions.ZSCORE(transactions.amount))\" + 1) end as \"LOG1P(accounts.RANGE(transactions.ZSCORE(transactions.amount)))\" , case when \"RANGE(transactions.ZSCORE(transactions.amount)|interval=P30D)\" + 1 > 0 then ln(\"RANGE(transactions.ZSCORE(transactions.amount)|interval=P30D)\" + 1) end as \"LOG1P(accounts.RANGE(transactions.ZSCORE(transactions.amount)|interval=P30D))\" , case when \"RANGE(transactions.ZSCORE(transactions.amount)|interval=P7D)\" + 1 > 0 then ln(\"RANGE(transactions.ZSCORE(transactions.amount)|interval=P7D)\" + 1) end as \"LOG1P(accounts.RANGE(transactions.ZSCORE(transactions.amount)|interval=P7D))\" , case when \"RANGE(transactions.amount)\" + 1 > 0 then ln(\"RANGE(transactions.amount)\" + 1) end as \"LOG1P(accounts.RANGE(transactions.amount))\" , case when \"RANGE(transactions.amount|interval=P30D)\" + 1 > 0 then ln(\"RANGE(transactions.amount|interval=P30D)\" + 1) end as \"LOG1P(accounts.RANGE(transactions.amount|interval=P30D))\" , case when \"RANGE(transactions.amount|interval=P7D)\" + 1 > 0 then ln(\"RANGE(transactions.amount|interval=P7D)\" + 1) end as \"LOG1P(accounts.RANGE(transactions.amount|interval=P7D))\" , case when credit_limit + 1 > 0 then ln(credit_limit + 1) end as \"LOG1P(accounts.credit_limit)\" , \"MEAN(transactions.BIN(transactions.amount))\" as \"MEAN(transactions.BIN(transactions.amount))\", \"MEAN(transactions.BIN(transactions.amount)|interval=P30D)\" as \"MEAN(transactions.BIN(transactions.amount)|interval=P30D)\", \"MEAN(transactions.BIN(transactions.amount)|interval=P7D)\" as \"MEAN(transactions.BIN(transactions.amount)|interval=P7D)\", \"MEAN(transactions.LOG1P(transactions.amount))\" as \"MEAN(transactions.LOG1P(transactions.amount))\", \"MEAN(transactions.LOG1P(transactions.amount)|interval=P30D)\" as \"MEAN(transactions.LOG1P(transactions.amount)|interval=P30D)\", \"MEAN(transactions.LOG1P(transactions.amount)|interval=P7D)\" as \"MEAN(transactions.LOG1P(transactions.amount)|interval=P7D)\", \"MEAN(transactions.ZSCORE(transactions.amount))\" as \"MEAN(transactions.ZSCORE(transactions.amount))\", \"MEAN(transactions.ZSCORE(transactions.amount)|interval=P30D)\" as \"MEAN(transactions.ZSCORE(transactions.amount)|interval=P30D)\", \"MEAN(transactions.ZSCORE(transactions.amount)|interval=P7D)\" as \"MEAN(transactions.ZSCORE(transactions.amount)|interval=P7D)\", \"MEAN(transactions.amount)\" as \"MEAN(transactions.amount)\", \"MEAN(transactions.amount|interval=P30D)\" as \"MEAN(transactions.amount|interval=P30D)\", \"MEAN(transactions.amount|interval=P7D)\" as \"MEAN(transactions.amount|interval=P7D)\", \"P95(transactions.BIN(transactions.amount))\" as \"P95(transactions.BIN(transactions.amount))\", \"P95(transactions.BIN(transactions.amount)|interval=P30D)\" as \"P95(transactions.BIN(transactions.amount)|interval=P30D)\", \"P95(transactions.BIN(transactions.amount)|interval=P7D)\" as \"P95(transactions.BIN(transactions.amount)|interval=P7D)\", \"P95(transactions.LOG1P(transactions.amount))\" as \"P95(transactions.LOG1P(transactions.amount))\", \"P95(transactions.LOG1P(transactions.amount)|interval=P30D)\" as \"P95(transactions.LOG1P(transactions.amount)|interval=P30D)\", \"P95(transactions.LOG1P(transactions.amount)|interval=P7D)\" as \"P95(transactions.LOG1P(transactions.amount)|interval=P7D)\", \"P95(transactions.ZSCORE(transactions.amount))\" as \"P95(transactions.ZSCORE(transactions.amount))\", \"P95(transactions.ZSCORE(transactions.amount)|interval=P30D)\" as \"P95(transactions.ZSCORE(transactions.amount)|interval=P30D)\", \"P95(transactions.ZSCORE(transactions.amount)|interval=P7D)\" as \"P95(transactions.ZSCORE(transactions.amount)|interval=P7D)\", \"P95(transactions.amount)\" as \"P95(transactions.amount)\", \"P95(transactions.amount|interval=P30D)\" as \"P95(transactions.amount|interval=P30D)\", \"P95(transactions.amount|interval=P7D)\" as \"P95(transactions.amount|interval=P7D)\", \"RANGE(transactions.BIN(transactions.amount))\" as \"RANGE(transactions.BIN(transactions.amount))\", \"RANGE(transactions.BIN(transactions.amount)|interval=P30D)\" as \"RANGE(transactions.BIN(transactions.amount)|interval=P30D)\", \"RANGE(transactions.BIN(transactions.amount)|interval=P7D)\" as \"RANGE(transactions.BIN(transactions.amount)|interval=P7D)\", \"RANGE(transactions.LOG1P(transactions.amount))\" as \"RANGE(transactions.LOG1P(transactions.amount))\", \"RANGE(transactions.LOG1P(transactions.amount)|interval=P30D)\" as \"RANGE(transactions.LOG1P(transactions.amount)|interval=P30D)\", \"RANGE(transactions.LOG1P(transactions.amount)|interval=P7D)\" as \"RANGE(transactions.LOG1P(transactions.amount)|interval=P7D)\", \"RANGE(transactions.ZSCORE(transactions.amount))\" as \"RANGE(transactions.ZSCORE(transactions.amount))\", \"RANGE(transactions.ZSCORE(transactions.amount)|interval=P30D)\" as \"RANGE(transactions.ZSCORE(transactions.amount)|interval=P30D)\", \"RANGE(transactions.ZSCORE(transactions.amount)|interval=P7D)\" as \"RANGE(transactions.ZSCORE(transactions.amount)|interval=P7D)\", \"RANGE(transactions.amount)\" as \"RANGE(transactions.amount)\", \"RANGE(transactions.amount|interval=P30D)\" as \"RANGE(transactions.amount|interval=P30D)\", \"RANGE(transactions.amount|interval=P7D)\" as \"RANGE(transactions.amount|interval=P7D)\", ((\"COUNT(transactions.transaction_date)\" - avg(\"COUNT(transactions.transaction_date)\") over ()) / nullif(stddev(\"COUNT(transactions.transaction_date)\") over (), 0)) as \"ZSCORE(accounts.COUNT(transactions.transaction_date))\" , ((\"COUNT(transactions.transaction_date|interval=P30D)\" - avg(\"COUNT(transactions.transaction_date|interval=P30D)\") over ()) / nullif(stddev(\"COUNT(transactions.transaction_date|interval=P30D)\") over (), 0)) as \"ZSCORE(accounts.COUNT(transactions.transaction_date|interval=P30D))\" , ((\"COUNT(transactions.transaction_date|interval=P7D)\" - avg(\"COUNT(transactions.transaction_date|interval=P7D)\") over ()) / nullif(stddev(\"COUNT(transactions.transaction_date|interval=P7D)\") over (), 0)) as \"ZSCORE(accounts.COUNT(transactions.transaction_date|interval=P7D))\" , ((\"COUNT(transactions.transaction_id)\" - avg(\"COUNT(transactions.transaction_id)\") over ()) / nullif(stddev(\"COUNT(transactions.transaction_id)\") over (), 0)) as \"ZSCORE(accounts.COUNT(transactions.transaction_id))\" , ((\"COUNT(transactions.transaction_id|interval=P30D)\" - avg(\"COUNT(transactions.transaction_id|interval=P30D)\") over ()) / nullif(stddev(\"COUNT(transactions.transaction_id|interval=P30D)\") over (), 0)) as \"ZSCORE(accounts.COUNT(transactions.transaction_id|interval=P30D))\" , ((\"COUNT(transactions.transaction_id|interval=P7D)\" - avg(\"COUNT(transactions.transaction_id|interval=P7D)\") over ()) / nullif(stddev(\"COUNT(transactions.transaction_id|interval=P7D)\") over (), 0)) as \"ZSCORE(accounts.COUNT(transactions.transaction_id|interval=P7D))\" , ((\"COUNT(transactions.transaction_type)\" - avg(\"COUNT(transactions.transaction_type)\") over ()) / nullif(stddev(\"COUNT(transactions.transaction_type)\") over (), 0)) as \"ZSCORE(accounts.COUNT(transactions.transaction_type))\" , ((\"COUNT(transactions.transaction_type|interval=P30D)\" - avg(\"COUNT(transactions.transaction_type|interval=P30D)\") over ()) / nullif(stddev(\"COUNT(transactions.transaction_type|interval=P30D)\") over (), 0)) as \"ZSCORE(accounts.COUNT(transactions.transaction_type|interval=P30D))\" , ((\"COUNT(transactions.transaction_type|interval=P7D)\" - avg(\"COUNT(transactions.transaction_type|interval=P7D)\") over ()) / nullif(stddev(\"COUNT(transactions.transaction_type|interval=P7D)\") over (), 0)) as \"ZSCORE(accounts.COUNT(transactions.transaction_type|interval=P7D))\" , ((\"MEAN(transactions.BIN(transactions.amount))\" - avg(\"MEAN(transactions.BIN(transactions.amount))\") over ()) / nullif(stddev(\"MEAN(transactions.BIN(transactions.amount))\") over (), 0)) as \"ZSCORE(accounts.MEAN(transactions.BIN(transactions.amount)))\" , ((\"MEAN(transactions.BIN(transactions.amount)|interval=P30D)\" - avg(\"MEAN(transactions.BIN(transactions.amount)|interval=P30D)\") over ()) / nullif(stddev(\"MEAN(transactions.BIN(transactions.amount)|interval=P30D)\") over (), 0)) as \"ZSCORE(accounts.MEAN(transactions.BIN(transactions.amount)|interval=P30D))\" , ((\"MEAN(transactions.BIN(transactions.amount)|interval=P7D)\" - avg(\"MEAN(transactions.BIN(transactions.amount)|interval=P7D)\") over ()) / nullif(stddev(\"MEAN(transactions.BIN(transactions.amount)|interval=P7D)\") over (), 0)) as \"ZSCORE(accounts.MEAN(transactions.BIN(transactions.amount)|interval=P7D))\" , ((\"MEAN(transactions.LOG1P(transactions.amount))\" - avg(\"MEAN(transactions.LOG1P(transactions.amount))\") over ()) / nullif(stddev(\"MEAN(transactions.LOG1P(transactions.amount))\") over (), 0)) as \"ZSCORE(accounts.MEAN(transactions.LOG1P(transactions.amount)))\" , ((\"MEAN(transactions.LOG1P(transactions.amount)|interval=P30D)\" - avg(\"MEAN(transactions.LOG1P(transactions.amount)|interval=P30D)\") over ()) / nullif(stddev(\"MEAN(transactions.LOG1P(transactions.amount)|interval=P30D)\") over (), 0)) as \"ZSCORE(accounts.MEAN(transactions.LOG1P(transactions.amount)|interval=P30D))\" , ((\"MEAN(transactions.LOG1P(transactions.amount)|interval=P7D)\" - avg(\"MEAN(transactions.LOG1P(transactions.amount)|interval=P7D)\") over ()) / nullif(stddev(\"MEAN(transactions.LOG1P(transactions.amount)|interval=P7D)\") over (), 0)) as \"ZSCORE(accounts.MEAN(transactions.LOG1P(transactions.amount)|interval=P7D))\" , ((\"MEAN(transactions.ZSCORE(transactions.amount))\" - avg(\"MEAN(transactions.ZSCORE(transactions.amount))\") over ()) / nullif(stddev(\"MEAN(transactions.ZSCORE(transactions.amount))\") over (), 0)) as \"ZSCORE(accounts.MEAN(transactions.ZSCORE(transactions.amount)))\" , ((\"MEAN(transactions.ZSCORE(transactions.amount)|interval=P30D)\" - avg(\"MEAN(transactions.ZSCORE(transactions.amount)|interval=P30D)\") over ()) / nullif(stddev(\"MEAN(transactions.ZSCORE(transactions.amount)|interval=P30D)\") over (), 0)) as \"ZSCORE(accounts.MEAN(transactions.ZSCORE(transactions.amount)|interval=P30D))\" , ((\"MEAN(transactions.ZSCORE(transactions.amount)|interval=P7D)\" - avg(\"MEAN(transactions.ZSCORE(transactions.amount)|interval=P7D)\") over ()) / nullif(stddev(\"MEAN(transactions.ZSCORE(transactions.amount)|interval=P7D)\") over (), 0)) as \"ZSCORE(accounts.MEAN(transactions.ZSCORE(transactions.amount)|interval=P7D))\" , ((\"MEAN(transactions.amount)\" - avg(\"MEAN(transactions.amount)\") over ()) / nullif(stddev(\"MEAN(transactions.amount)\") over (), 0)) as \"ZSCORE(accounts.MEAN(transactions.amount))\" , ((\"MEAN(transactions.amount|interval=P30D)\" - avg(\"MEAN(transactions.amount|interval=P30D)\") over ()) / nullif(stddev(\"MEAN(transactions.amount|interval=P30D)\") over (), 0)) as \"ZSCORE(accounts.MEAN(transactions.amount|interval=P30D))\" , ((\"MEAN(transactions.amount|interval=P7D)\" - avg(\"MEAN(transactions.amount|interval=P7D)\") over ()) / nullif(stddev(\"MEAN(transactions.amount|interval=P7D)\") over (), 0)) as \"ZSCORE(accounts.MEAN(transactions.amount|interval=P7D))\" , ((\"P95(transactions.BIN(transactions.amount))\" - avg(\"P95(transactions.BIN(transactions.amount))\") over ()) / nullif(stddev(\"P95(transactions.BIN(transactions.amount))\") over (), 0)) as \"ZSCORE(accounts.P95(transactions.BIN(transactions.amount)))\" , ((\"P95(transactions.BIN(transactions.amount)|interval=P30D)\" - avg(\"P95(transactions.BIN(transactions.amount)|interval=P30D)\") over ()) / nullif(stddev(\"P95(transactions.BIN(transactions.amount)|interval=P30D)\") over (), 0)) as \"ZSCORE(accounts.P95(transactions.BIN(transactions.amount)|interval=P30D))\" , ((\"P95(transactions.BIN(transactions.amount)|interval=P7D)\" - avg(\"P95(transactions.BIN(transactions.amount)|interval=P7D)\") over ()) / nullif(stddev(\"P95(transactions.BIN(transactions.amount)|interval=P7D)\") over (), 0)) as \"ZSCORE(accounts.P95(transactions.BIN(transactions.amount)|interval=P7D))\" , ((\"P95(transactions.LOG1P(transactions.amount))\" - avg(\"P95(transactions.LOG1P(transactions.amount))\") over ()) / nullif(stddev(\"P95(transactions.LOG1P(transactions.amount))\") over (), 0)) as \"ZSCORE(accounts.P95(transactions.LOG1P(transactions.amount)))\" , ((\"P95(transactions.LOG1P(transactions.amount)|interval=P30D)\" - avg(\"P95(transactions.LOG1P(transactions.amount)|interval=P30D)\") over ()) / nullif(stddev(\"P95(transactions.LOG1P(transactions.amount)|interval=P30D)\") over (), 0)) as \"ZSCORE(accounts.P95(transactions.LOG1P(transactions.amount)|interval=P30D))\" , ((\"P95(transactions.LOG1P(transactions.amount)|interval=P7D)\" - avg(\"P95(transactions.LOG1P(transactions.amount)|interval=P7D)\") over ()) / nullif(stddev(\"P95(transactions.LOG1P(transactions.amount)|interval=P7D)\") over (), 0)) as \"ZSCORE(accounts.P95(transactions.LOG1P(transactions.amount)|interval=P7D))\" , ((\"P95(transactions.ZSCORE(transactions.amount))\" - avg(\"P95(transactions.ZSCORE(transactions.amount))\") over ()) / nullif(stddev(\"P95(transactions.ZSCORE(transactions.amount))\") over (), 0)) as \"ZSCORE(accounts.P95(transactions.ZSCORE(transactions.amount)))\" , ((\"P95(transactions.ZSCORE(transactions.amount)|interval=P30D)\" - avg(\"P95(transactions.ZSCORE(transactions.amount)|interval=P30D)\") over ()) / nullif(stddev(\"P95(transactions.ZSCORE(transactions.amount)|interval=P30D)\") over (), 0)) as \"ZSCORE(accounts.P95(transactions.ZSCORE(transactions.amount)|interval=P30D))\" , ((\"P95(transactions.ZSCORE(transactions.amount)|interval=P7D)\" - avg(\"P95(transactions.ZSCORE(transactions.amount)|interval=P7D)\") over ()) / nullif(stddev(\"P95(transactions.ZSCORE(transactions.amount)|interval=P7D)\") over (), 0)) as \"ZSCORE(accounts.P95(transactions.ZSCORE(transactions.amount)|interval=P7D))\" , ((\"P95(transactions.amount)\" - avg(\"P95(transactions.amount)\") over ()) / nullif(stddev(\"P95(transactions.amount)\") over (), 0)) as \"ZSCORE(accounts.P95(transactions.amount))\" , ((\"P95(transactions.amount|interval=P30D)\" - avg(\"P95(transactions.amount|interval=P30D)\") over ()) / nullif(stddev(\"P95(transactions.amount|interval=P30D)\") over (), 0)) as \"ZSCORE(accounts.P95(transactions.amount|interval=P30D))\" , ((\"P95(transactions.amount|interval=P7D)\" - avg(\"P95(transactions.amount|interval=P7D)\") over ()) / nullif(stddev(\"P95(transactions.amount|interval=P7D)\") over (), 0)) as \"ZSCORE(accounts.P95(transactions.amount|interval=P7D))\" , ((\"RANGE(transactions.BIN(transactions.amount))\" - avg(\"RANGE(transactions.BIN(transactions.amount))\") over ()) / nullif(stddev(\"RANGE(transactions.BIN(transactions.amount))\") over (), 0)) as \"ZSCORE(accounts.RANGE(transactions.BIN(transactions.amount)))\" , ((\"RANGE(transactions.BIN(transactions.amount)|interval=P30D)\" - avg(\"RANGE(transactions.BIN(transactions.amount)|interval=P30D)\") over ()) / nullif(stddev(\"RANGE(transactions.BIN(transactions.amount)|interval=P30D)\") over (), 0)) as \"ZSCORE(accounts.RANGE(transactions.BIN(transactions.amount)|interval=P30D))\" , ((\"RANGE(transactions.BIN(transactions.amount)|interval=P7D)\" - avg(\"RANGE(transactions.BIN(transactions.amount)|interval=P7D)\") over ()) / nullif(stddev(\"RANGE(transactions.BIN(transactions.amount)|interval=P7D)\") over (), 0)) as \"ZSCORE(accounts.RANGE(transactions.BIN(transactions.amount)|interval=P7D))\" , ((\"RANGE(transactions.LOG1P(transactions.amount))\" - avg(\"RANGE(transactions.LOG1P(transactions.amount))\") over ()) / nullif(stddev(\"RANGE(transactions.LOG1P(transactions.amount))\") over (), 0)) as \"ZSCORE(accounts.RANGE(transactions.LOG1P(transactions.amount)))\" , ((\"RANGE(transactions.LOG1P(transactions.amount)|interval=P30D)\" - avg(\"RANGE(transactions.LOG1P(transactions.amount)|interval=P30D)\") over ()) / nullif(stddev(\"RANGE(transactions.LOG1P(transactions.amount)|interval=P30D)\") over (), 0)) as \"ZSCORE(accounts.RANGE(transactions.LOG1P(transactions.amount)|interval=P30D))\" , ((\"RANGE(transactions.LOG1P(transactions.amount)|interval=P7D)\" - avg(\"RANGE(transactions.LOG1P(transactions.amount)|interval=P7D)\") over ()) / nullif(stddev(\"RANGE(transactions.LOG1P(transactions.amount)|interval=P7D)\") over (), 0)) as \"ZSCORE(accounts.RANGE(transactions.LOG1P(transactions.amount)|interval=P7D))\" , ((\"RANGE(transactions.ZSCORE(transactions.amount))\" - avg(\"RANGE(transactions.ZSCORE(transactions.amount))\") over ()) / nullif(stddev(\"RANGE(transactions.ZSCORE(transactions.amount))\") over (), 0)) as \"ZSCORE(accounts.RANGE(transactions.ZSCORE(transactions.amount)))\" , ((\"RANGE(transactions.ZSCORE(transactions.amount)|interval=P30D)\" - avg(\"RANGE(transactions.ZSCORE(transactions.amount)|interval=P30D)\") over ()) / nullif(stddev(\"RANGE(transactions.ZSCORE(transactions.amount)|interval=P30D)\") over (), 0)) as \"ZSCORE(accounts.RANGE(transactions.ZSCORE(transactions.amount)|interval=P30D))\" , ((\"RANGE(transactions.ZSCORE(transactions.amount)|interval=P7D)\" - avg(\"RANGE(transactions.ZSCORE(transactions.amount)|interval=P7D)\") over ()) / nullif(stddev(\"RANGE(transactions.ZSCORE(transactions.amount)|interval=P7D)\") over (), 0)) as \"ZSCORE(accounts.RANGE(transactions.ZSCORE(transactions.amount)|interval=P7D))\" , ((\"RANGE(transactions.amount)\" - avg(\"RANGE(transactions.amount)\") over ()) / nullif(stddev(\"RANGE(transactions.amount)\") over (), 0)) as \"ZSCORE(accounts.RANGE(transactions.amount))\" , ((\"RANGE(transactions.amount|interval=P30D)\" - avg(\"RANGE(transactions.amount|interval=P30D)\") over ()) / nullif(stddev(\"RANGE(transactions.amount|interval=P30D)\") over (), 0)) as \"ZSCORE(accounts.RANGE(transactions.amount|interval=P30D))\" , ((\"RANGE(transactions.amount|interval=P7D)\" - avg(\"RANGE(transactions.amount|interval=P7D)\") over ()) / nullif(stddev(\"RANGE(transactions.amount|interval=P7D)\") over (), 0)) as \"ZSCORE(accounts.RANGE(transactions.amount|interval=P7D))\" , ((credit_limit - avg(credit_limit) over ()) / nullif(stddev(credit_limit) over (), 0)) as \"ZSCORE(accounts.credit_limit)\" , account_type as account_type, credit_limit as credit_limit\n", " from accounts_synth _ego\n", " )\n", " \n", "\n", " select * from accounts_transform\n", " ) as t\n", "\n", " order by aod.as_of_date\n", " \n", "================================================================================\n" ] } ], "source": [ "sql = featurizer.query\n", "print(\"Generated SQL (with custom primitives):\")\n", "print(\"=\" * 80)\n", "print(sql)\n", "print(\"=\" * 80)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 7. Summary\n", "\n", "In this tutorial, we learned:\n", "\n", "1. **Check built-ins first**: Featurizer now ships with 43 aggregations and 71 transformations covering most common needs\n", "2. **Registration pattern**: How to create custom primitives when built-ins aren't enough\n", "3. **Implementation**: Subclass, implement `to_sql()`, register, use\n", "4. **Best practices**: Always return new `Feature` instances from transformers\n", "\n", "### When to Write Custom Primitives\n", "- Domain-specific metrics (e.g., financial ratios, medical scores)\n", "- Complex SQL expressions not covered by built-ins\n", "- Organization-specific feature definitions" ] }, { "cell_type": "code", "execution_count": 7, "metadata": { "execution": { "iopub.execute_input": "2026-06-21T19:29:57.029230Z", "iopub.status.busy": "2026-06-21T19:29:57.029178Z", "iopub.status.idle": "2026-06-21T19:29:57.030776Z", "shell.execute_reply": "2026-06-21T19:29:57.030455Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Custom Primitives Summary\n", "========================================\n", "Built-in aggregations: 69\n", "Built-in transformations: 83\n", "Custom aggregations: 3 (median, p95, range)\n", "Custom transformations: 3 (log, zscore, bin)\n", "Total features generated: 187\n" ] } ], "source": [ "print(\"Custom Primitives Summary\")\n", "print(\"=\" * 40)\n", "print(f\"Built-in aggregations: {len(aggs)}\")\n", "print(f\"Built-in transformations: {len(transforms)}\")\n", "print(f\"Custom aggregations: 3 (median, p95, range)\")\n", "print(f\"Custom transformations: 3 (log, zscore, bin)\")\n", "print(f\"Total features generated: {len(target_features)}\")" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.12.13" } }, "nbformat": 4, "nbformat_minor": 4 }