Data Cleaning Pipeline and SQL Analytics Screen

Data Engineer first-round screen combining a noisy CSV cleaning pipeline with three SQL tasks: monthly-reset cumulative sales, recursive employee hierarchy, and conditional order aggregation. Requirements Cleaning pipeline input: customer transaction CSV with columns date, amount, customer_id, payment_method, and notes. Data issues: Dates appear in mixed formats: YYYY-MM-DD, MM/DD/YYYY, and strings such as Jan 5, 2024. Amounts include currency symbols and commas, such as $1,234.56. customer_id can lose leading zeros after Excel processing. payment_method contains typos and inconsistent casing. After cleaning, describe what validations should run. SQL task 1: sales table with date, store_id, amount; compute cumulative sales by store, resetting to 0 whenever a new month starts. Output store_id, date, total_amount. SQL task 2: organization table with employee_id, manager_id, name; find all direct and indirect reports under manager_id = 1, returning employee_id, manager_id, name, level, and hierarchy_path. SQL task 3: orders table with order_id, status, amount, date; in one pass, compute completed-only revenue, pending count, and cancelled count by date. Notes The cleaning portion rewards validation discipline: parse dates to a canonical type, normalize amount to decimal, preserve IDs as strings, map payment-method aliases, and quarantine unparseable rows. Add explicit validation after cleaning: required columns, type checks, non-null key fields, duplicate transaction identifiers when available, allowed payment methods, amount range checks, and row-count / rejection-rate reporting. The hierarchy query is a recursive CTE problem. The monthly cumulative sales query should partition by store_id and month, then order by date inside the window. The orders query is a conditional aggregation problem using SUM(CASE WHEN ...) or dialect-specific filtered aggregates. Preparation Write a Pandas cleaning function that returns both cleaned rows and a reject table with reasons; include tests for mixed dates, currency strings, and leading-zero customer_id values. Memorize three SQL templates: monthly windowed cumulative sum, recursive CTE with level and hierarchy_path, and conditional aggregation in one grouped query. Prepare validation commentary that separates schema checks, business-rule checks, and reconciliation checks against source row counts.