← Boston Consulting Group Interview Insights

Boston Consulting Group·Data Scientist·Technical Phone Screen·Intermediate

Intermediate
Apr 2026

Summary

BCG data scientist interview, one big SQL question that looked manageable until I actually tried to write it out. More of a technical screen vibe than a full onsite, and the problem had enough moving parts to trip you up if you're not careful with window functions and date filtering.

Questions Asked (1)

Q1

Write a SQL query that computes, per car model, the total revenue for the current 7-day window and the prior 7-day window (completed orders only), identifies the top 3 customers by spend in the current window with their share of model revenue, and calculates week-over-week revenue growth. Ties in the top-3 ranking should be broken by the customer's earliest purchase date on that model across all history.

Data ModelingProduct Analytics & MetricsAlgorithms & Data Structures
Author's notes

This one took me longer to structure than I expected.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Break the problem into three parts: first compute per-model revenue for the current and prior 7-day windows using conditional aggregation on completed orders; second rank customers by spend in the current window per model, using a tie-breaker on earliest purchase date; third combine these to get top 3 customers with their revenue share and week-over-week growth. Use CTEs to keep the query modular and readable.

Pro tip: Clarify the definition of '7-day window' (e.g., rolling 7 days ending today vs. fixed calendar weeks) and confirm that 'completed orders' means status = 'completed'. Also, mention that you'd handle ties in the top-3 ranking using ROW_NUMBER() with a deterministic tie-breaker, and consider performance implications of window functions on large datasets.

1. Define windows and filter completed orders

Determine the date ranges for the current and prior 7-day windows (e.g., using CURRENT_DATE - INTERVAL '7 days'). Filter the orders table to only include completed orders.

2. Compute per-model revenue for both windows

Use conditional aggregation (SUM(CASE WHEN ...)) to calculate total revenue per car model for the current and prior windows in a single pass or via separate CTEs.

3. Rank customers by spend in current window

For each model, rank customers by their total spend in the current window. Use ROW_NUMBER() with ORDER BY spend DESC, earliest_purchase_date ASC to break ties.

4. Select top 3 customers and compute revenue share

Filter to the top 3 ranked customers per model, and calculate each customer's share as their spend divided by the model's total current-window revenue.

5. Calculate week-over-week growth and assemble final output

Compute growth as (current_revenue - prior_revenue) / prior_revenue. Join the revenue and top-customer results to produce the final per-model output.

Key Points to Mention

  • Use of CTEs for modularity and readability
  • Conditional aggregation for current vs. prior window revenue
  • Window functions (ROW_NUMBER) for ranking with tie-breaking
  • Handling of NULLs or zero revenue in growth calculation
  • Definition of 'completed orders' (e.g., status = 'completed')
  • Performance considerations: indexing on date and model, avoiding unnecessary subqueries

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.