Interview Question Bank · MIN per entity · PERCENTILE_CONT
8. Median days to first dashboard
MediumSaaSQuestion #116
Concept: MIN per entity · PERCENTILE_CONTshow
Time-to-value is measured per account: find each account's first qualifying event with MIN, subtract its start date, then summarize. Use the median, not the average, because a few slow accounts drag the mean up. PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x) returns the middle value, or the halfway point between the two middle values when the count is even.
Example (different data):
WITH firsts AS ( SELECT p.id, MIN(r.reviewed_on) - p.opened_on AS days FROM pull_requests p JOIN reviews r ON r.pr_id = p.id GROUP BY p.id, p.opened_on ) SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY days) AS median_days FROM firsts;
Your task
Tallyboard counts an account as onboarded once it builds its first dashboard (event 'create_dashboard'). The onboarding team wants the typical time it takes, measured from signup.
For accounts that have created at least one dashboard, compute days from signup_date to the date of their first create_dashboard event. Return one row: accounts_measured and median_days (PERCENTILE_CONT median, rounded to 1 decimal).
Starting Postgres in your browser…
Checking your access…