Applicable Fee Ids

Applicable Fee IDs — Solution Guide

Dataset Files

acquirer_countries.csv is NOT needed for Applicable_Fee_IDs questions. Do NOT use it to compute intracountry.

Fee Rule Matching Logic

A fee applies when every non-null/non-empty condition in the fee record matches the transaction or merchant characteristic.

Null/Empty = Applies to All

• List fields (account_type, aci, merchant_category_code): [] or None → matches all values
• Scalar fields (capture_delay, is_credit, intracountry, monthly_volume, monthly_fraud_level): None → matches all values

intracountry Field

• In fees.json: 0.0 = international (False), 1.0 = domestic (True), None = both
• Always compute from payments.csv directly: intracountry = (issuing_country == acquirer_country)
• NEVER look up acquirer country from acquirer_countries.csv — the acquirer_country column in payments.csv already contains the per-transaction acquiring country. Using acquirer_countries.csv will produce wrong combinations and incorrect results.

capture_delay Mapping

Merchant data has numeric strings or text. Map to fee categories:

def map_capture_delay(raw):
    if raw in ('immediate', 'manual'):
        return raw
    try:
        n = int(raw)
        if n < 3:    return '<3'
        elif n <= 5: return '3-5'
        else:        return '>5'
    except:
        return raw

Monthly Volume and Fraud Brackets

def get_monthly_bracket(month_txs):
    total_vol = month_txs['eur_amount'].sum()
    fraud_vol = month_txs[month_txs['has_fraudulent_dispute'] == True]['eur_amount'].sum()
    fraud_pct = (fraud_vol / total_vol * 100) if total_vol > 0 else 0

    if total_vol < 100_000:       vol_bracket = '<100k'
    elif total_vol < 1_000_000:   vol_bracket = '100k-1m'
    elif total_vol < 5_000_000:   vol_bracket = '1m-5m'
    else:                          vol_bracket = '>5m'

    if fraud_pct < 7.2:            fraud_bracket = '<7.2%'
    elif fraud_pct < 7.7:          fraud_bracket = '7.2%-7.7%'
    elif fraud_pct < 8.3:          fraud_bracket = '7.7%-8.3%'
    else:                           fraud_bracket = '>8.3%'

    return vol_bracket, fraud_bracket

Natural calendar months (per manual): always use full natural months (Jan 1–31, Feb 1–28, etc.).

Month day-range reference (non-leap year 2023):

Jan: 1–31    Feb: 32–59   Mar: 60–90   Apr: 91–120
May: 121–151 Jun: 152–181 Jul: 182–212 Aug: 213–243
Sep: 244–273 Oct: 274–304 Nov: 305–334 Dec: 335–365

Question Types and Solutions

Type 1: Merchant + Day Query

"What fee IDs apply to Merchant_X on day N of 2023?"

import pandas as pd, json

with open('fees.json') as f:          fees = json.load(f)
with open('merchant_data.json') as f: merchants = json.load(f)
payments = pd.read_csv('payments.csv')

# Merchant attributes
merchant = next(m for m in merchants if m['merchant'] == 'MERCHANT_NAME')
acct_type = merchant['account_type']
mcc       = merchant['merchant_category_code']
cap_delay = map_capture_delay(merchant['capture_delay'])

# Filter transactions for the target day
DAY = N  # e.g., 200 for day 200
period_tx = payments[(payments['merchant'] == 'MERCHANT_NAME') &
                     (payments['year'] == 2023) &
                     (payments['day_of_year'] == DAY)].copy()

# Compute intracountry from payments.csv columns directly
period_tx['intracountry'] = period_tx['issuing_country'] == period_tx['acquirer_country']

# Unique (card_scheme, is_credit, aci, intracountry) combos that actually occurred
combos = period_tx[['card_scheme', 'is_credit', 'aci', 'intracountry']].drop_duplicates()

# Monthly stats: use the natural calendar month containing DAY
MONTH_START, MONTH_END = S, E  # look up in the day-range table above
month_tx = payments[(payments['merchant'] == 'MERCHANT_NAME') &
                    (payments['year'] == 2023) &
                    (payments['day_of_year'] >= MONTH_START) &
                    (payments['day_of_year'] <= MONTH_END)]
vol_bracket, fraud_bracket = get_monthly_bracket(month_tx)

# Match fees
def fee_applies(fee, cs, cr, ac, ic):
    if fee['card_scheme'] is not None and fee['card_scheme'] != cs:              return False
    if fee['account_type'] and acct_type not in fee['account_type']:             return False
    if fee['capture_delay'] is not None and fee['capture_delay'] != cap_delay:   return False
    if fee['merchant_category_code'] and mcc not in fee['merchant_category_code']: return False
    if fee['is_credit'] is not None and fee['is_credit'] != cr:                  return False
    if fee['aci'] and ac not in fee['aci']:                                       return False
    if fee['intracountry'] is not None and bool(fee['intracountry']) != ic:      return False
    if fee['monthly_volume'] is not None and fee['monthly_volume'] != vol_bracket:      return False
    if fee['monthly_fraud_level'] is not None and fee['monthly_fraud_level'] != fraud_bracket: return False
    return True

applicable = set()
for _, row in combos.iterrows():
    for fee in fees:
        if fee_applies(fee, row['card_scheme'], row['is_credit'], row['aci'], row['intracountry']):
            applicable.add(fee['ID'])

print(', '.join(str(x) for x in sorted(applicable)))

Type 2: Merchant + Month Query

"What fee IDs apply to Merchant_X in [month] 2023?"

Same as Type 1 but:

• period_tx = all transactions in the target month (use the day range from the table)
• month_tx = same as period_tx (month query → month stats use the same period)
• Apply monthly constraints normally

Type 3: Merchant + Full-Year Query

"What fee IDs apply to Merchant_X in 2023?"

Same as Type 1 but:

• period_tx = all transactions in 2023 (year == 2023)
• Skip monthly_volume and monthly_fraud_level checks entirely — no single month represents the full year, so treat these fee conditions as non-applicable (skip them, don't filter on them)

def fee_applies_yearly(fee, cs, cr, ac, ic):
    if fee['card_scheme'] is not None and fee['card_scheme'] != cs:              return False
    if fee['account_type'] and acct_type not in fee['account_type']:             return False
    if fee['capture_delay'] is not None and fee['capture_delay'] != cap_delay:   return False
    if fee['merchant_category_code'] and mcc not in fee['merchant_category_code']: return False
    if fee['is_credit'] is not None and fee['is_credit'] != cr:                  return False
    if fee['aci'] and ac not in fee['aci']:                                       return False
    if fee['intracountry'] is not None and bool(fee['intracountry']) != ic:      return False
    # monthly_volume and monthly_fraud_level are skipped for full-year queries
    return True

Type 4: Simple Attribute Filter (No payment data needed)

"What fee IDs apply to account_type=F and aci=A?"

import json
with open('fees.json') as f: fees = json.load(f)

matching = [fee['ID'] for fee in fees
            if (not fee['account_type'] or 'F' in fee['account_type'])
            and (not fee['aci'] or 'A' in fee['aci'])]
print(', '.join(str(x) for x in sorted(matching)))

Type 5: Reverse Lookup — Fee → Merchants

"Which merchants were affected by fee ID 709 in 2023?"

with open('fees.json') as f: fees = json.load(f)
fee = next(f for f in fees if f['ID'] == 709)
payments = pd.read_csv('payments.csv')
mask = (payments['year'] == 2023)
if fee['card_scheme']:      mask &= (payments['card_scheme'] == fee['card_scheme'])
if fee['is_credit'] is not None: mask &= (payments['is_credit'] == fee['is_credit'])
if fee['aci']:              mask &= (payments['aci'].isin(fee['aci']))
# For merchant-level conditions (account_type, MCC, capture_delay), join merchant_data.json
affected = sorted(payments[mask]['merchant'].unique())
print(', '.join(affected))

Critical Pitfalls

1. intracountry MUST come from payments.csv['acquirer_country'], NOT from acquirer_countries.csv. The acquirer_country column in each payment row is the actual acquiring country for that transaction. Using acquirer_countries.csv to look up the merchant's acquirer and hardcoding its country as the acquirer country will produce wrong transaction combinations and wrong fee matches.

2. Monthly constraints are decisive — a fee with monthly_volume='1m-5m' does NOT apply to a merchant with <100k monthly volume. Always compute actual monthly stats from transaction data.

3. capture_delay mapping is required — '1' or '2' → '<3'; '7' → '>5'. A fee with capture_delay='<3' won't match a merchant with capture_delay='manual'.

4. Empty list [] = applies to all, same as None. Never treat [] as "no match".

5. Use actual transaction combos from payments.csv for the specific period, not all theoretical permutations. A fee only applies if a matching transaction actually occurred.

6. Output the answer as soon as fee matching is complete. Avoid excessive verification loops — they waste turns and risk hitting context limits before producing a result.

7. Format: comma-separated integers sorted ascending. Empty string if no fees apply.