00:00

2503. Standardize User Emails and Extract Domains

EasySQLITE102 / 1584

Relevant companies

2503. Standardize User Emails and Extract Domains

Easy102 / 1584

Relevant companies

Sign in to Practice

Write SQL and Python, run instantly in your browser, and track your progress.

Runs live in your browser — no setup
Save progress across all 1,500+ problems
Submit and validate your answer instantly

Business Problem

You are a security analyst at Okta, a leading identity platform. For an upcoming compliance audit, you need to provide a standardized list of all user accounts. The report requires that all email addresses be normalized to lowercase and that the email domain be extracted for analysis of organizational sign-ups. User roles also need to be standardized to uppercase for consistent reporting.

Your task is to write a SQL query that transforms the users table to produce this standardized list.

The final report should be sorted alphabetically by the extracted email domain to help group users by their organization.

Schema: users

Column Name	Type
user_id	INTEGER
org_id

Business Problem

Your task is to write a SQL query that transforms the users table to produce this standardized list.

The final report should be sorted alphabetically by the extracted email domain to help group users by their organization.

Schema: users

Column Name	Type
user_id	INTEGER
org_id

user_id	email_lower	email_domain	role_upper
78	miapark@azuredata.io	azuredata.io	OWNER
79	sandra@azuredata.io	azuredata.io	BILLING
93	hana.jones@coralindustries.tech	coralindustries.tech	OWNER
94	sebastian.park@coralindustries.tech	coralindustries.tech

user_id	email_lower	email_domain	role_upper
78	miapark@azuredata.io	azuredata.io	OWNER
79	sandra@azuredata.io	azuredata.io	BILLING
93	hana.jones@coralindustries.tech	coralindustries.tech	OWNER
94	sebastian.park@coralindustries.tech	coralindustries.tech

NULL

email_domain

''

Standardize User Emails and Extract Domains — SQL Easy | Let's Data Science | Let's Data Science

2503. Standardize User Emails and Extract Domains

Relevant companies

Tags

2503. Standardize User Emails and Extract Domains

Relevant companies

Tags

Sign in to Practice