v_fee_comparison (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, privatkonto_annual, privatkonto_freq, debit_card_annual, credit_card_standard_annual, transfer_domestic_ebanking, transfer_sepa_ebanking, debit_fx_markup_pct, atm_other_bank_fee, atm_abroad_fee

Next page

CREATE VIEW v_fee_comparison AS
SELECT
    b.country,
    b.id AS bank_id,
    b.name AS bank_name,
    b.bank_type,
    MAX(CASE WHEN f.fee_type = 'account_mgmt' AND p.category = 'current_account' AND p.target_group = 'standard' THEN f.amount END) AS privatkonto_annual,
    MAX(CASE WHEN f.fee_type = 'account_mgmt' AND p.category = 'current_account' AND p.target_group = 'standard' THEN f.frequency END) AS privatkonto_freq,
    MAX(CASE WHEN f.fee_type = 'annual_fee' AND p.category = 'debit_card' THEN f.amount END) AS debit_card_annual,
    MAX(CASE WHEN f.fee_type = 'annual_fee' AND p.category = 'credit_card' AND p.target_group = 'standard' THEN f.amount END) AS credit_card_standard_annual,
    MAX(CASE WHEN f.fee_type = 'transfer_domestic' AND f.channel = 'ebanking' THEN f.amount END) AS transfer_domestic_ebanking,
    MAX(CASE WHEN f.fee_type = 'transfer_sepa' AND f.channel = 'ebanking' THEN f.amount END) AS transfer_sepa_ebanking,
    MAX(CASE WHEN f.fee_type = 'fx_markup' AND p.category = 'debit_card' THEN f.amount END) AS debit_fx_markup_pct,
    MAX(CASE WHEN f.fee_type = 'atm_domestic_other' AND p.category = 'debit_card' THEN f.amount END) AS atm_other_bank_fee,
    MAX(CASE WHEN f.fee_type = 'atm_abroad' AND p.category = 'debit_card' THEN f.amount END) AS atm_abroad_fee
FROM bank b
LEFT JOIN product p ON p.bank_id = b.id
LEFT JOIN fee f ON f.product_id = p.id
GROUP BY b.id;