Business Problem

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.

Schema: users

Business Problem

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.

Schema: users

Column Name	Type
user_id	INTEGER
email	TEXT
full_name	TEXT
country	TEXT
language	TEXT
created_at	TEXT
account_status	TEXT

Column Name	Type
user_id	INTEGER
email	TEXT
full_name	TEXT
country	TEXT
language	TEXT
created_at	TEXT
account_status	TEXT

user_id	email	full_name	country	language	created_at	account_status
1	andrewbrown@icloud.com	Andrew Brown	CA	en	2025-06-14	active
2

user_id	email	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

2504. Normalize Streaming User Locales

Relevant companies

Tags

2504. Normalize Streaming User Locales

Relevant companies

Tags

Sign in to Practice