Map sources to functional dataset with SQL
Company: EY
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
You must produce a functional, consumption‑ready dataset for daily exposure monitoring. Assume “today” = 2025‑09‑01. Use the last 7 calendar days by trade_dt (inclusive), handle late‑arriving data, and deduplicate to the latest ingested record per trade_id.
Sample source tables (ASCII):
Customers
+---------+-----------+------------+---------+
| cust_id | cust_name | kyc_status | country |
+---------+-----------+------------+---------+
| 101 | Alpha LLC | PASS | US |
| 102 | Beta SA | PASS | FR |
| 103 | Gamma AG | REVIEW | DE |
+---------+-----------+------------+---------+
Accounts
+---------+---------+---------+---------------------+
| acct_id | cust_id | product | opened_at |
+---------+---------+---------+---------------------+
| 5001 | 101 | MARGIN | 2023-05-10 09:00:00 |
| 5002 | 102 | CASH | 2024-11-01 10:00:00 |
+---------+---------+---------+---------------------+
Trades
+----------+---------+------------+-------------+---------------+------+----------+---------------------+
| trade_id | acct_id | trade_dt | asset_class | notional_usd | side | status | ingested_at |
+----------+---------+------------+-------------+---------------+------+----------+---------------------+
| T1 | 5001 | 2025-08-26 | EQ | 1,000,000 | BUY | BOOKED | 2025-08-26 12:00:00 |
| T1 | 5001 | 2025-08-26 | EQ | 1,000,000 | BUY | CANCELED | 2025-08-27 08:00:00 |
| T2 | 5001 | 2025-08-28 | FI | 2,500,000 | SELL | BOOKED | 2025-08-28 11:30:00 |
| T3 | 5002 | 2025-08-30 | EQ | 750,000 | BUY | BOOKED | 2025-09-01 02:00:00 |
+----------+---------+------------+-------------+---------------+------+----------+---------------------+
RiskLimits
+---------+----------------+
| acct_id | daily_limit_usd|
+---------+----------------+
| 5001 | 2,000,000 |
| 5002 | 1,000,000 |
+---------+----------------+
Task: Write SQL to build a DailyExposure fact at grain (acct_id, trade_dt) over [2025‑08‑25, 2025‑09‑01]. Requirements:
- Deduplicate to the latest ingested row per (trade_id) before aggregation.
- Compute gross_notional (sum abs(notional_usd)), net_notional (BUY positive, SELL negative), limit_utilization = gross_notional / daily_limit_usd, and breach_flag (limit_utilization > 1.0).
- Exclude trades with status = 'CANCELED'.
- Include only accounts with Customers.kyc_status = 'PASS'.
- Make the query idempotent for daily backfills (no double counting on re‑runs).
Provide the final SELECT and explain one edge case your SQL intentionally ignores.
Overview: This question evaluates competency in SQL-based data manipulation and data engineering concepts, including handling late-arriving data and deduplication to the latest ingested record, joining and filtering for enrichment and KYC status, and computing exposure metrics such as gross/net notional, limit utilization, and breach flags.
You must produce a functional, consumption-ready dataset for daily exposure monitoring. Assume "today" = 2025-06-01. Use the last 7 calendar days by trade_dt (inclusive), i.e. from 2025-05-26 to 2025-06-01.
Task: Write SQL to build a DailyExposure fact at grain (acct_id, trade_dt) over [2025-05-26, 2025-06-01]. Requirements:
- Deduplicate to the latest ingested row per trade_id before aggregation (use ingested_at to pick the most recent record).
- Compute gross_notional (sum of ABS(notional_usd)), net_notional (BUY positive, SELL negative), limit_utilization = gross_notional / daily_limit_usd, and breach_flag (1 if limit_utilization > 1.0, else 0).
- Exclude trades with status = 'CANCELED'.
- Include only accounts whose customer has Customers.kyc_status = 'PASS'.
- Make the query idempotent for daily backfills (re-running for the same date range must not double count trades).
- Base your logic only on the source tables below.
Provide the final SELECT statement and briefly describe one edge case your SQL intentionally ignores (for example, how you treat accounts that do not have a risk limit).
Tables
Customers(cust_id INT, cust_name VARCHAR(100), kyc_status VARCHAR(20), country VARCHAR(2))
Accounts(acct_id INT, cust_id INT, product VARCHAR(20), opened_at TIMESTAMP)
Trades(trade_id VARCHAR(20), acct_id INT, trade_dt DATE, asset_class VARCHAR(10), notional_usd DECIMAL(18,2), side VARCHAR(4), status VARCHAR(20), ingested_at TIMESTAMP)
RiskLimits(acct_id INT, daily_limit_usd DECIMAL(18,2))
Hints
- First deduplicate Trades using a window function over trade_id ordered by ingested_at DESC, then filter to rn = 1.
- After deduplication, join Trades to Accounts, Customers, and RiskLimits, filter out non-PASS KYC and CANCELED trades, then aggregate by acct_id and trade_dt.