Clean messy data before you trust it

Marketing is uploading German customers to an email tool that rejects addresses with capital letters or stray spaces. For customers whose country is 'Germany', return id, name and the email converted to lowercase with leading and trailing spaces removed. Any row order is fine.

You're preparing a customer count by location for a report, and blank cells confuse readers. Return country, city and the number of customers for each country and city combination. Customers without a city must appear under the city 'Unknown' instead of NULL. Any row order is fine.

The same person sometimes creates a second account with a differently typed email. Treat two customers as duplicates when their emails match after converting to lowercase and removing leading and trailing spaces. Return the cleaned email, the number of customer records with it, and the lowest customer id (the record to keep), only for cleaned emails that appear more than once.

To plan email deliverability, count customers per email domain. The domain is everything after the '@' in the cleaned email (lowercase, no leading or trailing spaces). Return domain and number of customers. Any row order is fine.

HR wants a quick salary band summary. Use these rules: 'entry' for salary below 70000, 'mid' for 70000 up to 99999, and 'senior' for 100000 and above (labels in lowercase exactly as written). Return band, number of employees and average salary rounded to whole euros. Any row order is fine.

For accounts on the 'scale' plan, return company, customer lifetime in days, and lifetime in years rounded to 1 decimal. Lifetime runs from created_at to churned_at; for accounts that have not churned, use '2026-09-30' (the last day of the data) instead of churned_at. Years = days / 365.25. Sort by lifetime in days, longest first.