Days from signup to first order
mediumDays from signup to first order
Stripe SQL Interview Question
Stripe's growth team tracks how long an online store's new customers take to make their first purchase after signing up.
For each customer who has placed at least one order, find their name and the number of days from signing up to their first order, as days_to_first_order. Count an order of any status as a first order.
Asked of
- Data Analyst
- Product Analyst
- Data Scientist
- Analytics Engineer
customersTable10 rows
| Column Name | Type |
|---|---|
| customer_id | BIGINT |
| name | VARCHAR |
| country | VARCHAR |
| segment | VARCHAR |
| signup_date | DATE |
ordersTable35 rows
| Column Name | Type |
|---|---|
| order_id | BIGINT |
| customer_id | BIGINT |
| order_date | DATE |
| status | VARCHAR |
| amount | DOUBLE |
customersExample Input
| customer_id | name | country | segment | signup_date |
|---|---|---|---|---|
| 1 | Priya Sharma | India | consumer | 2023-01-14 |
| 2 | Marcus Webb | United States | enterprise | 2023-02-03 |
| 3 | Ana Sousa | Brazil | consumer | 2023-02-27 |
| 4 | Liam O'Connor | Ireland | small_business | 2023-03-15 |
| 5 | Yuki Tanaka | Japan | enterprise | 2023-04-02 |
| 6 | Fatima Al-Rashid | United Arab Emirates | consumer | 2023-05-21 |
| 7 | Daniel Okafor | Nigeria | small_business | 2023-06-08 |
ordersExample Input
| order_id | customer_id | order_date | status | amount |
|---|---|---|---|---|
| 1001 | 1 | 2024-01-05 | completed | 120.5 |
| 1002 | 2 | 2024-01-07 | completed | 2400 |
| 1003 | 1 | 2024-01-11 | completed | 89.99 |
| 1004 | 3 | 2024-01-15 | canceled | 45 |
| 1005 | 4 | 2024-01-18 | completed | 610.75 |
| 1006 | 2 | 2024-01-22 | completed | 1850.25 |
| 1007 | 5 | 2024-01-29 | completed | 3200 |
| 1008 | 1 | 2024-02-02 | refunded | 75 |
| 1009 | 6 | 2024-02-06 | completed | 55.4 |
| 1010 | 3 | 2024-02-09 | completed | 132.6 |
Example Output
| name | days_to_first_order |
|---|---|
| Ana Sousa | 322 |
| Fatima Al-Rashid | 261 |
| Liam O'Connor | 309 |
| Marcus Webb | 338 |
| Priya Sharma | 356 |
| Yuki Tanaka | 302 |
Explanation
Priya Sharma signed up on January 14, 2023, and placed a first order on January 5, 2024, which is 356 days later. Daniel Okafor has never ordered, so that name does not appear.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Stripe
Difficulty
medium
Topic
dates
Your query
Starting the SQL engine...