Design SQL cleaning, mapping, dedupe, and keying

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).

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.

Table staging_customers

column	type	constraints
stg_customer_id	INT	PRIMARY KEY
full_name	TEXT
email	TEXT
created_at	TEXT
country	TEXT

Sample data:

country	created_at	email	full_name	stg_customer_id
US	2025-08-28 10:12:00	ana.li@example.com	Ana Li	1
usa	2025-08-28 10:12	ANA.LI@example.com	Ana Li	2
UK	2025/08/30	ben.oneal@example	-Ben Oneal-	3
United States	2025-08-31	cara.d@example.com	Cara-D	4
NULL	2025-08-29	ana.li@example.com	NULL	5

Expected output

One row per distinct email_norm (NULLs grouped together), keeping the most recent created_at_ts (ties broken by smallest stg_customer_id), with normalized name/email/timestamp/country, ordered by email_norm NULLS LAST then created_at_ts.

country_code	created_at_ts	email_norm	full_name_norm
NULL	2025-08-29T00:00:00	ana.li@example.com	NULL
US	2025-08-31T00:00:00	cara.d@example.com	Cara-D
UK	2025-08-30T00:00:00	NULL	Ben Oneal

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.
3. Use ROW_NUMBER() OVER (PARTITION BY email_norm ORDER BY created_at_ts DESC NULLS LAST, stg_customer_id ASC) and keep rn = 1; PARTITION BY groups all NULL emails together.

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).

Table staging_customers

column	type	constraints
stg_customer_id	INT	PRIMARY KEY
full_name	TEXT
email	TEXT
created_at	TEXT
country	TEXT

Sample data:

country	created_at	email	full_name	stg_customer_id
US	2025-08-28 10:12:00	ana.li@example.com	Ana Li	1
usa	2025-08-28 10:12	ANA.LI@example.com	Ana Li	2
UK	2025/08/30	ben.oneal@example	Ben ONeal	3
United States	2025-08-31	cara.d@example.com	Cara-D	4
NULL	2025-08-29	ana.li@example.com	NULL	5

Table customers

column	type	constraints
customer_id	INT	PRIMARY KEY, GENERATED BY DEFAULT AS IDENTITY
full_name	TEXT
email	TEXT
created_at	TIMESTAMP
country_code	CHAR(2)
updated_at	TIMESTAMP

Sample data:

country_code	created_at	customer_id	email	full_name	updated_at
US	2025-08-01 09:00:00	101	ana.li@example.com	Ana Li	NULL
KR	2025-08-15 12:00:00	102	dae.kim@example.com	Dae Kim	NULL

Expected output

A mapping specification table describing how each customers column is populated from the cleaned staging data, including example before/after values for Ana Li.

data_type	example_source_value_for_ana_li	example_target_value_for_ana_li	nullability_default	source_expression	target_column	transformation_rule
INT	NULL	NULL	NOT NULL	GENERATED BY DEFAULT AS IDENTITY	customer_id	Surrogate primary key; not loaded from staging
TEXT	Ana Li	NULL	NULL allowed	cleaned.full_name_norm	full_name	Trimmed, internal spaces collapsed, leading/trailing punctuation removed; may be NULL if staging full_name is NULL in the surviving row
TEXT	ana.li@example.com / ANA.LI@example.com	ana.li@example.com	NOT NULL	cleaned.email_norm	email	Lowercased, trimmed, validated email; invalid emails excluded from load
TIMESTAMP	2025-08-28 10:12:00 / 2025-08-28 10:12 / 2025-08-29	2025-08-29 00:00:00	NOT NULL	cleaned.created_at_ts	created_at	Parsed from flexible text formats; dedup keeps the most recent timestamp per email
CHAR(2)	US / usa / (NULL on latest row)	US (if a non-NULL US mapping survives) or NULL (if only the latest NULL survives)	NULL allowed	cleaned.country_code	country_code	Mapped to ISO-3166-1 alpha-2 using {US, usa, United States -> US}; other 2-letter alphabetic codes uppercased; otherwise NULL
TIMESTAMP	NULL	NULL	NULL allowed	set only on UPDATE as NOW()	updated_at	NULL on initial insert; on later upsert updates, set to current 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.

Table staging_customers

column	type	constraints
stg_customer_id	INT	PRIMARY KEY
full_name	TEXT
email	TEXT
created_at	TEXT
country	TEXT

Sample data:

country	created_at	email	full_name	stg_customer_id
US	2025-08-28 10:12:00	ana.li@example.com	Ana Li	1
usa	2025-08-28 10:12	ANA.LI@example.com	Ana Li	2
UK	2025/08/30	ben.oneal@example	Ben ONeal	3
United States	2025-08-31	cara.d@example.com	Cara-D	4
NULL	2025-08-29	ana.li@example.com	NULL	5

Table customers

column	type	constraints
customer_id	INT	PRIMARY KEY, GENERATED BY DEFAULT AS IDENTITY
full_name	TEXT
email	TEXT
created_at	TIMESTAMP
country_code	CHAR(2)
updated_at	TIMESTAMP

Sample data:

country_code	created_at	customer_id	email	full_name	updated_at
US	2025-08-01 09:00:00	101	ana.li@example.com	Ana Li	NULL
KR	2025-08-15 12:00:00	102	dae.kim@example.com	Dae Kim	NULL

Expected output

No customers share an email key (so Part 1 yields no rows); exactly one staging-vs-customers collision exists, where surviving staging row 5 (ana.li@example.com) matches customer 101.

cnt	customer_id	customer_ids	email_key	row_type	stg_customer_id
NULL	101	NULL	ana.li@example.com	staging_vs_customers	5

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 ...).
3. UNION ALL the two parts, padding the columns each part does not produce with explicitly-typed NULLs (e.g. NULL::int, NULL::int[]) so the column types line up.

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.

Table customers

column	type	constraints
customer_id	INT	PRIMARY KEY, GENERATED BY DEFAULT AS IDENTITY
full_name	TEXT
email	TEXT
created_at	TIMESTAMP
country_code	CHAR(2)
updated_at	TIMESTAMP

Sample data:

country_code	created_at	customer_id	email	full_name	updated_at
US	2025-08-01 09:00:00	101	ana.li@example.com	Ana Li	NULL
KR	2025-08-15 12:00:00	102	dae.kim@example.com	Dae Kim	NULL

Table orders

column	type	constraints
order_id	INT	PRIMARY KEY
customer_id	INT
order_date	DATE
amount	NUMERIC(10,2)

Sample data:

amount	customer_id	order_date	order_id
120	101	2025-08-20	1001
50	101	2025-08-21	1002
200	102	2025-08-22	1003

Expected output

These are DDL statements that alter table structure and add indexes; they do not return row data. The script adds a uniqueness constraint on a normalized email expression, a foreign key with a RESTRICT delete rule, and an index to support joins and date-based queries on orders.

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).

Table orders

column	type	constraints
order_id	INT	PRIMARY KEY
customer_id	INT
order_date	DATE
amount	NUMERIC(10,2)

Sample data:

amount	customer_id	order_date	order_id
120	101	2025-08-20	1001
50	101	2025-08-21	1002
200	102	2025-08-22	1003

Expected output

Each row represents a pair of consecutive-day orders by the same customer. With the sample data, customer 101 has orders on 2025-08-20 and 2025-08-21, forming one consecutive pair.

customer_id	first_date	first_order_id	next_date	next_order_id
101	2025-08-20	1001	2025-08-21	1002

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).

Table staging_customers

column	type	constraints
stg_customer_id	INT	PRIMARY KEY
full_name	TEXT
email	TEXT
created_at	TEXT
country	TEXT

Sample data:

country	created_at	email	full_name	stg_customer_id
US	2025-08-28 10:12:00	ana.li@example.com	Ana Li	1
usa	2025-08-28 10:12	ANA.LI@example.com	Ana Li	2
UK	2025/08/30	ben.oneal@example	Ben O'Neal	3
United States	2025-08-31	cara.d@example.com	Cara-D	4
NULL	2025-08-29	ana.li@example.com	NULL	5

Table customers

column	type	constraints
customer_id	INT	PRIMARY KEY, GENERATED BY DEFAULT AS IDENTITY
full_name	TEXT
email	TEXT
created_at	TIMESTAMP
country_code	CHAR(2)
updated_at	TIMESTAMP

Sample data:

country_code	created_at	customer_id	email	full_name	updated_at
US	2025-08-01 09:00:00	101	ana.li@example.com	Ana Li	NULL
KR	2025-08-15 12:00:00	102	dae.kim@example.com	Dae Kim	NULL

Expected output

This is an upsert (INSERT ... ON CONFLICT) statement that does not return row data. Conceptually, with the sample data, it would update customer 101 (Ana Li) setting updated_at to NOW() while preserving her existing name and country_code, insert a new customer for 'cara.d@example.com' with a new customer_id, and ignore the invalid email 'ben.oneal@example'.

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.