Calculate the signup activation rate. A user is activated if they confirmed their signup via SMS. Return the rate rounded to 2 decimal places.
This funnel analysis problem reflects TikTok's focus on growth metrics and user activation. Understanding conversion rates at each step of the user journey is critical for product teams. This question tests your ability to calculate rates correctly using LEFT JOINs to preserve the denominator (all signups) while counting only successful conversions in the numerator—a common pattern in A/B testing and funnel analysis.
Key concepts: LEFT JOIN to preserve all base records, conditional aggregation with COUNT() and filtering, division with proper type casting (1.0 for float division), ROUND() for precision control, and understanding the difference between INNER JOIN (would exclude non-confirmed users) and LEFT JOIN (keeps all signups). This tests your grasp of join semantics and their impact on metrics.
TikTok's growth team uses similar queries to: calculate conversion rates across signup funnels, measure email/SMS verification success rates, track feature adoption metrics across user cohorts, generate daily KPI dashboards for product managers, identify drop-off points in onboarding flows, and power automated alerts when activation rates fall below thresholds.
When tackling this TikTok problem, the key is to understand the grain of the result. Are you returning one row per user, or one row per category? Always start by identifying your unique join keys and consider if filtered aggregations (CASE WHEN) are more efficient than multiple subqueries.
Be careful with NULL values in your JOIN conditions or aggregate functions. In interview scenarios, datasets often include edge cases like zero-count categories or duplicate entries that can throw off a simple COUNT(*) if not handled with DISTINCT.
Share your approach, optimized queries, or ask questions. Learning from others is the fastest way to master SQL.
SELECT ROUND(
COUNT(t.email_id) * 1.0 / COUNT(e.email_id),
2
) AS confirm_rate
FROM emails e
LEFT JOIN texts t
ON e.email_id = t.email_id
AND t.signup_action = 'Confirmed'The LEFT JOIN retains all emails in the denominator while only matching confirmed text rows in the numerator. COUNT(t.email_id) naturally skips NULLs from unmatched rows, counting only confirmed signups. Multiplying by 1.0 prevents integer division truncation. Moving signup_action = 'Confirmed' to the ON clause — not WHERE — is critical: placing it in WHERE silently converts the LEFT JOIN to an INNER JOIN, shrinking the denominator and inflating the rate. Efficient with an index on texts(email_id, signup_action) and the most readable, portable approach.
SELECT ROUND(
SUM(CASE WHEN t.signup_action = 'Confirmed' THEN 1.0 ELSE 0 END)
/ COUNT(e.email_id),
2
) AS confirm_rate
FROM emails e
LEFT JOIN texts t ON e.email_id = t.email_idUsing SUM(CASE WHEN ... THEN 1.0 ELSE 0 END) makes the conditional counting logic explicit, removing reliance on COUNT's implicit NULL-skipping behavior. Using 1.0 in the THEN branch ensures floating-point arithmetic without a separate CAST. This pattern is preferred when clarity matters — it reads as 'sum the confirmed ones, divide by total emails' — and naturally extends to computing multiple rates in one pass. Performance is identical to the LEFT JOIN COUNT approach; both execute the same join and aggregate scan.
Both solutions require a single scan of the emails table and a join to the texts table. An index on texts(email_id, signup_action) is particularly valuable — it allows the database to locate matching email IDs and filter for 'Confirmed' in one index scan rather than a full texts table scan. Without this index, the optimizer will likely choose a hash join with a full scan of texts. At scale, if emails is partitioned by signup_date, adding a date range filter to your query dramatically reduces scan size for cohort-specific activation analysis. COUNT vs SUM/CASE shows negligible performance difference; the choice is purely stylistic. Avoid ROUND inside a subquery unnecessarily — ROUND is cheap and should be the outermost operation. Never use COUNT(DISTINCT user_id) in either numerator or denominator: one user may have multiple email IDs, and the correct measurement unit is per-email-attempt, not per-user.
The most frequent mistake is integer division: COUNT(confirmed) / COUNT(total) truncates to 0 in databases that default to integer arithmetic when the numerator is smaller. Always cast using * 1.0, CAST(... AS FLOAT), or put 1.0 in the SUM/CASE THEN branch. A critical structural mistake is placing signup_action = 'Confirmed' in WHERE instead of ON — this filters out unmatched LEFT JOIN rows (which have NULL for signup_action), converting the LEFT JOIN to an INNER JOIN and artificially inflating the activation rate by shrinking the denominator. Using INNER JOIN produces the same bug. Another pitfall is misidentifying the denominator: the question asks for rate across all email signups, so COUNT(e.email_id) is correct, not COUNT(DISTINCT e.user_id) or COUNT(t.email_id).
Placing a filter in WHERE on a LEFT JOIN column converts it to an effective INNER JOIN: unmatched rows have NULL for all right-side columns, and NULL = 'Confirmed' evaluates to UNKNOWN (not TRUE), so those rows are excluded. Moving the filter to ON means unmatched rows still appear with NULLs intact, keeping the full email list in the denominator. This is the most common join-filter placement bug in rate-calculation problems.
Multiplying by 1.0 promotes the integer COUNT result to floating-point, preventing integer division from truncating results to 0 or a whole number. Alternatives include CAST(COUNT(...) AS FLOAT), CAST(... AS DECIMAL(10,4)), or using 1.0 in a SUM/CASE THEN branch. In PostgreSQL the :: cast operator works: COUNT(...)::float. The specific method doesn't matter as long as at least one operand is non-integer before division occurs.
Add GROUP BY on a date truncation of e.signup_date and include it in the SELECT: GROUP BY DATE_TRUNC('week', e.signup_date) in PostgreSQL or DATE_FORMAT(e.signup_date, '%Y-%u') in MySQL. This produces one activation rate per cohort week, enabling trend analysis over time. Ensure you ORDER BY the date column for chronological presentation, making cohort decay or improvement patterns immediately visible.