Quick Overview

This question evaluates proficiency in data cleaning and normalization, deduplication, timestamp parsing, simple email validation, country code standardization, source-to-target mapping, and DDL/DML design using SQL (and optionally Python).

Design SQL cleaning, mapping, dedupe, and keying

Company: Freddie Mac

Role: Data Scientist

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Technical Screen

Assume PostgreSQL 15. You may choose a different SQL dialect if you clearly state it and adapt syntax accordingly. Schema and small sample data: - Table: staging_customers(stg_customer_id INT, full_name TEXT, email TEXT, created_at TEXT, country TEXT) Rows: 1 | Ana Li | ana.li@example.com | 2025-08-28 10:12:00 | US 2 | Ana Li | ANA.LI@example.com | 2025-08-28 10:12 | usa 3 | Ben O'Neal | ben.oneal@example | 2025/08/30 | UK 4 | Cara-D | cara.d@example.com | 2025-08-31 | United States 5 | null | ana.li@example.com | 2025-08-29 | null - Table: customers(customer_id INT PK, full_name TEXT, email TEXT, created_at TIMESTAMP, country_code CHAR(2), updated_at TIMESTAMP NULL) Rows: 101 | Ana Li | ana.li@example.com | 2025-08-01 09:00:00 | US | null 102 | Dae Kim| dae.kim@example.com | 2025-08-15 12:00:00 | KR | null - Table: orders(order_id INT PK, customer_id INT FK -> customers(customer_id), order_date DATE, amount NUMERIC(10,2)) Rows: 1001 | 101 | 2025-08-20 | 120.00 1002 | 101 | 2025-08-21 | 50.00 1003 | 102 | 2025-08-22 | 200.00 Answer the following. Be precise, handle edge cases, and justify design choices briefly. a) Data cleaning: Write a single SQL statement (using CTEs allowed) that produces a cleaned result set from staging_customers with columns: email_norm, full_name_norm, created_at_ts, country_code. Rules: trim all whitespace, collapse internal double spaces in names, lowercase emails, remove trailing/leading punctuation, validate email by simple rule (must contain one '@' and at least one '.' after '@'; reject otherwise), standardize country to ISO-3166-1 alpha-2 with mappings {US, usa, United States -> US}, leave others as their two-letter code if already valid else NULL; parse created_at flexibly to TIMESTAMP, set to NULL if unparsable. Deduplicate by email_norm keeping the row with the most recent created_at_ts; if tie, keep the smallest stg_customer_id. b) Data mapping spec: Provide a concise source-to-target mapping to load the cleaned result into customers, including for each target column: data type, source expression, transformation rule, and nullability/defaults (e.g., how updated_at is populated). Include at least one example row from the sample data showing the before/after values for Ana Li. c) Find duplicates: 1) In customers, return any duplicate person records by case-insensitive, trimmed email (treat lower(trim(email)) as the uniqueness key). Show email_key and count, plus a sample customer_id list. 2) In staging_customers (after cleaning logic from part a), find records that would collide with existing customers on email_key and show both staging and customers identifiers side-by-side. d) DDL vs DML: For each of the following, specify whether it’s DDL or DML, then write the exact SQL: - Enforce email uniqueness in customers on lower(trim(email)). - Add a foreign key from orders.customer_id to customers.customer_id with a deletion rule you choose (CASCADE or RESTRICT) and explain why. - Create any index(es) you deem necessary to support parts c) and f), and name them appropriately. e) Self-join: Using orders, list customer_id pairs of consecutive-day orders by the same customer (o2.order_date = o1.order_date + INTERVAL '1 day'). Output: customer_id, first_order_id, first_date, next_order_id, next_date. Avoid duplicate pairs and ensure performance on large tables. f) IF EXISTS…INSERT (upsert): Insert or update customers from the cleaned staging set. If email_key does not exist in customers, INSERT a new row (created_at from staging, country_code from cleaning). If it exists, UPDATE full_name if different (prefer the longest non-null normalized name), update country_code if NULL in customers and non-NULL in staging, and set updated_at = NOW(). Implement as a single statement (e.g., INSERT…ON CONFLICT for Postgres or MERGE for SQL Server). Explain how your approach avoids race conditions under concurrent loads (mention constraints and locking behavior).

Overview: This question evaluates proficiency in data cleaning and normalization, deduplication, timestamp parsing, simple email validation, country code standardization, source-to-target mapping, and DDL/DML design using SQL (and optionally Python).

Clean and deduplicate staging_customers

You are loading raw customer records from a staging table into a clean warehouse layer. In **PostgreSQL 15**, write a **single SQL statement** (CTEs and window functions allowed) that reads from `staging_customers` and produces a cleaned, deduplicated result set with exactly these columns: - `email_norm` - `full_name_norm` - `created_at_ts` - `country_code` **Cleaning rules (applied per row):** 1. **full_name_norm** — if `full_name` is NULL, keep it NULL. Otherwise: trim surrounding whitespace, collapse runs of internal whitespace into a single space, and strip any leading/trailing punctuation characters (use the `[[:punct:]]` POSIX class). 2. **email_norm** — lowercase and trim `email`, then strip leading/trailing punctuation. Then validate: the cleaned value is valid only if it contains exactly one `@`, has at least one character before the `@`, and contains at least one `.` somewhere after the `@`. If the cleaned value is **invalid**, set `email_norm` to NULL. 3. **created_at_ts** — parse the TEXT column `created_at` into a TIMESTAMP, trying these formats in order: `YYYY-MM-DD HH24:MI:SS`, `YYYY-MM-DD HH24:MI`, `YYYY-MM-DD`, `YYYY/MM/DD`. If none match, set `created_at_ts` to NULL. 4. **country_code** — standardize to an ISO-3166-1 alpha-2 code: any of `US`, `usa`, `United States` (case-insensitive) maps to `'US'`. Otherwise, if the value is already a valid two-letter alphabetic code, keep it uppercased; any other value (and NULL) becomes NULL. **Deduplication rules:** - Deduplicate by `email_norm`, treating all NULL `email_norm` values as a single group. - Within each group, keep the row with the **most recent** `created_at_ts` (NULL timestamps sort last). - Break ties on `created_at_ts` by keeping the row with the **smallest** `stg_customer_id`. **Output:** return only the surviving (deduplicated) rows, ordered by `email_norm` with NULLs last, then by `created_at_ts`.

Tables

staging_customers(stg_customer_id INT, full_name TEXT, email TEXT, created_at TEXT, country TEXT)

Hints

  1. Anchor a separate regex per accepted date format and test with the `~` operator before calling `to_timestamp`; return NULL when none match.
  2. Validate the email with a single pattern like `^[^@]+@[^@]+\.[^@]+$` to enforce one `@` and a dot after it.

Define source-to-target mapping from cleaned staging to customers

Using the cleaned output defined in Question 1 (columns: email_norm, full_name_norm, created_at_ts, country_code), specify a concise source-to-target mapping for loading into the customers table. For each target column in customers, provide: - Target data type - Source expression (from the cleaned result or other mechanism) - Transformation rule / business logic - Nullability or default behavior (e.g., how updated_at is populated) Include at least one example for the customer Ana Li, showing how her data flows from raw staging rows to the cleaned values that would be inserted/updated in customers. Express your mapping as a single SQL query that returns one row per target column with appropriate descriptive text (you may use string literals to describe rules and the Ana Li example).

Tables

staging_customers(stg_customer_id INT, full_name TEXT, email TEXT, created_at TEXT, country TEXT)

customers(customer_id INT, full_name TEXT, email TEXT, created_at TIMESTAMP, country_code CHAR(2), updated_at TIMESTAMP)

Hints

  1. You can represent the mapping itself as rows returned from a SELECT of string literals.
  2. For Ana Li, think through which staging row survives deduplication and what her cleaned values are.

Find duplicate emails and collisions between staging and customers

Using PostgreSQL, write a **single** SQL statement (CTEs allowed) that produces one unified result set combining two analyses over the `staging_customers` and `customers` tables. Treat emails case-insensitively and ignore surrounding whitespace by using `lower(trim(email))` as the email key throughout. **Part 1 — duplicate customers.** Within `customers`, find any groups of records that share the same email key (`lower(trim(email))`). For each *duplicated* key (i.e. appearing in two or more rows), produce a row describing it. **Part 2 — staging vs. customers collisions.** Reproduce the cleaning + deduplication of `staging_customers` from Question 1: normalize and validate each staging email, keep exactly one surviving row per cleaned email key (when several staging rows share the same key, keep the one with the most recent parseable `created_at`, breaking ties by the smallest `stg_customer_id`), then find every surviving staging row whose cleaned email key matches an existing customer's email key. Produce one row per such (staging, customer) match. **Output.** Return a single result set with exactly these six columns, in this order: - `row_type` — `'customers'` for Part 1 rows, `'staging_vs_customers'` for Part 2 rows - `email_key` — the normalized `lower(trim(email))` key - `cnt` — the duplicate count (Part 1 rows only; `NULL` for Part 2 rows) - `customer_ids` — an integer array of the colliding customer ids, ascending (Part 1 rows only; `NULL` for Part 2 rows) - `stg_customer_id` — the surviving staging id (Part 2 rows only; `NULL` for Part 1 rows) - `customer_id` — the matched customer id (Part 2 rows only; `NULL` for Part 1 rows) Order the output by `row_type`, then by `email_key`.

Tables

staging_customers(stg_customer_id INT, full_name TEXT, email TEXT, created_at TEXT, country TEXT)

customers(customer_id INT, full_name TEXT, email TEXT, created_at TIMESTAMP, country_code CHAR(2), updated_at TIMESTAMP)

Hints

  1. Reuse the cleaning + dedup logic from Question 1 as CTEs so you end up with exactly one surviving staging row per cleaned email key, then join that to customers.
  2. For Part 1 group customers by lower(trim(email)) and filter with HAVING COUNT(*) > 1; aggregate the ids with array_agg(... ORDER BY ...).

DDL vs DML: constraints and indexes for customers and orders

In PostgreSQL 15, classify each of the following as DDL or DML and write the exact SQL statements: 1) Enforce email uniqueness in customers on lower(trim(email)). 2) Add a foreign key from orders.customer_id to customers.customer_id with a deletion rule you choose (CASCADE or RESTRICT) and briefly explain the choice. 3) Create any index(es) you deem necessary to support duplicate detection and collision checks (Question 3) and the upsert logic (Question 6). Name the indexes appropriately. All statements may appear in a single SQL script separated by semicolons; include comments indicating DDL/DML and explaining the deletion rule choice.

Tables

customers(customer_id INT, full_name TEXT, email TEXT, created_at TIMESTAMP, country_code CHAR(2), updated_at TIMESTAMP)

orders(order_id INT, customer_id INT, order_date DATE, amount NUMERIC(10,2))

Hints

  1. In PostgreSQL, a UNIQUE constraint on an expression can be declared with an extra pair of parentheses around the expression.
  2. Use ON DELETE RESTRICT (or omit ON DELETE) if you want to forbid deleting customers that still have related orders.

Self-join orders to find consecutive-day purchases

In PostgreSQL 15, using the orders table, list pairs of consecutive-day orders placed by the same customer. Two orders form a pair when o2.order_date = o1.order_date + INTERVAL '1 day' and o1.customer_id = o2.customer_id. Return the following columns: - customer_id - first_order_id (o1.order_id) - first_date (o1.order_date) - next_order_id (o2.order_id) - next_date (o2.order_date) Avoid duplicate pairs and write the query so it scales to large tables (assume appropriate indexes exist).

Tables

orders(order_id INT, customer_id INT, order_date DATE, amount NUMERIC(10,2))

Hints

  1. Use a self-join on orders, joining on customer_id and order_date + INTERVAL '1 day'.
  2. An index on (customer_id, order_date) helps this self-join scale to large tables.

Upsert customers from cleaned staging with race-safety

In PostgreSQL 15, write a single statement (CTEs allowed) that upserts from the cleaned, deduplicated staging set (as in Question 1) into customers using email_key = lower(trim(email)) as the natural key. Rules: - Use the cleaned staging data with columns: email_norm, full_name_norm, created_at_ts, country_code, and one surviving row per email. - Consider only rows with a valid email_norm (non-NULL). - If email_key does not exist in customers, INSERT a new row: - email from email_norm - full_name from full_name_norm - created_at from created_at_ts - country_code from cleaned.country_code - updated_at should remain NULL on initial insert. - If email_key already exists in customers, UPDATE that row: - Update full_name if different: prefer the longest non-NULL normalized name between the existing value and the incoming value. - Update country_code only if it is NULL in customers and non-NULL in staging. - Set updated_at = NOW(). Implement this as a single INSERT ... ON CONFLICT statement. Assume there is a UNIQUE constraint on customers enforcing uniqueness of lower(trim(email)); use it as the ON CONFLICT target. Briefly explain in comments how this approach avoids race conditions under concurrent loads (mention constraints and locking behavior).

Tables

staging_customers(stg_customer_id INT, full_name TEXT, email TEXT, created_at TEXT, country TEXT)

customers(customer_id INT, full_name TEXT, email TEXT, created_at TIMESTAMP, country_code CHAR(2), updated_at TIMESTAMP)

Hints

  1. Use INSERT ... ON CONFLICT ON CONSTRAINT customers_email_key_unique so you can rely on the expression-based UNIQUE constraint.
  2. In the DO UPDATE clause, use CASE and COALESCE to implement the longest-name and country_code-if-NULL rules.

Loading coding console...