A · Behavioural & Scenario Questions

Tell your story. Interviewers want to know how you think, communicate, and handle messy situations.

+
Behavioral
Tell me about a time you found a data issue that others missed.
Approach
  • Pick a real example (duplicate IDs, typos in category names, logic errors in aggregation)
  • Explain what made you notice it (validation check, outlier, business logic check, comparing reports)
  • Show the impact: "This would have cost us ₹50K in bad analysis"
Model Answer
At my last role at SouthBank India, I was building a churn model for the payment team. We were aggregating daily active users from a raw events table, and I noticed something off—the DAU numbers didn't match the SQL count I'd written. I dug in and found that a data pipeline was loading duplicate user_ids on Sundays due to a timezone bug. The DBA thought it was a reporting issue, but I traced it to the raw logs and found the bug was in the ETL, not the warehouse.

The fix prevented us from over-reporting DAU by 12%, which would have masked a real churn signal. I documented the issue, worked with ops to patch it, and added a uniqueness check to our validation suite. The lesson: always validate counts before you build on them. One bad row can derail a whole analysis.
Follow-up They Might Ask
How did you validate the duplicate check going forward? (Data quality tests, daily monitoring, PagerDuty alerts.)
+
Behavioral
Describe an analysis that changed a business decision.
Approach
  • Start with the business question, not the data
  • Show the analysis: "I cohorted users by signup date and looked at 30-day retention"
  • End with the decision: "We shifted budget from SMS to email because retention was 2x higher"
Jumia was spending ₹2M per month on SMS campaigns to re-engage inactive sellers. The ops team assumed SMS was the strongest channel, but nobody had measured it. I pulled 6 months of engagement data and ran a cohort analysis: tracked 10K sellers who received an SMS and compared their 7-day reactivation rate to 10K matched sellers who didn't. SMS drove 3% reactivation. Email was 8%. Push was 6%. The SMS cohort was also expensive—₹5 per message.

I presented the ROI: shift 60% of SMS spend to email, keep 20% on push, and we'd hit the same reactivation KPI at 40% lower cost. The VP ran a small test, it held, and we reallocated budget. Saved ₹800K annually.
Follow-up They Might Ask
How did you handle confounding variables (sellers who were already less engaged might ignore both SMS and email)?
+
Behavioral
You're asked for a number in 30 minutes. How do you balance speed vs rigor?
Approach
  • Ask clarifying questions first: "What decision does this number feed?"
  • State your assumptions out loud: "I'm using last 30 days, excluding returns"
  • Use a sanity check: "This is 5% of our total, which feels right"
Speed and rigor aren't opposites—they're managed by clarity. First, I ask what the number is for. If it's "rough sizing for a proposal," 80% confident is fine. If it's "board report," I need 95%.

In 30 minutes: I use a template. Load the data I know exists (no ETL waits), write a quick query with documented assumptions, spot-check the result against a known metric, and deliver with a one-line caveat: "This is Nov–Dec only; full year would need 2 more hours." Most stakeholders care more about knowing the limits than pretending they don't exist.

Red flag: if someone pressures me to skip the caveat. That's when I push back: "I can give you 80% in 30 min or 95% in 2 hours. Which matters more?"
Follow-up They Might Ask
Tell me about a time you had to deliver under time pressure. What went wrong?
+
Behavioral
Disagreement with a stakeholder about what the data says—how did you handle it?
Approach
  • Show you listened to their view first
  • Explain the data calmly: "Here's the filter, here's the calculation"
  • Offer to dig deeper if they spot a real issue
The CMO at a fintech was convinced that email open rates had tanked. I pulled the data and showed opens were up 15% YoY. She didn't believe me. Instead of doubling down, I asked why she felt they were down. She said, "Sales told me." I worked with the data eng team, walked her through the query, and showed her a pivot table: the drop she'd heard about was real, but only for one segment (finance services—I'd used a stricter open definition that quarter).

Once we aligned on definitions, the disagreement dissolved. The lesson: most data fights are really definition fights. We fixed the segment issue and she trusted the dashboard after that.
Follow-up They Might Ask
How do you prevent definition mismatches upstream?
+
Behavioral
Tell me about a time you had to explain something technical to a non-technical audience.
Approach
  • Pick a real metric or finding (cohort retention, A/B test result, churn trend)
  • Show the analogy: "Like tracking how many customers shop with us weekly vs once a year"
  • End with the business implication: "If churn rises, we lose ₹10M revenue"
I was explaining SQL window functions to the product team. I said: "Window functions let us calculate running totals without grouping up. Imagine standing in a checkout line—you want to know how many people are ahead of you in this line, and also in the next line. Window functions do that." Everyone got it immediately.

For non-technical audiences, I skip the jargon and use a familiar problem. Cohort retention? "How many of the 100 people who signed up in Jan are still using us in Mar?" That's it. No cohort language needed.
Follow-up They Might Ask
How would you explain why the same metric differs across two dashboards?
+
Behavioral
What's an analysis you're most proud of and why?
Approach
  • Pick something that combined technical depth + business impact
  • Show you learned something (methodology, communication, tooling)
  • End with the outcome: raised by 2x? Prevented churn? Saved time?"
I built a unit economics model for Safaricom that tied acquisition cost to customer lifetime value by segment. It wasn't flashy—just Python, Excel, and 200 lines of pandas—but it changed how we budgeted. Before, we spent the same to acquire everyone. After, we spent 3x more on high-LTV segments and cut low-LTV spend by 40%. ROI on marketing went from 2.1x to 3.8x in one quarter.

I'm proud of it because I had to ask real questions: How do we define a customer segment? When does someone count as acquired? What's our hold-out period for LTV? I worked with finance, ops, and marketing to get the definitions right, and the model stuck because everyone trusted it.
Follow-up They Might Ask
How did you handle edge cases in the model (churn, reactivation, seasonal effects)?

B · SQL Questions

Real queries you'll write on day one. Master window functions, joins, and aggregations.

+
SQL
Write SQL to find the top 3 customers by revenue in each region using window functions.
Approach
  • Sum revenue by customer and region
  • Rank customers within each region using ROW_NUMBER() or RANK()
  • Filter to rank <= 3
Window functions are your friend here. We want to rank within each region, so we partition by region and order by revenue descending.
SELECT region, customer_id, total_revenue, rank FROM ( SELECT region, customer_id, SUM(amount) as total_revenue, ROW_NUMBER() OVER (PARTITION BY region ORDER BY SUM(amount) DESC) as rank FROM orders GROUP BY region, customer_id ) ranked WHERE rank <= 3 ORDER BY region, rank;
Follow-up They Might Ask
What's the difference between ROW_NUMBER, RANK, and DENSE_RANK? (Ties: ROW_NUMBER skips, RANK ties, DENSE_RANK doesn't skip.)
+
SQL
Given a sessions table, compute 7-day rolling daily active users (DAU).
Approach
  • Find unique users per day
  • Use a date range window (7 days back from each day)
  • Count distinct users in that window
Rolling DAU shows trends better than daily spikes. We group by date, count distinct users, then apply a 7-day window.
SELECT date, COUNT(DISTINCT user_id) as daily_users, COUNT(DISTINCT user_id) OVER ( ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) as rolling_7day_users FROM ( SELECT DISTINCT DATE(session_start) as date, user_id FROM sessions ) daily GROUP BY date ORDER BY date;
Follow-up They Might Ask
How would you compute this on a large table with millions of sessions? (Pre-aggregate daily unique users first, then window.)
+
SQL
Detect duplicate orders using SELF JOIN or ROW_NUMBER.
Approach
  • Option 1: ROW_NUMBER, find where rank > 1
  • Option 2: Self-join on order fields, find pairs
  • Decide what "duplicate" means (same customer, amount, date within 5 min?)
ROW_NUMBER is cleaner. We define a duplicate as the same customer, amount, and timestamp within 5 minutes.
SELECT order_id, customer_id, amount, created_at, dup_flag FROM ( SELECT order_id, customer_id, amount, created_at, ROW_NUMBER() OVER ( PARTITION BY customer_id, amount, DATE_TRUNC('minute', created_at) ORDER BY created_at ) as dup_flag FROM orders ) ranked WHERE dup_flag > 1;
Follow-up They Might Ask
How would you handle refunds—remove duplicates or mark them for review?
+
SQL
Anti-join: find users who have NOT placed an order.
Approach
  • LEFT JOIN users to orders
  • Filter WHERE orders.id IS NULL
Anti-join is a left join where the right side is null. Useful for churn, inactive user analysis, etc.
SELECT u.user_id, u.email, u.signup_date FROM users u LEFT JOIN orders o ON u.user_id = o.user_id WHERE o.order_id IS NULL ORDER BY u.signup_date DESC;
Follow-up They Might Ask
How would you find users who signed up but haven't ordered in 90 days?
+
SQL
Pivot monthly revenue by category (wide format).
Approach
  • Aggregate revenue by month and category
  • Use CASE WHEN or PIVOT to rotate
  • Sum amounts by month and category
Pivot using conditional SUM or PostgreSQL CROSSTAB. Here's the CASE approach.
SELECT DATE_TRUNC('month', order_date)::DATE as month, SUM(CASE WHEN category = 'Electronics' THEN amount ELSE 0 END) as electronics, SUM(CASE WHEN category = 'Fashion' THEN amount ELSE 0 END) as fashion, SUM(CASE WHEN category = 'Books' THEN amount ELSE 0 END) as books, SUM(amount) as total FROM orders GROUP BY DATE_TRUNC('month', order_date) ORDER BY month;
Follow-up They Might Ask
How would you handle new categories added mid-year?
+
SQL
Explain the difference between WHERE and HAVING with an example.
Approach
  • WHERE filters rows before aggregation
  • HAVING filters groups after aggregation
  • Example: WHERE excludes rows, HAVING excludes groups
WHERE runs on raw data, HAVING runs on aggregated data. Example: "Find regions where at least 5 customers spent >₹50K each."
SELECT region, COUNT(DISTINCT customer_id) as num_big_spenders, SUM(amount) as total_revenue FROM orders WHERE amount > 50000 -- Filter rows first GROUP BY region HAVING COUNT(DISTINCT customer_id) >= 5 -- Filter groups second ORDER BY total_revenue DESC;
Follow-up They Might Ask
Could you move the HAVING condition to WHERE? Why or why not?
+
SQL
Write a query to calculate month-over-month growth rate.
Approach
  • Aggregate revenue by month
  • Use LAG to get previous month's value
  • Calculate (current - previous) / previous
LAG is the inverse of LEAD. Grab the prior month, then compute the percentage change.
SELECT month, revenue, LAG(revenue) OVER (ORDER BY month) as prev_month_revenue, ROUND( (revenue - LAG(revenue) OVER (ORDER BY month)) / LAG(revenue) OVER (ORDER BY month) * 100, 2 ) as growth_pct FROM ( SELECT DATE_TRUNC('month', order_date)::DATE as month, SUM(amount) as revenue FROM orders GROUP BY DATE_TRUNC('month', order_date) ) monthly ORDER BY month;
Follow-up They Might Ask
How would you handle the first month (NULL growth)?
+
SQL
You have a sales table and a refunds table with possible multiple refunds per sale—compute net revenue per order using an outer join and null-safe sum.
Approach
  • LEFT JOIN sales to aggregated refunds (sum per order_id)
  • Subtract refunds from sales amount
  • COALESCE refund to 0 for orders with no refunds
Multiple refunds per sale means we need to sum refunds by order_id first, then left join.
SELECT s.order_id, s.amount as gross_revenue, COALESCE(r.total_refunded, 0) as refunded_amount, s.amount - COALESCE(r.total_refunded, 0) as net_revenue FROM sales s LEFT JOIN ( SELECT order_id, SUM(amount) as total_refunded FROM refunds GROUP BY order_id ) r ON s.order_id = r.order_id ORDER BY s.order_id;
Follow-up They Might Ask
What if a refund is greater than the sale amount (error case)?

C · Pandas & Python Questions

Manipulate data in code. GroupBy, merge, apply, pipe—know them all.

+
Pandas
How would you find rows where a column has >X% missing and recommend whether to drop or impute?
Approach
  • Calculate missing % per column
  • Filter to >X%
  • Decide: drop if >50% null, impute if <10%, investigate if in between
Missing data decisions depend on the % and the column type.
import pandas as pd df = pd.read_csv('data.csv') # Calculate missing % per column missing_pct = (df.isnull().sum() / len(df) * 100).sort_values(ascending=False) # Categorize drop_cols = missing_pct[missing_pct > 50].index.tolist() impute_cols = missing_pct[(missing_pct > 0) & (missing_pct <= 10)].index.tolist() investigate_cols = missing_pct[(missing_pct > 10) & (missing_pct <= 50)].index.tolist() print(f"Drop: {drop_cols}") print(f"Impute: {impute_cols}") print(f"Investigate: {investigate_cols}") # Then drop and impute df = df.drop(columns=drop_cols) df[impute_cols] = df[impute_cols].fillna(df[impute_cols].median())
Follow-up They Might Ask
How would you choose between mean imputation and forward-fill for time-series data?
+
Pandas
Write pandas for: "top 3 products per category by sales" using groupby and nlargest.
Approach
  • Group by category
  • For each group, get top 3 by sales using nlargest
GroupBy + nlargest is cleaner than sorting and slicing.
import pandas as pd df = pd.read_csv('sales.csv') # Aggregate sales by product and category product_sales = df.groupby(['category', 'product'])['amount'].sum().reset_index() # Get top 3 per category top_3 = product_sales.groupby('category').apply( lambda x: x.nlargest(3, 'amount') ).reset_index(drop=True) print(top_3)
Follow-up They Might Ask
How would you tie-break if two products have the same sales amount?
+
Pandas
What's the difference between .apply(), .transform(), and .agg()? When to use each.
Approach
  • .apply(): row-wise or column-wise custom function
  • .transform(): return same shape as input (for group operations)
  • .agg(): reduce to scalar (sum, mean, count)
They're different because they preserve different shapes and are optimized for different jobs.
# apply: custom function, any shape df['scaled'] = df['amount'].apply(lambda x: x / 1000) # transform: returns same length as input (great for group operations) df['pct_of_group'] = df.groupby('category')['amount'].transform( lambda x: x / x.sum() * 100 ) # agg: reduces to a scalar category_totals = df.groupby('category')['amount'].agg(['sum', 'mean', 'count'])
Follow-up They Might Ask
Why is transform faster than apply for grouped operations?
+
Pandas
How do you detect outliers in a financial column? Walk through IQR vs z-score vs business rule.
Approach
  • IQR: simple, robust to skew, domain-agnostic
  • Z-score: assumes normal distribution, sensitive to skew
  • Business rule: "transactions >10M are manual review" — best if you know the domain
I'd use IQR for most cases, then validate against business rules.
import pandas as pd import numpy as np df = pd.read_csv('transactions.csv') # IQR method Q1 = df['amount'].quantile(0.25) Q3 = df['amount'].quantile(0.75) IQR = Q3 - Q1 outliers_iqr = df[(df['amount'] < Q1 - 1.5*IQR) | (df['amount'] > Q3 + 1.5*IQR)] # Z-score method z_scores = np.abs((df['amount'] - df['amount'].mean()) / df['amount'].std()) outliers_zscore = df[z_scores > 3] # Business rule outliers_business = df[df['amount'] > 10000000] print(f"IQR outliers: {len(outliers_iqr)}") print(f"Z-score outliers: {len(outliers_zscore)}") print(f"Business rule outliers: {len(outliers_business)}")
Follow-up They Might Ask
How would you handle seasonal variations in outlier detection (e.g., peak month anomalies)?
+
Pandas
Merge / join: inner vs left vs outer—when does each apply?
Approach
  • Inner join: keep only matches (e.g., customers with orders)
  • Left join: keep all left rows, fill right with NaN if no match (most common)
  • Outer join: keep all rows from both, fill missing with NaN
Left join is most common because you usually want to preserve all rows from your primary table.
import pandas as pd customers = pd.read_csv('customers.csv') orders = pd.read_csv('orders.csv') # Inner: only customers with orders inner = customers.merge(orders, on='customer_id', how='inner') # Left: all customers, orders if available left = customers.merge(orders, on='customer_id', how='left') # Outer: all customers and all orders outer = customers.merge(orders, on='customer_id', how='outer') # Example: find customers with no orders no_orders = left[left['order_id'].isna()]
Follow-up They Might Ask
How would you join on different column names (customer_id vs cust_id)?
+
Pandas
Compute rolling 7-day average with pandas.
Approach
  • Sort by date first
  • Use .rolling(7).mean()
  • Set date as index if needed
Rolling is simpler in pandas than SQL once you have time-indexed data.
import pandas as pd df = pd.read_csv('daily_sales.csv') df['date'] = pd.to_datetime(df['date']) df = df.sort_values('date') # Rolling 7-day average df['rolling_7d_avg'] = df['sales'].rolling(window=7, min_periods=1).mean() # With date index (optional, helps with resampling) df.set_index('date', inplace=True) df['rolling_7d_avg'] = df['sales'].rolling('7d').mean() print(df)
Follow-up They Might Ask
What's the difference between .rolling().mean() and .resample('7d').mean()?
+
Pandas
Explain what .pipe() does and why it matters.
Approach
  • .pipe() chains custom functions on DataFrames
  • Makes code readable: each function is one transformation
  • Alternative to nesting function calls
Pipe is about readability and composability. Instead of nesting, you chain.
import pandas as pd def clean_names(df): df.columns = df.columns.str.lower().str.replace(' ', '_') return df def remove_nulls(df): return df.dropna() def scale_amounts(df): df['amount'] = df['amount'] / 1000 return df # Without pipe: hard to read df = scale_amounts(remove_nulls(clean_names(df))) # With pipe: clear flow df = (df .pipe(clean_names) .pipe(remove_nulls) .pipe(scale_amounts)) print(df)
Follow-up They Might Ask
How would you pass arguments to a piped function?
+
Pandas
You have a dataset too big for memory. How do you analyze it? (chunked read, dtype tricks, polars/duckdb, aggregation-first)
Approach
  • Read in chunks with pd.read_csv(..., chunksize=50000)
  • Reduce dtype size (int32 vs int64, category for strings)
  • Use polars or duckdb for larger datasets
  • Aggregate first (group, sum), then analyze
The strategy depends on what analysis you need. If it's aggregation, chunking + agg works. If it's complex, use duckdb.
import pandas as pd # Option 1: Chunked read + aggregate totals = {} for chunk in pd.read_csv('huge_file.csv', chunksize=50000): chunk['date'] = pd.to_datetime(chunk['date']) chunk['amount'] = chunk['amount'].astype('float32') group_totals = chunk.groupby('customer_id')['amount'].sum() for cid, total in group_totals.items(): totals[cid] = totals.get(cid, 0) + total # Option 2: Use duckdb (SQL on large files) import duckdb result = duckdb.query("SELECT customer_id, SUM(amount) FROM 'huge_file.csv' GROUP BY customer_id").to_df() # Option 3: Use polars (faster than pandas) import polars as pl df = pl.scan_csv('huge_file.csv').select([ 'customer_id', pl.col('amount').sum() ]).collect()
Follow-up They Might Ask
How would you estimate how much RAM you need before loading?

D · Statistics & A/B Testing

Hypothesis testing, significance, and causal inference. Know your p-values.

+
Stats
What is a p-value in plain English?
Approach
  • p-value = probability of seeing this result if the null hypothesis is true
  • Low p-value (p<0.05) = result is unlikely under the null, so we reject it
  • It's NOT the probability the hypothesis is true
A p-value is the probability of observing your data (or more extreme) if the null hypothesis is actually true. If the p-value is 0.03, there's a 3% chance you'd see this result by pure randomness. That's low, so we say the result is "statistically significant."

Common mistake: "p=0.05 means 95% chance the treatment works." Wrong. It means "5% chance I'd see this result if the treatment actually doesn't work."
Follow-up They Might Ask
What's the difference between statistical significance and practical significance?
+
Stats
You ran an A/B test: B converts at 10%, A at 9%. n=1000 each. Significant?
Approach
  • Use a chi-square test or proportions test
  • Calculate z-score: (10% - 9%) / SE
  • With n=1000, SE is small, so even 1% difference might be significant
Let me calculate. With n=1000 per arm, the standard error for a proportion is sqrt(p*(1-p)/n). For A at 9%, SE is about 0.009. The z-score is (0.10 - 0.09) / 0.0095 ≈ 1.05. That gives a p-value around 0.30, so it's NOT significant. The 1% difference could easily be noise.

Key insight: sample size matters. With n=1000, you need at least 1-2% difference to be significant. With n=100K, even 0.5% would be significant (but might not matter in practice).
from scipy.stats import chi2_contingency # Contingency table a_conversions = 90 # 9% of 1000 b_conversions = 100 # 10% of 1000 a_non = 910 b_non = 900 table = [[a_conversions, a_non], [b_conversions, b_non]] chi2, p_value, dof, expected = chi2_contingency(table) print(f"Chi-square: {chi2:.3f}, p-value: {p_value:.3f}") # Output: p-value ≈ 0.30 (not significant)
Follow-up They Might Ask
How would you choose sample size before the test starts?
+
Stats
Explain Type I vs Type II error.
Approach
  • Type I (false positive): reject null when it's true. Rate = alpha (usually 0.05)
  • Type II (false negative): fail to reject null when it's false. Rate = beta (usually 0.20)
  • Power = 1 - beta (ability to detect true effect)
Type I is a false alarm, Type II is a miss. In business: Type I kills budget on a failing feature, Type II wastes opportunity cost by not rolling out a winner. Most teams accept 5% Type I risk and 20% Type II risk (80% power).
Follow-up They Might Ask
How do you reduce both errors? (Larger sample size.)
+
Stats
What's the difference between correlation and causation? How do you establish causation in business data?
Approach
  • Correlation: variables move together (ice cream sales and drowning deaths are correlated—both increase in summer)
  • Causation: one causes the other
  • In observational data: use randomized tests, instrumental variables, or matching (cohorts)
Correlation doesn't imply causation, but causation implies correlation. In business, we establish causation through A/B tests (best), or through careful analysis of observational data (propensity score matching, cohort analysis, diff-in-diff). You can never be 100% sure with observational data, but you can reduce confounding.
Follow-up They Might Ask
Give me an example of confounding in e-commerce data.
+
Stats
Your A/B test has a leaky metric—conversions are up, but revenue per user is flat. What's happening?
Approach
  • Leaky metric: metric includes rows not fully exposed to the treatment
  • Conversion up, revenue flat = you're converting cheaper customers or there's selection bias
  • Check: are converters lower-tier users? Did a bot convert? Is the metric double-counted?
This usually means one of three things: (1) lower-quality conversions (bots, fraudsters), (2) metric definition mismatch (conversions counted before payment, revenue after refund), or (3) selection bias (different user cohorts between arms). I'd drill into the converters to see if they're lower-value customers, check for bot activity, and validate the metric definitions align.
Follow-up They Might Ask
How would you set up alerts to catch leaky metrics in the future?
+
Stats
How do you choose sample size for a test?
Approach
  • You need: baseline conversion rate, MDE (minimum detectable effect), alpha (0.05), power (0.80)
  • Formula: n = (z_alpha + z_beta)^2 * (p(1-p) + p'(1-p')) / (p - p')^2
  • Use a power calculator tool; don't calculate by hand
Sample size depends on four things: your baseline conversion rate, the MDE you care about, and your tolerance for false positives and false negatives. If baseline is 5% and you want to detect a 20% lift (to 6%), with 80% power and 95% confidence, you need about 10K users per arm. If you only care about detecting a 50% lift, you need ~2K.

I always use Evan Miller's calculator or Python's statsmodels.stats.proportion_power. Hand math is error-prone.
from statsmodels.stats.power import tt_solve_power # Detect a 10% effect size with 80% power, 5% significance power_result = tt_solve_power( effect_size=0.1, nobs=None, # Solve for sample size alpha=0.05, power=0.80, alternative='two-sided' ) print(f"Sample size per arm: {int(power_result)}") # ~393
Follow-up They Might Ask
What do you do if you can't wait for the full sample size?

E · Analytics & Product Questions

Metrics, funnels, cohorts, churn. The day-to-day work.

+
Analytics
Define and compute monthly active users (MAU).
Approach
  • MAU = count distinct users with at least one event in the calendar month
  • Be clear on what "active" means (login, view, purchase, session?)
  • Compare to DAU and WAU to see seasonality
MAU is just the count of unique users who did something (logged in, viewed content, placed an order—depends on your definition) in a calendar month. It's a lagging indicator: you report March MAU on April 1st. Useful for cohort-level trends but less useful for day-to-day decision making (DAU is better for that).
SELECT DATE_TRUNC('month', event_date)::DATE as month, COUNT(DISTINCT user_id) as mau FROM events WHERE event_type IN ('login', 'view', 'purchase') GROUP BY DATE_TRUNC('month', event_date) ORDER BY month DESC;
Follow-up They Might Ask
How would you compute MAU for only paid users?
+
Analytics
Walk through a cohort retention analysis.
Approach
  • Cohort = users grouped by signup month (or day, or product version)
  • Retention = % of cohort still active N days/months later
  • Table format: rows = cohorts, columns = weeks since signup
A cohort retention analysis shows how long users stick around based on when they signed up. You group users by signup cohort (e.g., Jan 2025 signups), then track how many are still active 1 week, 2 weeks, 1 month, 3 months later. This reveals if newer cohorts are stickier than older ones, and it's a leading indicator of LTV changes. If Jan cohort had 60% week-1 retention and Feb has 65%, you've improved the onboarding.
SELECT cohort_date, week_number, COUNT(DISTINCT user_id) as users, COUNT(DISTINCT CASE WHEN active THEN user_id END) as active_users, ROUND(COUNT(DISTINCT CASE WHEN active THEN user_id END) * 100.0 / COUNT(DISTINCT user_id), 1) as retention_pct FROM ( SELECT user_id, DATE_TRUNC('week', MIN(event_date))::DATE as cohort_date, DATE_TRUNC('week', event_date)::DATE as event_week, (DATE_TRUNC('week', event_date)::DATE - DATE_TRUNC('week', MIN(event_date))::DATE) / 7 as week_number, COUNT(*) > 0 as active FROM events GROUP BY user_id, DATE_TRUNC('week', event_date) ) cohorts GROUP BY cohort_date, week_number ORDER BY cohort_date, week_number;
Follow-up They Might Ask
How would you visualize this as a heatmap?
+
Analytics
What's RFM segmentation and when do you use it?
Approach
  • RFM = Recency, Frequency, Monetary
  • Score each dimension 1-5, then combine for a segment
  • Use when you want to target high-value at-risk customers or dormant VIPs
RFM is a quick customer segmentation. For each user, you calculate: how recent was their last purchase (Recency), how often do they buy (Frequency), and how much have they spent (Monetary). Then you score each 1-5 (5 is best). A customer with R=5, F=5, M=3 is a frequent, recent buyer but not your highest spender—worth nurturing but maybe not your VIP. RFM is useful when you need fast segments for email campaigns or re-engagement tests. It's not perfect (ignores product mix, margins), but it's simple.
SELECT user_id, MAX(order_date) as last_order_date, COUNT(*) as purchase_count, SUM(amount) as lifetime_value, -- R: days since last order (lower is better, so reverse score) CASE WHEN MAX(order_date) >= CURRENT_DATE - INTERVAL '30 days' THEN 5 WHEN MAX(order_date) >= CURRENT_DATE - INTERVAL '60 days' THEN 4 WHEN MAX(order_date) >= CURRENT_DATE - INTERVAL '180 days' THEN 3 WHEN MAX(order_date) >= CURRENT_DATE - INTERVAL '365 days' THEN 2 ELSE 1 END as r_score, -- F: frequency (higher is better) CASE WHEN COUNT(*) >= 20 THEN 5 WHEN COUNT(*) >= 10 THEN 4 WHEN COUNT(*) >= 5 THEN 3 WHEN COUNT(*) >= 2 THEN 2 ELSE 1 END as f_score, -- M: monetary (higher is better) CASE WHEN SUM(amount) >= 100000 THEN 5 WHEN SUM(amount) >= 50000 THEN 4 WHEN SUM(amount) >= 20000 THEN 3 WHEN SUM(amount) >= 5000 THEN 2 ELSE 1 END as m_score FROM orders GROUP BY user_id;
Follow-up They Might Ask
How would you combine R, F, M into a single segment (high, mid, low value)?
+
Analytics
How would you diagnose a 15% drop in daily signups?
Approach
  • Timeline: when exactly did it drop? (One day or gradual?)
  • Scope: all channels or just one? (Google, organic, paid?)
  • Technical: did something break? (Sign-up form, email, payment gateway?)
  • External: did something change? (Marketing paused? Competitor promo? News?)
A 15% drop is material. I'd start with a timeline: was it sudden (suggest a technical break) or gradual (suggest a marketing or seasonal issue)? Then I'd break it down by channel: is Google organic down, or is it just paid? If it's all channels, it's likely a product issue (broken sign-up flow, slow site, bad email verification). If it's one channel, it's external (keyword bid reduction, iOS update). Check Mixpanel or GA for funnel dropoff, check Slack for deploy logs, and ask the marketing team if they changed campaigns.
Follow-up They Might Ask
You traced it to a broken email verification link—how do you quantify the impact and communicate it to leadership?
+
Analytics
Churn rate vs retention rate—definitions, when to use which.
Approach
  • Churn = % who left (month-over-month, period-over-period)
  • Retention = % who stayed (inverse of churn, but often calculated differently)
  • Use churn for trend monitoring, retention for cohort analysis
Churn and retention are inverses in simple cases, but they're calculated differently in practice. Churn rate (month-over-month) = users who were active in month N but not in month N+1 / users in month N. Retention rate (cohort-based) = users active N months after signup / users who signed up in that cohort. Churn is for monitoring trends ("our churn is 8% MoM"), retention is for predicting LTV ("Jan cohort has 50% month-1 retention"). I use churn for dashboards, retention for strategic decisions.
Follow-up They Might Ask
How do you handle trial-to-paid churn vs paid-to-churned churn differently?
+
Analytics
A CEO asks "how's the business doing?" — what 5 metrics do you show?
Approach
  • Pick metrics that ladder up to a business outcome (revenue, growth, retention)
  • Mix leading and lagging indicators
  • Show vs. plan (% to plan, growth rate)
Depends on the business, but I'd show: (1) ARR or GMV (top-line, lagging), (2) MRR growth % or YoY growth (momentum), (3) churn % or retention (health), (4) CAC and LTV ratio (efficiency), (5) payback period or cash flow (sustainability). For a marketplace, I'd swap CAC for take rate or seller onboarding rate. The point is to tell a complete story: we're growing (metrics 1-2), retention is stable (metric 3), and we're spending efficiently (metric 4). No single metric is enough.
Follow-up They Might Ask
What would change if the CEO's goal is to maximize profitability instead of growth?

F · Business Sense & Case Studies

Real problems from real companies. Show you think like a product person.

+
Case Study
You're Safaricom: prepaid ARPU is falling 3% MoM. How do you investigate?
Approach
  • Segment the drop: is it active users (churn), or users spending less (lower usage)?
  • Timeline: started when? (Competitor promo? Price change? Seasonal?)
  • Cohort: all segments or just one? (Youth pre-paid vs elderly?)
A 3% MoM decline in prepaid ARPU is concerning—it compounds to 30% annual. I'd first break it down: Is it fewer active users, or users using less? Query the last 6 months of prepaid subscriber counts and ARPU separately. If count is down, it's churn (why are they leaving?). If count is flat but ARPU is down, it's lower usage (price sensitivity, competitor promo, network issues?).

Then I'd segment: is it all age groups or just the 18-25 segment? All regions or just Nairobi? All plans or just the ₹99 tier? Once I localize the drop, I can investigate causation: did we raise prices? Did Airtel launch a competitor plan? Did network quality drop? This tells me if it's fixable (product issue) or structural (market shift).
Follow-up They Might Ask
You found it's the 18-25 segment spending less. What do you recommend?
+
Case Study
You're Jumia: Nairobi return rate is 22% vs 12% for Mombasa. What do you do?
Approach
  • Hypothesis: logistics (longer delivery = more damage), product quality, customer expectations?
  • Segment further: return rate by category (fashion vs electronics?), seller, order value?
  • Interview: talk to CS, logistics teams
A 22% vs 12% gap is huge. First, I'd rule out data errors: same return definition across cities? Then I'd drill into product mix: is Nairobi ordering more fashion (higher return rate) or more electronics? Is it the same sellers in both cities? Next, I'd look at fulfillment speed and damage rates. Nairobi has more demand, maybe logistics is slower or packages are damaged more in transit. I'd also check order value: if Nairobi customers place smaller orders, they might have higher return intent (lower-intent buyers).

Finally, I'd talk to the ops team: are Nairobi delivery partners different? Are they trained less? Once I identify the root cause (product mix, delivery speed, seller quality), I can fix it. If it's a Nairobi logistics issue, I'd invest in better fulfillment. If it's product mix, I'd guide sellers to adjust SKUs.
Follow-up They Might Ask
You found the return reason: "arrived damaged". How do you measure the business impact and prioritize a fix?
+
Case Study
Your CMO says "conversion is down". Where do you start?
Approach
  • Clarify: is it email, web, app, or all? Which cohort? Which timeframe?
  • Pull the trend: graph it from 90 days back to see when and where it dropped
  • Segment by traffic source, device, OS version, geography
"Conversion is down" is too vague. I'd ask: conversion where (sign-up, purchase, trial-to-paid)? On what (web, app, email)? When did it start? Then I'd pull a trend line for the last 90 days—sudden drop (indicate a bug or campaign pause) or gradual (seasonal or competitive pressure)? Then segment: is web down but app up? Is mobile down but desktop up? Is USA down but EU fine? Once I find the specific segment with the decline, I can investigate that segment's recent changes.
Follow-up They Might Ask
You found it's iOS app sign-ups only. What changed recently?
+
Case Study
Design a dashboard for a fintech's head of operations.
Approach
  • Ops cares about: volume, latency, errors, SLA uptime
  • Show: daily transaction count, avg processing time, error rate, on-time settlement %
  • Segment by payment method (UPI, card, wallet), merchant tier, geography
  • Alerts for when error rate >1% or latency >500ms
An ops head needs real-time visibility into system health. I'd build a dashboard with: (1) today's transaction count vs yesterday and plan, (2) error rate % (red if >1%), (3) avg settlement latency (green if <200ms), (4) SLA uptime % (should be >99.9%), (5) breakdown by payment method (UPI, cards, wallets—critical to know if one method is broken), and (6) trend lines for the last 7 days. I'd also add alerts: if error rate spikes or latency exceeds threshold, ops gets paged. Ops doesn't care about retention or cohorts; they care about reliability and speed. Keep it simple and real-time.
Follow-up They Might Ask
How would you surface data quality issues (duplicate transactions, failed refunds) on this dashboard?
Africa & India's Best AI Academy
🤖 AI-first 🎓 Placement Assistance

Ready for your interview?

Join the Best AI Program: The AI-Era Analyst — 5 months, fully online, with placement assistance. Week 1 is free.

Explore The AI-Era Analyst →