pricing_rule

Data license: CC-BY-4.0 · Data source: bankproducts.info

30 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Suggested facets: bank_id, condition_type, effect_type, effect_magnitude, valid_from, valid_from (date)

CREATE TABLE pricing_rule (
    id                  TEXT PRIMARY KEY,       -- owkb:privatkonto_basis:rabatt_or_gate
    product_id          TEXT NOT NULL REFERENCES product(id),
    bank_id             TEXT NOT NULL REFERENCES bank(id),  -- denormalized
    -- 5 condition families surfaced batches 1-25:
    --   'cross_sell_asset_threshold'          — OWKB OKB-Rabatt (CHF 30k Vermoegen)
    --   'cross_sell_product_hold'             — OWKB Kredit-/Karten-Hold
    --   'cross_sell_relationship_aggregate'   — EKI Sum-of-relationship (10k Aggregat)
    --   'insurance_bundle'                    — Baloise Cross-Segment (bank+insurance)
    --   'package_tier_level'                  — LLB Sparzins-abhaengig-von-Package
    --   'share_ownership_min'                 — BBO 10 Namenaktien
    --   'share_ownership_escalating'          — Acrevis 11/55 (Member/Premium)
    --   'age_max'                             — AIKB Junge-Waiver bis 25
    --   'behavioral_monthly_deposit'          — Acrevis Kaspar CHF 100/Monat
    --   'vermoegen_progressive'               — Sparkasse Schwyz 3-Tier
    --   'additive_cross_sell_bonus'           — BCVs CHF 1.50/Kriterium
    --   'interest_boost_program'              — BCGE Boost 6-Bonus
    condition_type      TEXT NOT NULL,
    -- What triggers the rule. Free-text description (bank-specific phrasing).
    -- Examples: "CHF 30k Kundenvermoegen ODER Hypothek CHF 100k ODER OKB Karte";
    -- "5 Anteilscheine bei Biene deponiert"; "12k Karten-Umsatz pro Jahr".
    trigger_description TEXT NOT NULL,
    -- Numeric threshold (CHF) if applicable. NULL for non-numeric triggers
    -- ("member ownership required").
    trigger_threshold_chf REAL
        CHECK (trigger_threshold_chf IS NULL OR trigger_threshold_chf >= 0),
    -- What the rule does:
    --   'fee_waived'          — komplette Fee-Waiver (Kontogebuehr entfaellt)
    --   'fee_reduced'         — Fee-Reduktion (halbierte Gebuehr, Rabatt-Baustein)
    --   'rate_boost_bps'      — Zinssatz-Erhoehung (Aktionaers-Sparkonto)
    --   'commission_rebate_pct' — Prozent-Rabatt auf Kommission
    --   'card_included'       — Karten-Inklusion im Bundle
    --   'unlimited_included'  — Unlimited-Ausfuehrung im Bundle
    effect_type         TEXT NOT NULL,
    -- Numeric effect magnitude (bps for rate, CHF for fee, % for commission).
    -- NULL for effects without natural number (card_included).
    effect_magnitude    REAL,
    -- FK to the affected fee, if applicable. NULL when the rule affects a product-
    -- level property (product.access_tier, interest_rate, etc.) rather than a fee.
    affected_fee_id     TEXT REFERENCES fee(id),
    note                TEXT,                   -- narrative context
    source_page         INTEGER,                -- PDF page reference
    valid_from          TEXT                    -- YYYY-MM-DD when rule became active
);
CREATE INDEX idx_pricing_rule_product ON pricing_rule(product_id);
CREATE INDEX idx_pricing_rule_bank ON pricing_rule(bank_id);
CREATE INDEX idx_pricing_rule_type ON pricing_rule(condition_type);
CREATE INDEX idx_pricing_rule_effect ON pricing_rule(effect_type);