Guest User

Untitled

a guest
Sep 11th, 2018
132
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.91 KB | None | 0 0
  1. Imagine we're rolling out a live chat tool to existing customers who have expressed interest trying it.
  2.  
  3. 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.
  4.  
  5. For the interest form, there is one table, `form.contact`, with two relevant fields:
  6.  
  7. - `email` - The user's email address
  8. - `property_chat_interest` - A Unix timestamp in milliseconds representing when they filled out the form or null if they have not expressed interest
  9.  
  10. ```
  11. +------------------+--------------------------+
  12. | email | property_chat_interest |
  13. +------------------+--------------------------+
  14. | dixi@example.com | 1534938377000 |
  15. | eli@example.com | |
  16. +------------------+--------------------------+
  17. ```
  18.  
  19. 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:
  20.  
  21. 1. `automattic.conversation` with three relevant fields:
  22.  
  23. - `id` - The id of the conversation
  24. - `email` - The email of the person who reached out to support
  25. - `created_at` - A timestamp with the date/time the conversation was created
  26.  
  27. ```
  28. +----+-------------------+--------------------------+
  29. | id | email | created_at |
  30. +----+-------------------+--------------------------+
  31. | 1 | dixi@example.com | 2018-08-14 14:02:10 UTC |
  32. | 2 | eli@example.com | 2018-08-14 14:06:30 UTC |
  33. | 3 | matt@example.com | 2018-08-14 14:07:33 UTC |
  34. | 4 | katia@example.com | 2018-08-14 14:11:30 UTC |
  35. | 5 | jen@example.com | 2018-08-13 14:11:30 UTC |
  36. +----+-------------------+--------------------------+
  37. ```
  38.  
  39. 2. There's also a `automattic.conversation_tag` table with two relevant fields:
  40.  
  41. - `conversation_id` - The id of the conversation that was tagged. A conversation can have zero or more tags.
  42. - `tag` - The name of the tag
  43.  
  44. ```
  45. +-----------------+------------------------+
  46. | conversation_id | tag |
  47. +-----------------+------------------------+
  48. | 1 | new-trial |
  49. | 1 | bug-report |
  50. | 2 | property_chat_interest |
  51. | 4 | property_chat_interest |
  52. +-----------------+------------------------+
  53. ```
  54.  
  55. Your challenge:
  56.  
  57. 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.
  58.  
  59. The end result using the example tables above should be a functioning SQL query that returns the following:
  60.  
  61. ```
  62. +-------------------+-------------------------+
  63. | email | expressed_interest_at |
  64. +-------------------+-------------------------+
  65. | dixi@example.com | 2018-08-12 19:16:17 UTC |
  66. | eli@example.com | 2018-08-14 14:06:30 UTC |
  67. | katia@example.com | 2018-08-14 14:11:30 UTC |
  68. +-------------------+-------------------------+
  69. ```
Add Comment
Please, Sign In to add comment