Google Senior Business Intelligence Analyst Interview Questions and Answers 2026

WITH cohorts AS (
    SELECT
        u.user_id,
        DATE_TRUNC(u.signup_date, WEEK)                AS cohort_week,
        e.event_date,
        DATE_DIFF(e.event_date, u.signup_date, DAY)    AS days_since_signup
    FROM users u
    JOIN events e USING (user_id)
    WHERE e.event_date BETWEEN u.signup_date
                             AND DATE_ADD(u.signup_date, INTERVAL 30 DAY)
),
cohort_sizes AS (
    SELECT cohort_week, COUNT(DISTINCT user_id) AS cohort_size
    FROM cohorts
    GROUP BY cohort_week
)
SELECT
    c.cohort_week,
    cs.cohort_size,
    ROUND(100.0 * COUNT(DISTINCT CASE WHEN days_since_signup BETWEEN 6  AND 8  THEN c.user_id END) / cs.cohort_size, 1) AS day7_pct,
    ROUND(100.0 * COUNT(DISTINCT CASE WHEN days_since_signup BETWEEN 13 AND 15 THEN c.user_id END) / cs.cohort_size, 1) AS day14_pct,
    ROUND(100.0 * COUNT(DISTINCT CASE WHEN days_since_signup BETWEEN 29 AND 31 THEN c.user_id END) / cs.cohort_size, 1) AS day30_pct
FROM cohorts c
JOIN cohort_sizes cs USING (cohort_week)
GROUP BY c.cohort_week, cs.cohort_size
ORDER BY c.cohort_week;

WITH spend_90d AS (
    SELECT
        advertiser_id,
        SUM(spend_usd) AS total_spend
    FROM ad_spend
    WHERE campaign_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
    GROUP BY advertiser_id
),
ranked AS (
    SELECT
        advertiser_id,
        total_spend,
        RANK()   OVER (ORDER BY total_spend DESC)                  AS spend_rank,
        NTILE(10) OVER (ORDER BY total_spend DESC)                 AS decile,
        ROUND(100.0 * total_spend / SUM(total_spend) OVER (), 3)   AS pct_of_total
    FROM spend_90d
)
SELECT
    advertiser_id,
    total_spend,
    spend_rank,
    pct_of_total
FROM ranked
WHERE decile = 1
ORDER BY total_spend DESC;

WITH monthly_watch AS (
    SELECT
        v.category,
        DATE_TRUNC(vv.view_date, MONTH)              AS month_start,
        SUM(vv.watch_seconds) / 3600.0               AS total_watch_hours
    FROM video_views vv
    JOIN videos v ON vv.video_id = v.video_id
    GROUP BY v.category, month_start
),
with_lag AS (
    SELECT
        category,
        month_start,
        total_watch_hours,
        LAG(total_watch_hours) OVER (
            PARTITION BY category
            ORDER BY month_start
        ) AS prev_month_hours
    FROM monthly_watch
)
SELECT
    category,
    month_start,
    ROUND(total_watch_hours, 0)  AS watch_hours,
    ROUND(
        100.0 * (total_watch_hours - prev_month_hours)
              / NULLIF(prev_month_hours, 0), 1
    )                             AS mom_growth_pct,
    CASE
        WHEN (total_watch_hours - prev_month_hours) / NULLIF(prev_month_hours, 0) < -0.20
        THEN '⚠️ Declining'
        ELSE 'OK'
    END                           AS status
FROM with_lag
WHERE prev_month_hours IS NOT NULL
ORDER BY month_start DESC, mom_growth_pct ASC;

WITH ctr_by_segment AS (
    SELECT
        position,
        device_type,
        SUM(clicks)                                      AS total_clicks,
        SUM(impressions)                                 AS total_impressions,
        SAFE_DIVIDE(SUM(clicks), SUM(impressions))       AS segment_ctr
    FROM search_results
    WHERE query_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
    GROUP BY position, device_type
),
with_platform_avg AS (
    SELECT
        *,
        AVG(segment_ctr) OVER () AS platform_avg_ctr
    FROM ctr_by_segment
)
SELECT
    position,
    device_type,
    ROUND(segment_ctr * 100, 2)      AS ctr_pct,
    ROUND(platform_avg_ctr * 100, 2) AS platform_avg_pct,
    ROUND((segment_ctr - platform_avg_ctr) * 100, 2) AS ctr_gap_pp,
    CASE
        WHEN segment_ctr < platform_avg_ctr * 0.8 THEN 'Under-performing'
        ELSE 'Within range'
    END AS performance_flag
FROM with_platform_avg
ORDER BY ctr_gap_pp ASC;

WITH session_stats AS (
    SELECT
        session_id,
        user_id,
        COUNT(*)                                                        AS event_count,
        TIMESTAMP_DIFF(MAX(event_ts), MIN(event_ts), SECOND)           AS session_duration_secs,
        SAFE_DIVIDE(
            TIMESTAMP_DIFF(MAX(event_ts), MIN(event_ts), SECOND),
            NULLIF(COUNT(*) - 1, 0)
        )                                                               AS avg_secs_between_events
    FROM session_events
    GROUP BY session_id, user_id
),
platform_stats AS (
    SELECT
        AVG(event_count) AS mean_events,
        STDDEV(event_count) AS std_events
    FROM session_stats
)
SELECT
    s.session_id,
    s.user_id,
    s.event_count,
    s.avg_secs_between_events,
    ROUND((s.event_count - p.mean_events) / NULLIF(p.std_events, 0), 2) AS z_score,
    CASE
        WHEN s.event_count > 500 THEN 'rule:high_volume'
        WHEN s.avg_secs_between_events < 1 THEN 'rule:too_fast'
        WHEN (s.event_count - p.mean_events) / NULLIF(p.std_events, 0) > 3 THEN 'stat:3sigma'
        ELSE 'clean'
    END AS bot_flag
FROM session_stats s
CROSS JOIN platform_stats p
WHERE
    s.event_count > 500
    OR s.avg_secs_between_events < 1
    OR (s.event_count - p.mean_events) / NULLIF(p.std_events, 0) > 3
ORDER BY z_score DESC;

from statsmodels.stats.proportion import proportions_ztest
import numpy as np

n = 200_000
conv_ctrl = 0.043  # 4.3%
conv_var  = 0.048  # 4.8%

counts = np.array([conv_var * n, conv_ctrl * n])
nobs   = np.array([n, n])

z, p = proportions_ztest(counts, nobs, alternative='larger')
print(f"Z-stat  : {z:.3f}")
print(f"P-value : {p:.6f}")
print(f"Result  : {'Significant ✓' if p < 0.05 else 'Not significant ✗'}")

# Relative and absolute lift
abs_lift = conv_var - conv_ctrl
rel_lift = abs_lift / conv_ctrl * 100
print(f"\nAbsolute lift  : {abs_lift*100:.1f}pp")
print(f"Relative lift  : {rel_lift:.1f}%")

# Google-scale business impact
monthly_users = 1_000_000_000
retained_delta = abs_lift * monthly_users
print(f"\nAt Google scale (1B users):")
print(f"  Additional retained users/month : {retained_delta:,.0f}")
print(f"  Estimated ARR impact (₹12/user) : ₹{retained_delta * 12 / 1e9:.2f}B")

import pandas as pd
from prophet import Prophet

# Prepare data in Prophet format
df_prophet = df[["date", "revenue"]].rename(
    columns={"date": "ds", "revenue": "y"}
)

# Build model with weekly + yearly seasonality
model = Prophet(
    weekly_seasonality=True,
    yearly_seasonality=True,
    changepoint_prior_scale=0.05,   # conservative — avoids overfitting
    interval_width=0.90             # 90% confidence interval
)
model.fit(df_prophet)

# Generate 30-day forecast
future = model.make_future_dataframe(periods=30)
forecast = model.predict(future)

# Extract key columns
result = forecast[["ds", "yhat", "yhat_lower", "yhat_upper"]].tail(30)
result.columns = ["date", "forecast", "lower_90ci", "upper_90ci"]

# Sanity check: flag days where range is > 40% of forecast (high uncertainty)
result["uncertainty_flag"] = (
    (result["upper_90ci"] - result["lower_90ci"]) / result["forecast"] > 0.40
)
print(result.head(10))

import pandas as pd
import numpy as np

# Compute weekly summary per user
user_weekly = (
    df.groupby("user_id")
    .agg(
        avg_weekly_sessions = ("session_id", "nunique"),
        avg_nav_km          = ("navigation_km", "mean"),
        total_searches      = ("search_query", "count")
    )
    .reset_index()
)

# Normalise each feature to 0-1 for scoring
for col in ["avg_weekly_sessions", "avg_nav_km", "total_searches"]:
    user_weekly[f"{col}_norm"] = (
        (user_weekly[col] - user_weekly[col].min())
        / (user_weekly[col].max() - user_weekly[col].min())
    )

# Composite engagement score (equal weights)
user_weekly["engagement_score"] = (
    user_weekly["avg_weekly_sessions_norm"] * 0.40
  + user_weekly["avg_nav_km_norm"]          * 0.35
  + user_weekly["total_searches_norm"]      * 0.25
)

# Segment using percentile cutoffs
user_weekly["segment"] = pd.cut(
    user_weekly["engagement_score"],
    bins=[0, 0.33, 0.66, 1.0],
    labels=["Light", "Moderate", "Heavy"],
    include_lowest=True
)

print(user_weekly["segment"].value_counts(normalize=True).mul(100).round(1))

import pandas as pd

# df: state | active_users | smartphone_users
df["penetration_pct"] = (df["active_users"] / df["smartphone_users"] * 100).round(2)

national_avg = (df["active_users"].sum() / df["smartphone_users"].sum() * 100)
print(f"National penetration: {national_avg:.1f}%")

low_pen = df[df["penetration_pct"] < 20].copy()
low_pen["target_users"] = (national_avg / 100 * low_pen["smartphone_users"]).astype(int)
low_pen["uplift_users"] = low_pen["target_users"] - low_pen["active_users"]

print(
    low_pen[["state","penetration_pct","uplift_users"]]
    .sort_values("uplift_users", ascending=False)
    .to_string(index=False)
)
print(f"\nTotal uplift potential: {low_pen['uplift_users'].sum():,} users")

# LookML View: creators
view: creators {
  sql_table_name: `analytics.dim_creators` ;;

  dimension: creator_id {
    type: string
    primary_key: yes
    sql: ${TABLE}.creator_id ;;
  }

  dimension: creator_name { type: string; sql: ${TABLE}.creator_name ;; }
  dimension: primary_category { type: string; sql: ${TABLE}.primary_category ;; }
  dimension: country { type: string; sql: ${TABLE}.country ;; }
  dimension: is_partner { type: yesno; sql: ${TABLE}.is_partner ;; }
}

# LookML View: video_performance (fact)
view: video_performance {
  sql_table_name: `analytics.fact_video_daily` ;;

  dimension_group: report {
    type: time
    timeframes: [date, week, month, quarter]
    sql: ${TABLE}.report_date ;;
  }

  measure: total_watch_hours {
    type: sum
    sql: ${TABLE}.watch_seconds / 3600.0 ;;
    value_format: "#,##0.0"
  }

  measure: total_revenue {
    type: sum
    sql: ${TABLE}.revenue_usd ;;
    value_format: "$#,##0.00"
  }

  measure: subscriber_net_gain {
    type: sum
    sql: ${TABLE}.subscribers_gained - ${TABLE}.subscribers_lost ;;
  }

  measure: revenue_per_watch_hour {
    type: number
    sql: ${total_revenue} / NULLIF(${total_watch_hours}, 0) ;;
    value_format: "$0.00"
  }
}

# Explore: joins creators to performance
explore: video_performance {
  label: "YouTube Creator Performance"
  join: creators {
    type: left_outer
    sql_on: ${video_performance.creator_id} = ${creators.creator_id} ;;
    relationship: many_to_one
  }
}

-- FACT TABLE (grain: keyword × ad_group × date)
-- BigQuery: PARTITION BY report_date, CLUSTER BY advertiser_id
CREATE TABLE fact_ads_performance (
    report_date          DATE,          -- partition key
    advertiser_id        STRING,        -- cluster key
    campaign_key         INT64,         -- FK → dim_campaign
    ad_group_key         INT64,         -- FK → dim_ad_group
    keyword_key          INT64,         -- FK → dim_keyword (SCD Type 2)
    geo_key              INT64,         -- FK → dim_geography
    device_type          STRING,        -- mobile | desktop | tablet
    impressions          INT64,
    clicks               INT64,
    conversions          INT64,
    cost_usd             FLOAT64,
    -- Derived metrics (pre-computed for Looker performance)
    ctr                  FLOAT64,       -- clicks / impressions
    cpc                  FLOAT64,       -- cost / clicks
    conversion_rate      FLOAT64        -- conversions / clicks
);

-- DIM: Campaign
CREATE TABLE dim_campaign (
    campaign_key    INT64,
    campaign_id     STRING,
    campaign_name   STRING,
    campaign_type   STRING,            -- search | display | shopping | video
    bidding_strategy STRING,
    daily_budget_usd FLOAT64
);

-- DIM: Keyword (SCD Type 2 — tracks bid and match type changes)
CREATE TABLE dim_keyword (
    keyword_key     INT64,             -- surrogate key
    keyword_id      STRING,            -- natural key
    keyword_text    STRING,
    match_type      STRING,            -- broad | phrase | exact
    bid_usd         FLOAT64,
    valid_from      DATE,
    valid_to        DATE,
    is_current      BOOL
);

-- DIM: Geography
CREATE TABLE dim_geography (
    geo_key         INT64,
    country         STRING,
    state           STRING,
    city            STRING,
    geo_tier        STRING             -- tier1 | tier2 | tier3
);

-- models/mart/fct_ads_daily.sql
{{
    config(
        materialized     = 'incremental',
        incremental_strategy = 'merge',
        unique_key       = ['report_date', 'advertiser_id', 'campaign_id', 'ad_id'],
        partition_by     = {'field': 'report_date', 'data_type': 'date'},
        cluster_by       = ['advertiser_id'],
        on_schema_change = 'sync_all_columns'
    )
}}

WITH raw_events AS (
    SELECT
        DATE(event_timestamp)     AS report_date,
        advertiser_id,
        campaign_id,
        ad_id,
        event_type,               -- 'click' | 'impression' | 'conversion'
        revenue_usd
    FROM {{ source('raw', 'ad_events') }}

    {% if is_incremental() %}
    -- Only process last 3 days to handle late-arriving events
    WHERE DATE(event_timestamp) >= DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY)
    {% endif %}
)
SELECT
    report_date,
    advertiser_id,
    campaign_id,
    ad_id,
    COUNTIF(event_type = 'impression')            AS impressions,
    COUNTIF(event_type = 'click')                 AS clicks,
    COUNTIF(event_type = 'conversion')            AS conversions,
    SUM(CASE WHEN event_type = 'click'
             THEN revenue_usd ELSE 0 END)         AS revenue_usd,
    SAFE_DIVIDE(
        COUNTIF(event_type = 'click'),
        NULLIF(COUNTIF(event_type = 'impression'), 0)
    )                                             AS ctr,
    CURRENT_TIMESTAMP()                           AS dbt_updated_at
FROM raw_events
GROUP BY report_date, advertiser_id, campaign_id, ad_id