Google Senior Data Analyst Interview Questions and Answers 2026

WITH session_stats AS (
    SELECT
        s.query_category,
        COUNT(*)                                                         AS total_sessions,
        COUNTIF(s.results_count = 0)                                     AS zero_result_sessions,
        COUNTIF(s.results_count = 0) / COUNT(*) * 100                   AS zero_result_rate,
        COUNTIF(s.results_count > 0)                                     AS sessions_with_results,
        COUNT(DISTINCT c.session_id)                                     AS sessions_with_clicks,
        SAFE_DIVIDE(
            COUNT(DISTINCT c.session_id),
            COUNTIF(s.results_count > 0)
        ) * 100                                                          AS ctr_pct
    FROM search_sessions s
    LEFT JOIN search_clicks c USING (session_id)
    WHERE s.session_ts >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
    GROUP BY s.query_category
),
with_avg AS (
    SELECT *,
        AVG(zero_result_rate) OVER ()  AS overall_avg_zero_rate
    FROM session_stats
)
SELECT
    query_category,
    total_sessions,
    ROUND(zero_result_rate, 2)   AS zero_result_rate_pct,
    ROUND(ctr_pct, 2)            AS ctr_pct,
    ROUND(overall_avg_zero_rate, 2) AS overall_avg_pct
FROM with_avg
WHERE zero_result_rate > overall_avg_zero_rate
ORDER BY zero_result_rate DESC;

WITH creator_cohort AS (
    SELECT
        creator_id,
        DATE_TRUNC(MIN(upload_ts), MONTH)  AS cohort_month
    FROM video_uploads
    GROUP BY creator_id
    HAVING cohort_month >= DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL 12 MONTH), MONTH)
),
monthly_uploads AS (
    SELECT
        v.creator_id,
        DATE_TRUNC(v.upload_ts, MONTH)  AS upload_month
    FROM video_uploads v
    GROUP BY v.creator_id, DATE_TRUNC(v.upload_ts, MONTH)
),
cohort_activity AS (
    SELECT
        c.cohort_month,
        DATE_DIFF(u.upload_month, c.cohort_month, MONTH)  AS months_since_first,
        COUNT(DISTINCT u.creator_id)                        AS retained_creators
    FROM creator_cohort c
    JOIN monthly_uploads u USING (creator_id)
    WHERE DATE_DIFF(u.upload_month, c.cohort_month, MONTH) BETWEEN 1 AND 3
    GROUP BY c.cohort_month, DATE_DIFF(u.upload_month, c.cohort_month, MONTH)
),
cohort_size AS (
    SELECT cohort_month, COUNT(*) AS cohort_total
    FROM creator_cohort
    GROUP BY cohort_month
)
SELECT
    cs.cohort_month,
    cs.cohort_total,
    MAX(IF(ca.months_since_first=1, ca.retained_creators, NULL))  AS retained_m1,
    MAX(IF(ca.months_since_first=2, ca.retained_creators, NULL))  AS retained_m2,
    MAX(IF(ca.months_since_first=3, ca.retained_creators, NULL))  AS retained_m3,
    ROUND(MAX(IF(ca.months_since_first=1, ca.retained_creators, NULL))
          / cs.cohort_total * 100, 1)  AS retention_m1_pct,
    ROUND(MAX(IF(ca.months_since_first=2, ca.retained_creators, NULL))
          / cs.cohort_total * 100, 1)  AS retention_m2_pct,
    ROUND(MAX(IF(ca.months_since_first=3, ca.retained_creators, NULL))
          / cs.cohort_total * 100, 1)  AS retention_m3_pct
FROM cohort_size cs
LEFT JOIN cohort_activity ca USING (cohort_month)
GROUP BY cs.cohort_month, cs.cohort_total
ORDER BY cs.cohort_month;

WITH daily_funnel AS (
    SELECT
        city,
        DATE(event_ts)                                        AS event_date,
        COUNTIF(event_type = 'search')                        AS searches,
        COUNTIF(event_type = 'place_view')                    AS place_views,
        COUNTIF(event_type = 'nav_start')                     AS nav_starts,
        SAFE_DIVIDE(COUNTIF(event_type='place_view'),
                    COUNTIF(event_type='search')) * 100       AS s_to_pv_rate,
        SAFE_DIVIDE(COUNTIF(event_type='nav_start'),
                    COUNTIF(event_type='place_view')) * 100   AS pv_to_nav_rate,
        SAFE_DIVIDE(COUNTIF(event_type='nav_start'),
                    COUNTIF(event_type='search')) * 100       AS overall_rate
    FROM maps_events
    WHERE event_ts >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 14 DAY)
    GROUP BY city, DATE(event_ts)
),
with_wow AS (
    SELECT
        *,
        LAG(overall_rate, 7) OVER (PARTITION BY city ORDER BY event_date)  AS prev_week_rate,
        CASE
            WHEN (searches - place_views) >= (place_views - nav_starts) THEN 'Search→PlaceView'
            ELSE 'PlaceView→NavStart'
        END  AS biggest_drop_step
    FROM daily_funnel
)
SELECT
    city, event_date,
    searches, place_views, nav_starts,
    ROUND(s_to_pv_rate, 1)   AS search_to_placeview_pct,
    ROUND(pv_to_nav_rate, 1) AS placeview_to_nav_pct,
    ROUND(overall_rate, 1)   AS overall_funnel_rate_pct,
    ROUND(prev_week_rate, 1) AS prev_week_overall_pct,
    CASE WHEN overall_rate > prev_week_rate THEN '▲ IMPROVED'
         WHEN overall_rate < prev_week_rate THEN '▼ DECLINED'
         ELSE '— STABLE' END  AS wow_trend,
    biggest_drop_step
FROM with_wow
WHERE event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
ORDER BY city, event_date;