Coding questions · EY
Map sources to functional dataset with SQL
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‑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.
Build Daily Exposure Fact with Deduplication and Limit Utilization
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).
Table Customers
| column | type | constraints |
|---|---|---|
| cust_id | INT | PRIMARY KEY, NOT NULL |
| cust_name | VARCHAR(100) | NOT NULL |
| kyc_status | VARCHAR(20) | NOT NULL |
| country | VARCHAR(2) | NOT NULL |
Sample data:
| country | cust_id | cust_name | kyc_status |
|---|---|---|---|
| US | 101 | Alpha LLC | PASS |
| FR | 102 | Beta SA | PASS |
| DE | 103 | Gamma AG | REVIEW |
Table Accounts
| column | type | constraints |
|---|---|---|
| acct_id | INT | PRIMARY KEY, NOT NULL |
| cust_id | INT | NOT NULL |
| product | VARCHAR(20) | NOT NULL |
| opened_at | TIMESTAMP | NOT NULL |
Sample data:
| acct_id | cust_id | opened_at | product |
|---|---|---|---|
| 5001 | 101 | 2023-05-10 09:00:00 | MARGIN |
| 5002 | 102 | 2024-11-01 10:00:00 | CASH |
| 5003 | 103 | 2025-01-15 14:30:00 | MARGIN |
Table Trades
| column | type | constraints |
|---|---|---|
| trade_id | VARCHAR(20) | NOT NULL |
| acct_id | INT | NOT NULL |
| trade_dt | DATE | NOT NULL |
| asset_class | VARCHAR(10) | NOT NULL |
| notional_usd | DECIMAL(18,2) | NOT NULL |
| side | VARCHAR(4) | NOT NULL |
| status | VARCHAR(20) | NOT NULL |
| ingested_at | TIMESTAMP | NOT NULL |
Sample data:
| acct_id | asset_class | ingested_at | notional_usd | side | status | trade_dt | trade_id |
|---|---|---|---|---|---|---|---|
| 5001 | EQ | 2025-05-26 12:00:00 | 1000000 | BUY | BOOKED | 2025-05-26 | T1 |
| 5001 | EQ | 2025-05-27 08:00:00 | 1000000 | BUY | CANCELED | 2025-05-26 | T1 |
| 5001 | FI | 2025-05-28 11:30:00 | 2500000 | SELL | BOOKED | 2025-05-28 | T2 |
| 5002 | EQ | 2025-06-01 02:00:00 | 750000 | BUY | BOOKED | 2025-05-30 | T3 |
| 5003 | EQ | 2025-05-31 09:00:00 | 600000 | BUY | BOOKED | 2025-05-31 | T4 |
| 5001 | EQ | 2025-05-25 10:00:00 | 500000 | BUY | BOOKED | 2025-05-25 | T5 |
| 5002 | EQ | 2025-06-01 15:00:00 | 300000 | SELL | BOOKED | 2025-06-01 | T6 |
Table RiskLimits
| column | type | constraints |
|---|---|---|
| acct_id | INT | PRIMARY KEY, NOT NULL |
| daily_limit_usd | DECIMAL(18,2) | NOT NULL |
Sample data:
| acct_id | daily_limit_usd |
|---|---|
| 5001 | 2000000 |
| 5002 | 1000000 |
| 5003 | 1500000 |
Expected output
DailyExposure fact table at grain (acct_id, trade_dt) for trade_dt between 2025-05-26 and 2025-06-01. Trades are first deduplicated to the latest ingested row per trade_id, then filtered to non-CANCELED trades on accounts whose customers have KYC status PASS, joined to RiskLimits, and aggregated. Metrics: gross_notional (sum of absolute 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).
| acct_id | breach_flag | daily_limit_usd | gross_notional | limit_utilization | net_notional | trade_dt |
|---|---|---|---|---|---|---|
| 5001 | 1 | 2000000 | 2500000 | 1.25 | -2500000 | 2025-05-28 |
| 5002 | 0 | 1000000 | 750000 | 0.75 | 750000 | 2025-05-30 |
| 5002 | 0 | 1000000 | 300000 | 0.3 | -300000 | 2025-06-01 |
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.