Personalised customer emails 📨

Scenario

A B2B vendor is looking to email their customers about changes to their account.

This bank's customers are companies, and these companies have multiple contacts associated with them. While the companies have an email address in the system, it's not clear which contact the email address corresponds to.

The bank wants to email the company's email address, but to make it more personal, they want to use the first name of the corresponding contact in the email salutation; e.g. "Hey John".

Question

Given the customer details and relationships below, determine which first name to use in the email salutation for each company. If none of the contacts' names seem to be a good fit, use NULL instead.

The output should have one row for each company, with the columns:

• company_name as the name of the company
• company_email_address as the email address of the company
• salutation_name as the first name to use in the email salutation

Order the output by company_name.

Info

There is no "correct" answer to this question: just provide a reasonable solution for the database that you're using.

Expand for the DDL

create table customers (
    customer_id   int primary key,
    full_name     varchar not null,
    first_name    varchar,
    last_name     varchar,
    email_address varchar
);
insert into customers
values
    (1,  'Friends For Hire', null,       null,        'joe.trib@f4hire.com'),
    (2,  'Rachel Green',     'Rachel',   'Green',     'rachel.green@gmail.com'),
    (3,  'Monica Geller',    'Monica',   'Geller',    'gellerm@gmail.com'),
    (4,  'Ross Geller',      'Ross',     'Geller',    null),
    (5,  'Joey Tribbiani',   'Joey',     'Tribbiani', 'joe.tribbiani@gmail.com'),
    (6,  'Chandler Bing',    'Chandler', 'Bing',      'c.bing@gmail.com'),
    (7,  'Phoebe Buffay',    'Phoebe',   'Buffay',    null),
    (8,  'Fractal Factory',  null,       null,        'billiam@fractal-factory.co.uk'),
    (9,  'William Bloggs',   'William',  'Bloggs',    null),
    (10, 'Joe Bloggs',       'Joe',      'Bloggs',    null),
    (11, 'Some Company',     null,       null,        'admin@somecompany.com'),
    (12, 'Zoe Goode',        'Penny',    'Lane',      'pink.lotus@gmail.com'),
    (13, 'Leeroy Smythe',    'Leeroy',   'Smythe',    'lee.the.boss@gmail.com')
;

create table customer_relationships (
    parent_customer_id int references customers(customer_id),
    child_customer_id  int references customers(customer_id),
    relationship_type  varchar not null,
    primary key (parent_customer_id, child_customer_id)
);
insert into customer_relationships
values
    (1,  2,  'Director'),
    (1,  3,  'Shareholder'),
    (1,  4,  'Shareholder'),
    (1,  5,  'Director'),
    (1,  6,  'Director'),
    (1,  7,  'Director'),
    (8,  9,  'Director'),
    (8,  10, 'Director'),
    (11, 12, 'Director'),
    (11, 13, 'Director')
;

The solution can be found at:

• personalised-customer-emails.md

---

Sample input

Customers

customer_id	full_name	first_name	last_name	email_address
1	Straw Hat Pirates	null	null	king.luffy@strawhats.com
2	Monkey D Luffy	Luffy	Monkey	null
3	Roronoa Zoro	Zoro	Roronoa	null

Customer Relationships

parent_customer_id	child_customer_id	relationship_type
1	2	Captain
1	3	Swordsman

with

customers(customer_id, full_name, first_name, last_name, email_address) as (
    values
        (1,  'Straw Hat Pirates', null,    null,      'king.luffy@strawhats.com'),
        (2,  'Monkey D Luffy',    'Luffy', 'Monkey',  null),
        (3,  'Roronoa Zoro',      'Zoro',  'Roronoa', null)
),

customer_relationships(parent_customer_id, child_customer_id, relationship) as (
    values
        (1, 2, 'Captain'),
        (1, 3, 'Swordsman')
)

Sample output

company_name	company_email_address	salutation_name
Straw Hat Pirates	king.luffy@strawhats.com	Luffy

solution(company_name, company_email_address, salutation_name) as (
    values
        ('Straw Hat Pirates', 'king.luffy@strawhats.com', 'Luffy')
)

Hint 1

Use a string similarity function like DIFFERENCE (SQL Server) or JACCARD (DuckDB) to compare the contact names to the company email address.

Hint 2

Rank the contacts by similarity to the company email address and use the most similar one (above some threshold) as the salutation name.