← Capital One Interview Insights

Capital One·Data Scientist·Technical Phone Screen·Intermediate

Intermediate
Jul 2026

Summary

Capital One Data Scientist interview that was basically one long SQL problem covering campaign analytics, cost modeling, and donor targeting. The question was dense enough that I had to slow down and think through the schema carefully before writing anything.

Questions Asked (3)

Q1

Given a donors, campaigns, contacts, and donations schema, write SQL to compute per-campaign and per-segment metrics for August 2025: total reached, unique donors who donated, conversion rate, gross donations, variable cost, fixed cost, and net revenue (gross minus total cost).

Product Analytics & MetricsData Modeling
Author's notes

This took me longer than I expected to set up.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the schema and metric definitions, especially how 'reached' and 'segment' are defined. Then outline a SQL structure that aggregates donations per campaign and segment, computes each metric, and combines them into a final result set.

Pro tip: Explicitly state your assumptions about the schema and metric definitions before writing SQL, and use CTEs to break the query into logical steps for readability and maintainability.

1. Clarify schema and definitions

Confirm the tables and columns: donors, campaigns, contacts, donations. Define 'reached' (e.g., contacts linked to campaign) and 'segment' (e.g., donor demographic or contact attribute).

2. Identify relevant donations

Filter donations to August 2025 and join with campaigns and donors to get campaign and segment information.

3. Compute per-campaign and per-segment aggregates

Use GROUP BY campaign_id, segment to calculate total reached, unique donors, gross donations, variable cost, and fixed cost.

4. Calculate derived metrics

Compute conversion rate as unique donors divided by total reached, and net revenue as gross donations minus (variable cost + fixed cost).

5. Assemble final output

Combine all metrics into a single result set, ensuring proper handling of NULLs and division by zero.

Key Points to Mention

  • Use of CTEs or subqueries for step-by-step computation
  • Handling of division by zero in conversion rate
  • Definition of 'reached' and how it's derived from contacts
  • Aggregation of costs (variable and fixed) per campaign and segment
  • Filtering donations by date range (August 2025)
  • Ensuring unique donors are counted correctly (e.g., COUNT(DISTINCT donor_id))

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

Q2

After computing per-campaign metrics, write a single-row summary query that picks whichever campaign (gala vs. online) had the higher net revenue.

Product Analytics & Metrics
Author's notes

Pretty clean once you have the CTE from the first part.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, compute net revenue per campaign using a GROUP BY on campaign, then use a window function or ORDER BY with LIMIT to select the campaign with the highest net revenue. Alternatively, use a CASE expression to pivot the results into a single row with the winning campaign and its net revenue.

Pro tip: Mention that you would handle ties explicitly (e.g., using RANK() or a tie-breaker) and that you would validate the result against the underlying data to ensure accuracy.

1. Compute per-campaign net revenue

Use a subquery or CTE to calculate net revenue for each campaign by summing revenue minus costs, grouped by campaign.

2. Rank or order campaigns

Apply a window function like RANK() or ROW_NUMBER() ordered by net revenue descending, or simply ORDER BY net revenue DESC with LIMIT 1.

3. Select the top campaign

Filter to the top-ranked campaign (e.g., WHERE rank = 1) or use LIMIT 1 to get the campaign with the highest net revenue.

4. Format as a single row

If needed, pivot the result using conditional aggregation (e.g., MAX(CASE WHEN campaign = 'gala' THEN net_revenue END)) to return a single row with the winning campaign and its net revenue.

Key Points to Mention

  • Use of GROUP BY to aggregate net revenue per campaign
  • Window functions (RANK, ROW_NUMBER) or ORDER BY with LIMIT to identify the top campaign
  • Handling ties or multiple campaigns with the same net revenue
  • Ensuring the final output is a single row with the campaign name and net revenue
  • Consideration of NULLs or missing data in revenue/cost calculations
  • Performance implications of different approaches (e.g., subquery vs. window function)

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

Q3

Write a query to identify the top 10 prospective gala donors, ranked by total donations in the last 12 months, excluding anyone already contacted for the gala campaign.

Data ModelingProduct Analytics & Metrics
Author's notes

The NOT EXISTS / NOT IN subquery is the obvious move but I blanked on whether to use donations_hist or the main donations table.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the schema and business definitions (e.g., what constitutes a donation, how 'last 12 months' is defined, and how to identify contacted donors). Then write a SQL query that aggregates donations per donor, filters to the last 12 months, excludes contacted donors, and ranks by total donation amount to return the top 10.

Pro tip: Mention that you would validate the results by checking for edge cases like donors with no donations in the period or multiple contact records, and consider using a window function like RANK() or ROW_NUMBER() to handle ties appropriately.

1. Clarify Requirements and Schema

Ask about the tables involved (donations, donors, contacts), the definition of 'last 12 months' (e.g., relative to current date or a fixed date), and what 'contacted for the gala campaign' means (e.g., a flag or a separate table).

2. Aggregate Donations

Write a subquery or CTE that sums donation amounts per donor for the last 12 months, ensuring you filter by the correct date range.

3. Exclude Contacted Donors

Use a LEFT JOIN or NOT EXISTS to exclude donors who have been contacted for the gala campaign, being careful to handle NULLs correctly.

4. Rank and Limit

Order the results by total donation amount descending and limit to the top 10, using a window function if ties need special handling.

5. Validate and Optimize

Check for edge cases (e.g., donors with no donations, duplicate contacts) and consider indexing or query performance if the dataset is large.

Key Points to Mention

  • Use of CTEs or subqueries for readability and modularity
  • Correct date filtering for 'last 12 months' (e.g., DATEADD or INTERVAL)
  • Handling of ties in ranking (RANK vs. ROW_NUMBER vs. DENSE_RANK)
  • Efficient exclusion of contacted donors (NOT EXISTS vs. LEFT JOIN with NULL check)
  • Assumptions about data model (e.g., one row per donation, donor ID consistency)
  • Validation steps to ensure accuracy (e.g., checking counts, spot-checking results)

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