Assess SQL cleaning, mapping, joins, keys, and DDL/DML

You inherit a small retail analytics warehouse. Use the schema and sample rows below to answer all parts.

Schema and sample data (minimal rows shown):

Customers
+-------------+----------------------+---------------------+-------------+
| customer_id | email | created_at | referrer_id |
+-------------+----------------------+---------------------+-------------+
| 101 | a@x.com | 2025-07-11 09:15:00 | null |
| 102 | b@y.com | 2025-07-12 10:20:00 | 101 |
| 103 | a@x.com | 2025-07-12 10:21:00 | null |
+-------------+----------------------+---------------------+-------------+

Orders
+----------+-------------+---------------------+--------+--------------+
| order_id | customer_id | order_date | status | total_amount |
+----------+-------------+---------------------+--------+--------------+
| 5001 | 101 | 2025-08-01 12:00:00 | paid | 49.99 |
| 5002 | 101 | 2025-08-01 12:00:10 | paid | 49.99 |
| 5003 | 103 | 2025-08-02 09:00:00 | cancel | 15.00 |
+----------+-------------+---------------------+--------+--------------+

OrderItems
+----------+---------+------------+-----+------------+
| order_id | line_no | product_id | qty | unit_price |
+----------+---------+------------+-----+------------+
| 5001 | 1 | 9001 | 1 | 49.99 |
| 5002 | 1 | 9001 | 1 | 49.99 |
+----------+---------+------------+-----+------------+

Products
+------------+-------+----------------+--------+
| product_id | sku | name | price |
+------------+-------+----------------+--------+
| 9001 | SKU-1 | Widget Basic | 49.99 |
| 9002 | SKU-1 | Widget Basic | 49.99 |
+------------+-------+----------------+--------+

Answer all parts:

1. Data cleaning: Outline a concrete, ordered plan to clean this warehouse. Include: (a) deduplicating Customers by email while preserving the most trustworthy record (define and justify tie-breakers), (b) deduplicating Orders that appear to be ingestion duplicates (define a natural key and a time/amount tolerance you would use), (c) standardizing data types/time zones, validating referential integrity across all tables, and detecting impossible values (e.g., negative qty). For at least two steps, provide representative SQL snippets (or Python+pandas) you would run.
2. Data mapping: You must onboard a CSV web_orders_2025-08.csv with columns (order_id, order_ts, customer_email, sku, qty, price_cents). List exactly what fields you would include in a formal data mapping specification to land this file into Orders and OrderItems (e.g., source column, target table.column, data type, transformation logic, constraints, null/default rules, semantics/definitions, lineage, owner, quality tests, sample values, refresh cadence). Then, map each CSV column to its target columns and required transforms for this schema.
3. Find duplicates: (a) Write a query that flags duplicate customers by email and returns only the row you would keep per email with a reason code for why others were dropped. (b) Write a query that returns likely duplicate orders for the same customer when order_date is within 30 seconds and total_amount matches to 2 decimal places, grouped into clusters with a stable surrogate duplicate_group_id. (c) Write a query to detect duplicate Products by sku and pick the survivor deterministically.
4. DDL vs DML: Explain the practical differences between DDL and DML in this scenario, including transactional and locking implications. Provide one DDL statement you would actually run here to enforce integrity and one DML statement that demonstrates data manipulation on these tables.
5. Self join: Using Customers, return each customer_id, email, and the referrer’s email (if any). Ensure customers with no referrer still appear exactly once.
6. Conditional insert (IF NOT EXISTS…INSERT/UPSERT): Show how you would insert or update a Product by sku so that duplicate SKUs like SKU-1 collapse into a single correct row without violating constraints. Include how you would handle concurrent writes.
7. Keys and constraints: Define appropriate PRIMARY KEY, FOREIGN KEY, and UNIQUE constraints for all four tables given the sample data. Justify any composite keys (e.g., OrderItems) and discuss trade-offs of surrogate vs natural keys for Customers and Orders.
8. Describe your SQL skill: Rate your SQL proficiency from 1–10 and justify with two concrete examples from the tasks above (e.g., a window-function-based dedupe and a concurrency-safe upsert), including how you would test and optimize them.

Deduplicate Customers by Email Using Window Functions

Using the Customers table below, return one canonical row per email address. Treat multiple rows with the same email as potential duplicates. Keep the row with the earliest created_at; if there is a tie, keep the row with the lowest customer_id. For each surviving row, return customer_id, email, created_at, referrer_id, and a reason_code indicating why this row was kept (e.g., 'earliest_created_at_for_email' or 'only_record_for_email').

Table Customers

column	type	constraints
customer_id	INT	PRIMARY KEY, NOT NULL
email	VARCHAR(255)	NOT NULL
created_at	TIMESTAMP	NOT NULL
referrer_id	INT

Sample data:

created_at	customer_id	email	referrer_id
2025-07-11 09:15:00	101	a@x.com	NULL
2025-07-12 10:20:00	102	b@y.com	101
2025-07-12 10:21:00	103	a@x.com	NULL

Expected output

One row per distinct email, keeping the earliest created_at (tie-broken by lowest customer_id) and including a reason_code describing why this row was kept.

created_at	customer_id	email	reason_code	referrer_id
2025-07-11 09:15:00	101	a@x.com	earliest_created_at_for_email	NULL
2025-07-12 10:20:00	102	b@y.com	only_record_for_email	101

Hints

1. Use a window function like ROW_NUMBER() partitioned by email to choose the survivor per email.
2. You can compute both the rank within each email and the total count per email using window functions, then filter to rn = 1.

Cluster likely duplicate orders within 30 seconds

Using the Orders table, write a PostgreSQL query that returns clusters of likely duplicate orders.

Two or more orders belong to the same duplicate cluster when they have:

• the same customer_id
• the same total_amount when rounded to 2 decimal places
• order_date timestamps within 30 seconds of the cluster anchor

Return only clusters that contain more than one order. Output columns are duplicate_group_id, customer_id, order_id, order_date, and total_amount. Format order_date as YYYY-MM-DD HH24:MI:SS, keep total_amount rounded to 2 decimal places, and order the result by duplicate_group_id, then order_id.

Table Orders

column	type	constraints
order_id	INT	PRIMARY KEY, NOT NULL
customer_id	INT	NOT NULL
order_date	TIMESTAMP	NOT NULL
status	VARCHAR(20)	NOT NULL
total_amount	DECIMAL(10,2)	NOT NULL

Sample data:

customer_id	order_date	order_id	status	total_amount
101	2025-08-01 12:00:00	5001	paid	49.99
101	2025-08-01 12:00:10	5002	paid	49.99
103	2025-08-02 09:00:00	5003	cancel	15

Expected output

Each row represents an order that belongs to a duplicate cluster (size > 1), with a stable duplicate_group_id shared by all orders in the same cluster.

customer_id	duplicate_group_id	order_date	order_id	total_amount
101	1	2025-08-01 12:00:00	5001	49.99
101	1	2025-08-01 12:00:10	5002	49.99

Hints

1. Join the table to itself to find nearby orders for the same customer and rounded amount.
2. Use the earliest nearby order_date as a stable anchor, then count orders sharing that anchor.
3. Use PostgreSQL TO_CHAR to format order_date; strftime is not valid PostgreSQL.

Detect Duplicate Products by SKU and Mark Survivors

Using the Products table below, detect duplicate products by sku. For any sku that appears more than once, deterministically pick the survivor as the row with the lowest product_id. Return all rows where sku appears more than once, with columns: product_id, sku, name, price, and record_status, where record_status is 'keep' for the survivor and 'drop_duplicate' for non-survivors.

Table Products

column	type	constraints
product_id	INT	PRIMARY KEY, NOT NULL
sku	VARCHAR(50)	NOT NULL
name	VARCHAR(255)	NOT NULL
price	DECIMAL(10,2)	NOT NULL

Sample data:

name	price	product_id	sku
Widget Basic	49.99	9001	SKU-1
Widget Basic	49.99	9002	SKU-1

Expected output

All product rows for SKUs that are duplicated, with a deterministic mark of which row to keep ('keep') and which to drop ('drop_duplicate').

name	price	product_id	record_status	sku
Widget Basic	49.99	9001	keep	SKU-1
Widget Basic	49.99	9002	drop_duplicate	SKU-1

Hints

1. Use ROW_NUMBER() partitioned by sku and ordered by product_id to decide which row to keep per SKU.
2. Also compute COUNT(*) OVER (PARTITION BY sku) and filter to sku_count > 1 so you only return duplicated SKUs.

Self-Join Customers to Get Referrer Email

Using the Customers table below, return each customer with their own email and the referring customer's email, if present. Output columns: customer_id, email, referrer_email. Customers without a referrer should still appear exactly once, with referrer_email as NULL.

Table Customers

column	type	constraints
customer_id	INT	PRIMARY KEY, NOT NULL
email	VARCHAR(255)	NOT NULL
created_at	TIMESTAMP	NOT NULL
referrer_id	INT

Sample data:

created_at	customer_id	email	referrer_id
2025-07-11 09:15:00	101	a@x.com	NULL
2025-07-12 10:20:00	102	b@y.com	101
2025-07-12 10:21:00	103	a@x.com	NULL

Expected output

One row per customer, with the corresponding referrer's email via a self-join, or NULL if there is no referrer.

customer_id	email	referrer_email
101	a@x.com	NULL
102	b@y.com	a@x.com
103	a@x.com	NULL

Hints

1. Use a LEFT JOIN from Customers to itself to ensure customers without referrers are still returned.
2. Join on referrer_id = customer_id in the self-join and alias the tables clearly (e.g., c for customer, r for referrer).