Business Context

You’re a data engineer at a large e-commerce marketplace (~20M registered customers, millions of daily sessions). Multiple upstream systems (web signup, mobile app, call center) write to a shared customer profile store. Due to intermittent API timeouts and partial updates, the customer_profiles table often contains missing values (NULL or empty strings) for key fields like email and phone.

Marketing and fraud teams rely on these fields for order confirmations, account recovery, and risk checks. If missing data isn’t cleaned consistently, the business sees higher support costs, lower deliverability, and weaker fraud detection.

Task

Write a SQL query that produces a cleaned view of customer contact data by imputing missing values using the most recent non-missing value available for the same customer.

Requirements

1. Return one row per customer_id.
2. For each customer, output:
   • customer_id
   • clean_email
   • clean_phone
   • last_updated_at (the latest profile update timestamp for that customer)
3. Treat both NULL and empty strings ('') as missing.
4. Impute missing values by taking the most recent non-missing value for that customer based on updated_at.
5. If a customer has never had a non-missing value for a field, return NULL for that cleaned field.
6. Order results by customer_id ascending.

Table Definitions

customer_profiles

Sample Data

customer_profiles

Expected Output