Write SQL and Python, run instantly in your browser, and track your progress.
You are a Marketing Analyst at Spotify. You are preparing a dataset for a global user engagement study to understand listening habits across different regions. To ensure your analysis is accurate, you must first clean and standardize the user locale fields.
Your task is to write a SQL query that transforms the users table to produce a clean list for your study. The query should include the user's ID and the following standardized fields:
email: converted to all lowercase.country_name: a new column that maps the country code (e.g., 'GB', 'BR') to its full name ('United Kingdom', 'Brazil').language: converted to all lowercase.The final list should be sorted alphabetically by the new country_name.
You are a Marketing Analyst at Spotify. You are preparing a dataset for a global user engagement study to understand listening habits across different regions. To ensure your analysis is accurate, you must first clean and standardize the user locale fields.
Your task is to write a SQL query that transforms the users table to produce a clean list for your study. The query should include the user's ID and the following standardized fields:
email: converted to all lowercase.country_name: a new column that maps the country code (e.g., 'GB', 'BR') to its full name ('United Kingdom', 'Brazil').language: converted to all lowercase.The final list should be sorted alphabetically by the new country_name.
| Column Name | Type |
|---|---|
| user_id | INTEGER |
| TEXT | |
| full_name | TEXT |
| country | TEXT |
| language | TEXT |
| created_at | TEXT |
| account_status | TEXT |
| Column Name | Type |
|---|---|
| user_id | INTEGER |
| TEXT | |
| full_name | TEXT |
| country | TEXT |
| language | TEXT |
| created_at | TEXT |
| account_status | TEXT |
| user_id | full_name | country | language | created_at | account_status | |
|---|---|---|---|---|---|---|
| 1 | andrewbrown@icloud.com | Andrew Brown | CA | en | 2025-06-14 | active |
| 2 |
| user_id | full_name | country | language | created_at | account_status | |
|---|---|---|---|---|---|---|
| 1 | andrewbrown@icloud.com | Andrew Brown | CA | en | 2025-06-14 | active |
| 2 |
| user_id | email_lower | country_name | language_lower |
|---|---|---|---|
| 8 | juan_hernandez@hotmail.com | Australia | en |
| 9 | saralee@proton.me | Australia | en |
| 17 | michael88@yahoo.com | Australia | en |
| 42 | betty28@live.com | Australia |
| user_id | email_lower | country_name | language_lower |
|---|---|---|---|
| 8 | juan_hernandez@hotmail.com | Australia | en |
| 9 | saralee@proton.me | Australia | en |
| 17 | michael88@yahoo.com | Australia | en |
| 42 | betty28@live.com | Australia |
Showing first 5 of 60 rows. Users with normalized locale information sorted by country name.
Showing first 5 of 60 rows. Users with normalized locale information sorted by country name.
Your final output should be a result set that meets the following requirements:
1. Transformations:
email column must be converted to all lowercase and aliased as email_lower.language column must be converted to all lowercase and aliased as language_lower.country_name column must be created based on the country code. You must implement the following mapping using a CASE statement:
Your final output should be a result set that meets the following requirements:
1. Transformations:
email column must be converted to all lowercase and aliased as email_lower.language column must be converted to all lowercase and aliased as language_lower.country_name column must be created based on the country code. You must implement the following mapping using a CASE statement:
| anthony.dubois@mail.com |
| anthony.dubois@mail.com |
| Anthony Dubois |
| Anthony Dubois |
| US |
| US |
| en |
| en |
| 2024-11-24 |
| 2024-11-24 |
| active |
| active |
| 3 | david92@hotmail.com | David Wagner | ES | es | 2024-11-25 | active |
| 3 | david92@hotmail.com | David Wagner | ES | es | 2024-11-25 | active |
| 4 | valentina_smith@outlook.com | Valentina Smith | DE | de | 2024-12-17 | active |
| 4 | valentina_smith@outlook.com | Valentina Smith | DE | de | 2024-12-17 | active |
| 5 | omar.wilson@yahoo.com | Omar Wilson | FR | fr | 2025-07-27 | active |
| 5 | omar.wilson@yahoo.com | Omar Wilson | FR | fr | 2025-07-27 | active |
| en |
| en |
| 55 | amit.nakamura@proton.me | Australia | en |
| 55 | amit.nakamura@proton.me | Australia | en |
2. Columns:
2. Columns:
user_idemail_lowercountry_namelanguage_loweruser_idemail_lowercountry_namelanguage_lower3. Ordering:
3. Ordering:
country_name in ascending alphabetical order.user_id in ascending order.country_name in ascending alphabetical order.user_id in ascending order.