04 · Custom Primitives

View on GitHub · Download .ipynb

This notebook demonstrates how to extend Featurizer with custom aggregation and transformation primitives using a financial analytics scenario.

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.

1. Built-in Primitives That Cover Common Needs

Before writing custom primitives, check if your need is already covered. Featurizer now ships with 43 aggregations and 71 transformations.

import sys
from pathlib import Path

sys.path.insert(0, str(Path.cwd().parent.parent))

from featurizer.primitives.utils import list_aggregations, list_transformations

aggs = sorted(list_aggregations())
transforms = sorted(list_transformations())

print(f"Built-in aggregations ({len(aggs)}):")
print(f"  {', '.join(aggs)}")
print(f"\nBuilt-in transformations ({len(transforms)}):")
print(f"  {', '.join(transforms)}")

Built-in aggregations (69):
  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

Built-in transformations (83):
  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

Built-in Equivalents of This Example’s Custom Primitives

Custom (this example)	Built-in name	Type
`Median`	`median`	aggregation
`Percentile95`	`p95`	aggregation
`Range`	`range`	aggregation
`Log`	`ln`	transformation
`ZScore`	`cross_entity_zscore`	transformation

To use built-in versions, no custom code is needed — just reference them by name.

2. The Custom Registration Pattern

When you need a truly custom primitive (e.g., domain-specific financial metrics), Featurizer provides a registration API. Let’s walk through the pattern.

with open("custom_primitives.py") as f:
    print(f.read())

"""Custom aggregation and transformation primitives for financial analytics.

These demonstrate the current primitive API and emit PostgreSQL-valid SQL:

- An **aggregation** subclasses ``Aggregator`` (or a richer base such as
  ``OrderedSetAggregator``) and overrides ``_build_aggregate_expression`` to
  return the SQL aggregate; the base wraps it into a ``Feature``. Interval
  variants pass ``interval`` so the expression can add the ``daterange`` filter.
- A **transformation** subclasses ``Transformer`` and overrides
  ``_build_transformer_call`` to return a scalar SQL expression over the
  current row; the base wraps it into a ``Feature``.

Register *instances* (not classes) via ``register_aggregation`` /
``register_transformer``, then select them by name in ``config.yaml``.
"""

from featurizer.primitives.aggregations import Aggregator, OrderedSetAggregator
from featurizer.primitives.transformations import Transformer
from featurizer.primitives.utils import register_aggregation, register_transformer

# ============================================================================
# Custom Aggregations
# ============================================================================


class Range(Aggregator):
    """Spread of a value: ``max(x) - min(x)``.

    PostgreSQL's ``FILTER`` clause attaches to a single aggregate, so for an
    interval window each of ``max`` and ``min`` is filtered independently.
    """

    def __init__(self):
        super().__init__(name="range")

    def _agg(self, fn, feature, interval):
        expr = f"{fn}({feature.name})"
        if interval and feature.entity and feature.entity.temporal_ix:
            event_date = feature.entity.temporal_ix.name
            daterange = (
                f"daterange((aod.as_of_date - interval '{interval}')::date, "
                f"aod.as_of_date::date, '[]')"
            )
            expr += f" filter (where {daterange} @> {event_date}::date)"
        return expr

    def _build_aggregate_expression(self, feature, interval=None):
        return f"({self._agg('max', feature, interval)} - {self._agg('min', feature, interval)})"


class Percentile95(OrderedSetAggregator):
    """95th percentile via the ordered-set base (handles WITHIN GROUP + interval)."""

    def __init__(self):
        super().__init__(name="p95", aggregate="percentile_cont", direct_argument=0.95)


# ============================================================================
# Custom Transformations
# ============================================================================


class Log1p(Transformer):
    """Natural log with a +1 offset: ``ln(x + 1)``.

    Guarded with a CASE: the logarithm is undefined for ``x + 1 <= 0``, and this
    data has negative values (withdrawals), so those map to NULL rather than
    raising. (Without the guard PostgreSQL errors on the whole query.)
    """

    def __init__(self):
        super().__init__(name="log1p")

    def _build_transformer_call(self, feature):
        return f"case when {feature.name} + 1 > 0 then ln({feature.name} + 1) end"


class ZScore(Transformer):
    """Z-score standardization across the rows in scope (``OVER ()``)."""

    def __init__(self):
        super().__init__(name="zscore")

    def _build_transformer_call(self, feature):
        return (
            f"(({feature.name} - avg({feature.name}) over ()) "
            f"/ nullif(stddev({feature.name}) over (), 0))"
        )


class BinCount(Transformer):
    """Discretize into 5 equal-width bins (0–4) across the rows in scope."""

    def __init__(self):
        super().__init__(name="bin", output_type="numeric")

    def _build_transformer_call(self, feature):
        f = feature.name
        return (
            f"case when {f} is null then null "
            f"else cast(floor(5 * ({f} - min({f}) over ()) "
            f"/ nullif(max({f}) over () - min({f}) over (), 0)) as integer) end"
        )


# ============================================================================
# Registration
# ============================================================================


def register_all_custom_primitives():
    """Register all custom primitives with the feature system."""
    register_aggregation("range", Range())
    register_aggregation("p95", Percentile95())

    register_transformer("log1p", Log1p())
    register_transformer("zscore", ZScore())
    register_transformer("bin", BinCount())

    print("✓ Registered custom primitives:")
    print("  Aggregations: range, p95")
    print("  Transformations: log1p, zscore, bin")

Key Steps:

Subclass Aggregation or Transformation from featurizer.primitives.abstractions
Implement to_sql(feature, alias) returning a SQL expression
Register via register_aggregation() or register_transformation()
Call register_all_custom_primitives() before creating the Featurizer

Important: Transformations must return new Feature instances (never mutate) to preserve hashing semantics.

3. Setup and Registration

import sys
from pathlib import Path

# This tutorial is database-free: it loads the config and inspects the
# synthesized features and the generated SQL — none of which touch a
# database. To execute the features against PostgreSQL, run
# `just example 04-custom-primitives` (create_data.py + run_example.py).
sys.path.insert(0, str(Path.cwd().parent.parent))

# Custom primitives must be registered BEFORE creating the Featurizer.
from custom_primitives import register_all_custom_primitives

register_all_custom_primitives()

✓ Registered custom primitives:
  Aggregations: range, p95
  Transformations: log1p, zscore, bin

4. Create Featurizer with Custom Primitives

from featurizer import Featurizer

featurizer = Featurizer("config.yaml")

print(f"Target entity: {featurizer.target.alias}")
print(f"Max depth: {featurizer.max_depth}")
print(f"Intervals: {featurizer.intervals}")

target_features = featurizer.features[featurizer.target.alias]
print(f"Generated features: {len(target_features)}")

[32m2026-06-21 13:29:57.017[0m | [34m[1mDEBUG   [0m | [36mfeaturizer.planner[0m:[36mplan[0m:[36m230[0m - [34m[1mStarting feature build for target accounts[0m


[32m2026-06-21 13:29:57.018[0m | [34m[1mDEBUG   [0m | [36mfeaturizer.planner[0m:[36m_build_features[0m:[36m256[0m - [34m[1mbuild_features(accounts) depth=0[0m


[32m2026-06-21 13:29:57.018[0m | [34m[1mDEBUG   [0m | [36mfeaturizer.planner[0m:[36m_build_features[0m:[36m256[0m - [34m[1mbuild_features(transactions) depth=1[0m


[32m2026-06-21 13:29:57.018[0m | [1mINFO    [0m | [36mfeaturizer.planner[0m:[36m_build_features[0m:[36m276[0m - [1mMaximum recursion depth reached at depth 2; materializing transactions without traversing further.[0m


[32m2026-06-21 13:29:57.018[0m | [34m[1mDEBUG   [0m | [36mfeaturizer.planner[0m:[36m_build_aggregations[0m:[36m1014[0m - [34m[1mProcessing backward relationship Entity(accounts).account_id -> Entity(transactions).account_id[0m


[32m2026-06-21 13:29:57.019[0m | [33m[1mWARNING [0m | [36mfeaturizer.planner[0m:[36m_apply_direct_roles[0m:[36m1155[0m - [33m[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.[0m


Target entity: accounts
Max depth: 2
Intervals: ['P7D', 'P30D']
Generated features: 187

5. Examining Features from Custom Primitives

Let’s see which features use our custom primitives.

custom_names = ["median", "p95", "range", "log", "zscore", "bin"]
custom_features = [
    f for f in target_features if any(prim in f.name.lower() for prim in custom_names)
]

print(f"Features using custom primitives: {len(custom_features)}")
for f in sorted(custom_features, key=lambda x: x.name)[:15]:
    print(f"  - {f.name}")

Features using custom primitives: 171
  - "BIN(accounts.COUNT(transactions.transaction_date))"
  - "BIN(accounts.COUNT(transactions.transaction_date|interval=P30D))"
  - "BIN(accounts.COUNT(transactions.transaction_date|interval=P7D))"
  - "BIN(accounts.COUNT(transactions.transaction_id))"
  - "BIN(accounts.COUNT(transactions.transaction_id|interval=P30D))"
  - "BIN(accounts.COUNT(transactions.transaction_id|interval=P7D))"
  - "BIN(accounts.COUNT(transactions.transaction_type))"
  - "BIN(accounts.COUNT(transactions.transaction_type|interval=P30D))"
  - "BIN(accounts.COUNT(transactions.transaction_type|interval=P7D))"
  - "BIN(accounts.MEAN(transactions.BIN(transactions.amount)))"
  - "BIN(accounts.MEAN(transactions.BIN(transactions.amount)|interval=P30D))"
  - "BIN(accounts.MEAN(transactions.BIN(transactions.amount)|interval=P7D))"
  - "BIN(accounts.MEAN(transactions.LOG1P(transactions.amount)))"
  - "BIN(accounts.MEAN(transactions.LOG1P(transactions.amount)|interval=P30D))"
  - "BIN(accounts.MEAN(transactions.LOG1P(transactions.amount)|interval=P7D))"

6. SQL Comparison: Custom vs Built-in

Let’s compare the SQL generated by a custom primitive vs its built-in equivalent.

sql = featurizer.query
print("Generated SQL (with custom primitives):")
print("=" * 80)
print(sql)
print("=" * 80)

[32m2026-06-21 13:29:57.026[0m | [34m[1mDEBUG   [0m | [36mfeaturizer.sql[0m:[36mrender[0m:[36m40[0m - [34m[1mRendered SQL for target 'accounts': 5 CTEs, 60572 chars[0m


Generated SQL (with custom primitives):
================================================================================

        select aod.as_of_date, t.*
        from as_of_dates as aod
        cross join lateral (

        with


        -- sythetize aggregations and direct features for transactions
        transactions_synth as (
        select
        transactions.transaction_id, transactions.transaction_date, transactions.account_id, amount, transaction_type
        from transactions


        )
        ,
        -- transform transactions
        transactions_transform as (
        select
        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
        from transactions_synth _ego
        )
        ,
        -- Aggregate for accounts
        transactions_aggs_for_accounts as (
        select
        transactions_transform.account_id,
        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)"
        from transactions_transform
         where transaction_date <= aod.as_of_date
        group by account_id
        )
        ,
        -- sythetize aggregations and direct features for accounts
        accounts_synth as (
        select
        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
        from accounts
         left join
         transactions_aggs_for_accounts on transactions_aggs_for_accounts.account_id = accounts.account_id
        )
        ,
        -- transform accounts
        accounts_transform as (
        select
        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
        from accounts_synth _ego
        )


        select * from accounts_transform
        ) as t

        order by aod.as_of_date

================================================================================

7. Summary

In this tutorial, we learned:

Check built-ins first: Featurizer now ships with 43 aggregations and 71 transformations covering most common needs
Registration pattern: How to create custom primitives when built-ins aren’t enough
Implementation: Subclass, implement to_sql(), register, use
Best practices: Always return new Feature instances from transformers

When to Write Custom Primitives

Domain-specific metrics (e.g., financial ratios, medical scores)
Complex SQL expressions not covered by built-ins
Organization-specific feature definitions

print("Custom Primitives Summary")
print("=" * 40)
print(f"Built-in aggregations: {len(aggs)}")
print(f"Built-in transformations: {len(transforms)}")
print(f"Custom aggregations: 3 (median, p95, range)")
print(f"Custom transformations: 3 (log, zscore, bin)")
print(f"Total features generated: {len(target_features)}")

Custom Primitives Summary
========================================
Built-in aggregations: 69
Built-in transformations: 83
Custom aggregations: 3 (median, p95, range)
Custom transformations: 3 (log, zscore, bin)
Total features generated: 187