sql.notes
mediumonline store · Q3
conversionaggregationdate math

First purchase within 7 days

Find the customers whose first completed order happened within 7 days of signing up.

  • compare calendar dates: the order's date minus signup_date must be between 0 and 7 days, both included. So an order at 23:30 on day 7 counts.
  • it's the first completed order that matters. A cancelled order in the first week doesn't make someone count.
  • first_order_date is the date (not timestamp) of that first completed order
output columns
customer_idsignup_datefirst_order_date

uses ,

ctrl + enter to run on the sample data · ctrl + shift + enter to submit

run your query to see the output here. Submit checks it against the sample data, hidden test cases, random data and a large dataset.