Validate Staged Customer Events in SQL

Describe your experience with ETL, data cleaning, and SQL.

Write a SQL query that separates valid staged customer events from invalid ones, based on the rules below. Use the provided tables to identify which events can be loaded into the target model and which should be rejected.

Output

1. One row per staged event
2. Columns: event_id, customer_email, event_date, status, rejection_reason
3. Include all staged rows
4. Sort by event_id ascending

Output details

• Normalize customer_email by trimming whitespace and converting it to lowercase, preserving NULL as NULL.
• Mark an event invalid with rejection_reason unknown customer when no customer matches the normalized email.
• For a known customer, mark NULL or blank customer_email as invalid with reason missing email.
• Mark NULL event_date as invalid with reason missing event date.
• Mark NULL or nonpositive amount as invalid with reason invalid amount.
• Use NULL for rejection_reason when status is valid, and prioritize unknown customer before other rejection reasons.