Signup Activation %
ASKED IN INTERVIEWTik Tok
Problem Statement
New users at TikTok sign up using their email. To activate their account, they must confirm a code sent via SMS.
Write an SQL query to find the activation rate of users. The activation rate is the percentage of users who confirmed their account via SMS within 24 hours of signing up.
Rules:
- The activation rate should be rounded to 2 decimal places.
- A user is considered activated only if they have a "Confirmed" status in the texts table and the time difference is <= 24 hours.
- Users who never received a text or never confirmed should be included in the denominator (total signups).
- Return a single column: confirm_rate.
- You have access to the emails, texts, and a user_profiles table for metadata.
Tests your understanding of
JOIN, Dates, Aggregation and Subqueries
Input Tables
emails
| email_id(INTEGER) | user_id(INTEGER) | signup_date(TIMESTAMP) |
|---|---|---|
| 125 | 7722 | 2022-06-14 10:00:00 |
| 433 | 8821 | 2022-06-15 11:00:00 |
| 551 | 3345 | 2022-06-16 12:00:00 |
| 600 | 1122 | 2022-06-17 09:00:00 |
| 700 | 9999 | 2022-06-18 14:00:00 |
texts
| text_id(INTEGER) | user_id(INTEGER) | signup_action(VARCHAR) | action_date(TIMESTAMP) |
|---|---|---|---|
| 1 | 7722 | Confirmed | 2022-06-15 09:00:00 |
| 2 | 8821 | Confirmed | 2022-06-15 13:00:00 |
| 3 | 3345 | Not Confirmed | 2022-06-16 15:00:00 |
| 4 | 1122 | Confirmed | 2022-06-19 10:00:00 |
| 5 | 7722 | Not Confirmed | 2022-06-14 11:00:00 |
user_profiles
| user_id(INTEGER) | country(VARCHAR) |
|---|---|
| 7722 | USA |
| 8821 | UK |
| 3345 | India |
| 1122 | Brazil |
| 9999 | Canada |
Expected Output
| confirm_rate(DECIMAL) |
|---|
| 0.4 |
Tags
MediumASKED IN INTERVIEWJOINDatesAggregationSubqueries
20-25 min
42%