runtimetask Deduplication · medium
Keep one invitation per person
Keep one invitation per invitee email (lower-cased and trimmed): the earliest sent_at, ties broken by the lowest invitation_id. Return invitation_id, email and sent_at.
what to return
- Columns
invitation_idemailsent_atin this order- Rows
- 6412 rows · any order
- Skill
- Keep the first record per duplicate key. Refresher: Deduplicate event retries →
expected result
Your query should return exactly these columns and values. Run it and compare.
expected result6412 rows · any order
| invitation_id | sent_at | |
|---|---|---|
| 4363 | aaliyah.anderson@evergreensecurity2101.example | 2025-03-31 06:44:03 |
| 2265 | aaliyah.anderson@summitcapital1064.example | 2024-11-24 21:41:28 |
| 3725 | aaliyah.baker@bluesecurity1783.example | 2025-02-11 13:18:16 |
first 3 of 6412 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.