Email Funnel: Opened Within a Day, Replied Within a Week
Problem statement
An email product logs every email a user sends in email_sends. Each row of email_opens is one time the recipient opened an email, and each row of email_replies is one reply to an email; the same email can be opened or replied to many times. report_period has exactly one row with a start_date and an end_date.
Build a per-email funnel for the emails sent in the period. An email belongs to the period when the date part of its sent_ts is from start_date through end_date, both days included. Return exactly one row per such email, with:
send_id;is_opened:1if the email has at least one open less than 24 hours aftersent_ts, otherwise0. An open exactly 24 hours later does not count;is_replied:1if the email has at least one reply less than 7 days (168 hours) aftersent_ts, otherwise0. A reply exactly 7 days later does not count. A reply counts even if no open was recorded.
Sort the rows by sent_ts from earliest to latest, and break ties by send_id from smallest to largest. If no email was sent in the period, return no rows.
Table schema
Use the same input data with any supported language. Open the Schema tab in the editor to see the generated SQL setup or Pandas DataFrames.
email_sends
One row per email sent: who sent it, to which address, and when.
| Column | Type | Nullable | Description |
|---|---|---|---|
| send_idPK | Integer | No | — |
| sender_id | Integer | No | — |
| recipient_email | Text | No | — |
| sent_ts | Timestamp | No | — |
email_opens
One row per open event. The same email can be opened many times.
| Column | Type | Nullable | Description |
|---|---|---|---|
| open_idPK | Integer | No | — |
| send_id | Integer | No | — |
| opened_ts | Timestamp | No | — |
Foreign key: send_id → email_sends(send_id)
email_replies
One row per reply event. The same email can get many replies.
| Column | Type | Nullable | Description |
|---|---|---|---|
| reply_idPK | Integer | No | — |
| send_id | Integer | No | — |
| replied_ts | Timestamp | No | — |
Foreign key: send_id → email_sends(send_id)
report_period
Exactly one row: the first and last calendar day of the sending period.
| Column | Type | Nullable | Description |
|---|---|---|---|
| start_date | Date | No | — |
| end_date | Date | No | — |
Expected result
Your query or function must return these columns.
| Column | Type | Nullable | Description |
|---|---|---|---|
| send_id | Integer | No | — |
| is_opened | Integer | No | — |
| is_replied | Integer | No | — |
Row order: must match exactly. Numeric tolerance: 0.
Constraints
report_periodhas exactly one row, andstart_date <= end_date.- Each of
email_sends,email_opensandemail_replieshas from0to500rows. - Every id (
send_id,sender_id,open_id,reply_id) is an integer from1to10^6. recipient_emailis a lowercase address of 3 to 60 characters.- All timestamps are given to the second and fall from
2020-01-01 00:00:00through2030-12-31 23:59:59; all dates fall in the same range. - Every open and reply refers to an existing
send_idand happens at or after that email'ssent_ts. No value isNULL.