sql.md

Imagine we're rolling out a 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, form.contact, with two relevant fields:

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

+------------------+--------------------------+
|      email       |  property_chat_interest  |
+------------------+--------------------------+
| dixi@example.com |            1534938377000 |
| eli@example.com  |                          |
+------------------+--------------------------+

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

1. automattic.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 | dixi@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 automattic.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 | property_chat_interest |
|               4 | property_chat_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 our live chat tool 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  |
+-------------------+-------------------------+
| dixi@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 |
+-------------------+-------------------------+

Where should I submit the results of this exercise?