Help Scout Data Analyst Screening Question

screen.md

We're currently in the process of rolling out Beacon, our live chat tool, to existing customers who have expressed interest trying it.

Customers could have expressed interest in two ways: either by filling out an interest form or mentioning to our support team that they want to try it.

For the interest form, there is one table, hubspot.contact, with two relevant fields:

• email - The user's email address
• property_beacon_interest - A Unix timestamp in milliseconds representing when they filled out the form or null if they have not expressed interest

+------------------+--------------------------+
|      email       | property_beacon_interest |
+------------------+--------------------------+
| matt@example.com |            1534101377000 |
| eli@example.com  |                          |
+------------------+--------------------------+

When a customer expresses interest in a support conversation, our support team tags the conversation with a beacon-interest tag. There are two relevant tables:

1. helpscout.conversation with three relevant fields:

• id - The id of the conversation
• email - The email of the person who reached out to support
• created_at - A timestamp with the date/time the conversation was created

+----+-------------------+--------------------------+
| id |       email       |        created_at        |
+----+-------------------+--------------------------+
|  1 | matt@example.com  | 2018-08-14 14:02:10 UTC  |
|  2 | eli@example.com   | 2018-08-14 14:06:30 UTC  |
|  3 | matt@example.com  | 2018-08-14 14:07:33 UTC  |
|  4 | katia@example.com | 2018-08-14 14:11:30 UTC  |
|  5 | jen@example.com   | 2018-08-13 14:11:30 UTC  |
+----+-------------------+--------------------------+

2. There's also a helpscout.conversation_tag table with two relevant fields:

• conversation_id - The id of the conversation that was tagged. A conversation can have zero or more tags.
• tag - The name of the tag

+-----------------+-----------------+
| conversation_id |       tag       |
+-----------------+-----------------+
|               1 | new-trial       |
|               1 | bug-report      |
|               2 | beacon-interest |
|               4 | beacon-interest |
+-----------------+-----------------+

Your challenge:

Write a SQL query (any dialect is fine) that combines data from these two sources that lists everyone who has expressed interest in trying Beacon and when they first expressed that interest.

The end result using the example tables above should be a functioning SQL query that returns the following:

+-------------------+-------------------------+
|      email        |  expressed_interest_at  |
+-------------------+-------------------------+
| matt@example.com  | 2018-08-12 19:16:17 UTC |
| eli@example.com   | 2018-08-14 14:06:30 UTC |
| katia@example.com | 2018-08-14 14:11:30 UTC |
+-------------------+-------------------------+

You should include your query in the response field to this question in the online application.