runtimetask TRIM / LOWER / SUBSTR / REPLACE · medium
Normalize invitee emails
Return invitation_id and normalized_email for invitations whose normalized form differs from the raw invitee_email. Normalize by trimming, lower-casing, and removing any '+tag' before the @.
what to return
- Columns
invitation_idnormalized_emailin this order- Rows
- 716 rows · any order
- Skill
- Normalize identifiers before matching. Refresher: Clean strings & cast values →
expected result
Your query should return exactly these columns and values. Run it and compare.
expected result716 rows · any order
| invitation_id | normalized_email |
|---|---|
| 9 | rana.nguyen@prismhealth5.example |
| 18 | samuel.mitchell@birchhealth7.example |
| 27 | leo.ramirez@falconlearning9.example |
first 3 of 716 rows
data you can query
Product / SaaS Analytics dataset: the first 6 rows of each table. Under each column name: the kind of value it holds, and which table it links to. Your queries run on all rows.
invitations7 columns · 6,450 rows
| invitation_idnumber · unique id | account_idnumber · links to accounts | invited_by_user_idnumber · links to users | invitee_emailtext | sent_atdate & time | accepted_atdate & time | accepted_user_idnumber · links to users |
|---|---|---|---|---|---|---|
| 1 | 1 | 1 | deepa.bell@ironcapital1.example | 2024-01-21 11:24:15 | 2024-01-24 03:43:14 | 2 |
| 2 | 1 | 1 | sofia.wilson@mail.example | 2024-02-02 07:45:53 | 2024-02-02 21:34:25 | 3 |
| 3 | 2 | 6 | nadia.flores@vectordata2.example | 2024-01-25 09:18:58 | NULL | NULL |
| 4 | 2 | 6 | rosa.jones@vectordata2.example | 2024-02-03 06:27:24 | 2024-02-03 07:03:46 | 7 |
| 5 | 3 | 10 | raj.li@truefoods3.example | 2024-01-20 15:16:56 | NULL | NULL |
| 6 | 3 | 10 | ayesha.silva@truefoods3.example | 2024-01-24 19:32:04 | 2024-01-26 05:58:03 | 11 |
- invitation_idnumber · unique id
- 1
- account_idnumber · links to accounts
- 1
- invited_by_user_idnumber · links to users
- 1
- invitee_emailtext
- deepa.bell@ironcapital1.example
- sent_atdate & time
- 2024-01-21 11:24:15
- accepted_atdate & time
- 2024-01-24 03:43:14
- accepted_user_idnumber · links to users
- 2
- invitation_idnumber · unique id
- 2
- account_idnumber · links to accounts
- 1
- invited_by_user_idnumber · links to users
- 1
- invitee_emailtext
- sofia.wilson@mail.example
- sent_atdate & time
- 2024-02-02 07:45:53
- accepted_atdate & time
- 2024-02-02 21:34:25
- accepted_user_idnumber · links to users
- 3
- invitation_idnumber · unique id
- 3
- account_idnumber · links to accounts
- 2
- invited_by_user_idnumber · links to users
- 6
- invitee_emailtext
- nadia.flores@vectordata2.example
- sent_atdate & time
- 2024-01-25 09:18:58
- accepted_atdate & time
- NULL
- accepted_user_idnumber · links to users
- NULL
- invitation_idnumber · unique id
- 4
- account_idnumber · links to accounts
- 2
- invited_by_user_idnumber · links to users
- 6
- invitee_emailtext
- rosa.jones@vectordata2.example
- sent_atdate & time
- 2024-02-03 06:27:24
- accepted_atdate & time
- 2024-02-03 07:03:46
- accepted_user_idnumber · links to users
- 7
- invitation_idnumber · unique id
- 5
- account_idnumber · links to accounts
- 3
- invited_by_user_idnumber · links to users
- 10
- invitee_emailtext
- raj.li@truefoods3.example
- sent_atdate & time
- 2024-01-20 15:16:56
- accepted_atdate & time
- NULL
- accepted_user_idnumber · links to users
- NULL
- invitation_idnumber · unique id
- 6
- account_idnumber · links to accounts
- 3
- invited_by_user_idnumber · links to users
- 10
- invitee_emailtext
- ayesha.silva@truefoods3.example
- sent_atdate & time
- 2024-01-24 19:32:04
- accepted_atdate & time
- 2024-01-26 05:58:03
- accepted_user_idnumber · links to users
- 11
first 6 of 6,450 rows · your queries use all of them
solution.sql
Hints and the answer are always available. Using them counts the question as solved with help.