SQL interview questions · Product analyst
14 SQL interview questions for product analysts
Product analytics interviews ask you to define and compute product metrics from raw event data: how many users convert, come back, stick around or churn. Expect to reason about time windows, first events and users with no activity.
What product analyst SQL interviews test
- Funnels and conversion within a time window
- Retention cohorts and day-1 return rates
- DAU, MAU and stickiness
- Churn and new vs returning users
- Attribution and sessionization from raw events
Funnels and conversion
- Signup-to-purchase funnelReturn step (the event name) and users (distinct users who did it) for signup, view_pricing, checkout and purchase, in that funnel order.Pattern: COUNT(DISTINCT) · funnel stepsPro
- Fast convertersReturn username and days_to_purchase (days from the user's created_at to their purchase event) for users who purchased within 7 days (day 7 counts). Fastest first, ties by username.Pattern: JOIN + date differencePro
- Pricing to checkout within an hourFor every user who viewed /pricing, return user_id, first_pricing_at (their first /pricing view) and minutes_to_checkout (whole minutes from first_pricing_at to their first /checkout view after it and at most 60 minutes later; 60 counts; NULL if there is none). Sort by user_id.Pattern: Funnel with a time window · range joinPro
- No-show rate without test accountsUsing only viewings booked by real buyers (viewing_buyers.is_test is false), return agent, viewings (all their viewings, any outcome), no_shows (outcome 'no_show') and no_show_rate (no_shows as a percentage of viewings, rounded to 2 decimals). Agents with no real viewings are left out. Sort by no_show_rate (highest first), then agent.Pattern: Conditional ratio · JOIN filter · FILTERPro
Retention and engagement
- Day-1 return rateGroup users by cohort_month, the month (YYYY-MM) of their first app open. For each cohort return cohort_month, users (users in the cohort), returned_day_1 (users who also opened the app on the day right after their first open) and day_1_rate (returned_day_1 as a percentage of users, rounded to 2 decimals). Include cohorts where nobody returned. Sort by cohort_month.Pattern: MIN first activity · LEFT JOIN on date + 1Pro
- Month-1 retention by signup cohortFor users who signed up (users.created_at) in January through March 2024, return cohort_month (YYYY-MM), cohort_size, retained (cohort users with at least one app open in the calendar month after their signup month) and retention_pct (retained as a percentage of cohort_size, rounded to 1 decimal). Oldest cohort first.Pattern: Cohorts · date_trunc · LEFT JOINPro
- DAU/MAU stickinessFrom app_opens, for each month return month (YYYY-MM), avg_dau (distinct user-days divided by the number of days in that month, rounded to 2 decimals), mau (distinct users that month) and stickiness_pct (100 * user-days / (days in month * mau), rounded to 1 decimal). Oldest first.Pattern: Distinct user-days · days in month · ratiosPro
- Monthly churn rateUsing app_opens, for February, March and April 2024 return month (YYYY-MM), churned_users (users active in the previous month with no app opens this month) and churn_rate_pct (churned_users as a percentage of the previous month's active users, rounded to 1 decimal). Oldest first.Pattern: Anti-join across months · churnPro
- New vs returning usersUsing app_opens, for each month return month (YYYY-MM), new_users (active that month for the first time ever), returning_users (active that month, first active in an earlier month) and cumulative_users (distinct users seen in any month up to and including this one). Oldest first.Pattern: First-seen month · running distinct countPro
Events, sessions and attribution
- Sessions from raw pageviewsSplit pageviews into sessions per user: a gap of more than 30 minutes since the user's previous view starts a new session (exactly 30 minutes stays in the same session). Return user_id, session_start, session_end and pageviews (views in the session). Sort by user_id, session_start.Pattern: LAG · running SUM · sessionizationPro
- First-touch vs last-touch attributionFor users with a purchase event, consider only marketing touches on or before their purchase date. Return channel, first_touch (purchases whose earliest touch was this channel) and last_touch (purchases whose latest touch was this channel), one row per channel (email, paid_search, referral, social), sorted by channel.Pattern: ROW_NUMBER both ways · attributionPro
- Daily active users, zero days includedReturn day (a DATE) and active_users (distinct users who logged in that day) for every day from 2024-08-01 to 2024-08-14, including days with 0. Oldest first.Pattern: generate_series · LEFT JOIN · filling date gapsPro
- Activity streaks as date rangesReturn user_id, start_date, end_date and days (number of distinct days in the streak) for every streak of at least 3 consecutive days in app_opens. Sort by user_id, start_date.Pattern: Gaps and islands · duplicate daysPro
- Items bought togetherReturn item_a, item_b (the two items of a pair, item_a alphabetically first) and baskets (number of different baskets containing both) for every pair found together in at least 2 baskets. Sort by baskets (highest first), then item_a, then item_b.Pattern: Self-join · a.item < b.item · COUNT(DISTINCT)Pro
Interview tip: Define the metric before writing SQL: who's in the denominator, what time window, what counts as active. A correct query for the wrong definition still fails the interview.