logo

Back to questions

Marketing Touch Streak

Hard

Background: Marketing touches, also known as touch points, are a brand's point of contact with its customers.

As a Data Analyst on Snowflake's Marketing Analytics team, you're examining customer relationship management (CRM) data to find contacts that meet 2 conditions:

  1. Had a marketing touch for 3 or more weeks in a row
  2. At least one of their marketing touches was a 'trial_request' type

List all the emails for these contacts.

Table:

Column NameType
event_idinteger
contact_idinteger
event_typestring ('webinar', 'conference_registration', 'trial_request')
event_datedate

Example Input:

event_idcontact_idevent_typeevent_date
11webinar4/17/2022
21trial_request4/23/2022
31whitepaper_download4/30/2022
42handson_lab4/19/2022
52trial_request4/23/2022
62conference_registration4/24/2022
73whitepaper_download4/30/2022
84trial_request4/30/2022
94webinar5/14/2022

Table:

Column NameType
contact_idinteger
emailstring

Example Input:

Example Output:

Explanation:

Contact ID 1 (andy.markus@att.net) is the only one who fulfilled both conditions - one of their event types was trial_request, and the marketing touch points happened consecutively for 3 weeks

Meanwhile, user 2 can't be included because their touch points happened in the same week.

PostgreSQL 14

Refer friends to get bonus content & cool prizes.