FastPrepEmail Funnel: Opened Within a Day, Replied Within a Week
Problems/Email Funnel: Opened Within a Day, Replied Within a Week

Email Funnel: Opened Within a Day, Replied Within a Week

Superhuman logoSuperhuman● MediumFULLTIMEPHONE SCREEN
QuestionConstraints

Problem statement

Source note: A Superhuman data engineer technical screen was a one-hour live coding round in PostgreSQL or PySpark. The task was an email funnel over three event tables, sends, opens and replies: for emails sent in a chosen date range, one row per email with a flag for an open within a day and a flag for a reply within a week. It says an open or reply right at the 24-hour or 7-day mark does not count, and that one email can have many opens and replies.

The report does not say how the date range is given, how flags are encoded, or how to break ties in the order. Practice choices here: the range comes from a one-row report_period table and covers both end days by the calendar date of sent_ts; the flags are 1 or 0; the windows are measured as elapsed time from sent_ts, not by calendar day, so an event at the send time counts; a reply counts without an open; rows go by send time, then send_id. Opens and replies never come before their send. Column names beyond the three tables, limits and examples are ours.

The judged task matches the report at about 85%.

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: 1 if the email has at least one open less than 24 hours after sent_ts, otherwise 0. An open exactly 24 hours later does not count;
  • is_replied: 1 if the email has at least one reply less than 7 days (168 hours) after sent_ts, otherwise 0. 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

MySQLPostgreSQLPandas

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.

ColumnTypeNullableDescription
send_idPKIntegerNo—
sender_idIntegerNo—
recipient_emailTextNo—
sent_tsTimestampNo—

email_opens

One row per open event. The same email can be opened many times.

ColumnTypeNullableDescription
open_idPKIntegerNo—
send_idIntegerNo—
opened_tsTimestampNo—

Foreign key: send_id → email_sends(send_id)

email_replies

One row per reply event. The same email can get many replies.

ColumnTypeNullableDescription
reply_idPKIntegerNo—
send_idIntegerNo—
replied_tsTimestampNo—

Foreign key: send_id → email_sends(send_id)

report_period

Exactly one row: the first and last calendar day of the sending period.

ColumnTypeNullableDescription
start_dateDateNo—
end_dateDateNo—

Expected result

Your query or function must return these columns.

ColumnTypeNullableDescription
send_idIntegerNo—
is_openedIntegerNo—
is_repliedIntegerNo—

Row order: must match exactly. Numeric tolerance: 0.

Constraints

  • report_period has exactly one row, and start_date <= end_date.
  • Each of email_sends, email_opens and email_replies has from 0 to 500 rows.
  • Every id (send_id, sender_id, open_id, reply_id) is an integer from 1 to 10^6.
  • recipient_email is a lowercase address of 3 to 60 characters.
  • All timestamps are given to the second and fall from 2020-01-01 00:00:00 through 2030-12-31 23:59:59; all dates fall in the same range.
  • Every open and reply refers to an existing send_id and happens at or after that email's sent_ts. No value is NULL.

More Superhuman problems

  • Longest Distinct StringOA · Seen Sep 2026
  • Minimum Time on Two ProcessorsOA · Seen Sep 2026
  • Top Mutual-Friend RecommendationsOA · Seen Sep 2026
All Superhuman interview questionsSee Superhuman hiring insights