Quick Overview

This question evaluates proficiency in time-series feature engineering, leakage prevention, memory-efficient large-scale data processing, categorical target encoding, exponential weighting, and algorithmic complexity within the Data Manipulation (SQL/Python) domain, emphasizing practical application with pandas at scale.

Compute leakage-safe rolling features in pandas

Company: Freddie Mac

Role: Data Scientist

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Onsite

Using pandas on a 50M-row monthly panel with columns [loan_id, msa, month, property_type, delinquent_90dpd (0/1), upb], create features for each loan-month: (a) a 12-month rolling delinquency rate per (msa, property_type) that excludes the current month (strict t-1 window), (b) a target-encoded property_type delinquency rate per MSA using only data strictly before the current month (leave-one-time-step-out), and (c) an exponentially weighted default intensity per loan with half-life = 6 months. Return a DataFrame with one row per loan-month containing these features, leakage-safe. Explain how you would: (1) ensure memory efficiency under 16 GB RAM (categorical dtypes, downcasting, chunked joins, parquet scans), (2) guarantee time-order correctness after shuffles (sort indices, stable groupby; avoid groupby.apply pitfalls), and (3) unit test correctness with a small deterministic example covering edge cases (missing months, single-observation groups). Provide the big-O time/memory tradeoffs of your approach.

Overview: This question evaluates proficiency in time-series feature engineering, leakage prevention, memory-efficient large-scale data processing, categorical target encoding, exponential weighting, and algorithmic complexity within the Data Manipulation (SQL/Python) domain, emphasizing practical application with pandas at scale.

Read the full Freddie Mac Data Scientist interview experience this question came from

12‑month rolling delinquency rate by MSA and property type (leakage‑safe)

You are given a large monthly loan performance table with one row per loan and calendar month. For each loan-month, compute a 12-month rolling delinquency rate for its (msa, property_type) group that is leakage-safe. Define the rolling rate for a given row as the average of delinquent_90dpd over all rows with the same msa and property_type whose month falls in the 12 calendar months immediately before the row’s month (strictly earlier months only; do NOT include the current month in the window). If there is no prior data for that group in the past 12 months, the result should be NULL. Return one row per loan-month with: loan_id, msa, property_type, month, and the 12-month rolling delinquency rate.

Tables

loan_monthly_status(loan_id INT, msa VARCHAR(10), property_type VARCHAR(20), month DATE, delinquent_90dpd INT, upb DECIMAL(12,2))

Hints

  1. Use a window function (AVG) partitioned by msa and property_type.
  2. Define the frame as a time-based RANGE from 12 months preceding to 1 month preceding to exclude the current month.

Target-encoded delinquency rate by MSA and property type (leave-one-time-step-out)

Using the same monthly loan performance table, create a leakage-safe target encoding for property_type within each MSA. For each loan-month row, compute the target-encoded delinquency rate as the average of delinquent_90dpd over all rows with the same msa and property_type whose month is strictly earlier than the current row’s month (month < current month). Do not include the current month in the calculation. If a group (msa, property_type) has no earlier rows, the result should be NULL. Return one row per loan-month with: loan_id, msa, property_type, month, and this target-encoded delinquency rate.

Tables

loan_monthly_status(loan_id INT, msa VARCHAR(10), property_type VARCHAR(20), month DATE, delinquent_90dpd INT, upb DECIMAL(12,2))

Hints

  1. Use a cumulative window over (msa, property_type) ordered by month.
  2. Use ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING to exclude the current row from the average.

Exponentially weighted default intensity per loan with 6-month half-life

Using the same loan_monthly_status table, compute an exponentially weighted default intensity over time for each loan. For each loan_id and month, define the default intensity as a normalized exponentially weighted average of delinquent_90dpd over the current and all past months of that loan: intensity(current_month) = sum_{j <= current} delinquent_90dpd_j * w(d_j) / sum_{j <= current} w(d_j), where d_j is the number of whole months between the current month and month_j, and the weight function uses a 6-month half-life: w(d) = 0.5^(d / 6). Use only the current and earlier months for each loan (no future leakage). Return one row per loan-month with: loan_id, month, and the exponentially weighted default intensity, rounded to 4 decimal places.

Tables

loan_monthly_status(loan_id INT, msa VARCHAR(10), property_type VARCHAR(20), month DATE, delinquent_90dpd INT, upb DECIMAL(12,2))

Hints

  1. Self-join the table on (loan_id) with a condition prev.month <= cur.month to gather all past observations for each loan-month.
  2. Compute a months_diff value from the date difference and use POWER(0.5, months_diff / 6.0) as the exponential weight, then aggregate weighted sums per (loan_id, month).

Loading coding console...