bank_computed (view)

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

420 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Suggested facets: country, bank_type, finma_category, parent_group_relation, too_big_to_fail, survival_mechanism

Next page

CREATE VIEW bank_computed AS
SELECT
    b.id,
    b.name,
    b.country,
    b.bank_type,
    bp.operational_since,
    bp.finma_category,
    bp.staatsgarantie,
    bp.parent_group_relation,
    bp.too_big_to_fail,
    -- 1. Lindy years: expected remaining lifespan ≈ years already survived
    CASE WHEN bp.operational_since IS NOT NULL
         THEN CAST(strftime('%Y', 'now') AS INTEGER) - bp.operational_since
         ELSE NULL END AS lindy_years,
    -- 2. Survival mechanism: what protects this bank's continuity (priority cascade)
    CASE WHEN bp.staatsgarantie IN ('full', 'limited') THEN 'staatsgarantie'
         WHEN bp.too_big_to_fail = 1 THEN 'tbtf'
         WHEN bp.parent_group_relation = 'network_member' THEN 'cooperative'
         WHEN bp.parent_group_relation = 'subsidiary' THEN 'subsidiary'
         ELSE 'independent' END AS survival_mechanism
FROM bank b
LEFT JOIN bank_profile bp ON b.id = bp.bank_id;