Implement R dplyr simulation and left join
Company: Google
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Using R and dplyr, run a simulation and a join.
Data:
prices
item_id | price_usd
1 | 10.00
2 | 20.00
3 | 30.00
4 | 40.00
catalog
item_id | category
1 | A
2 | B
3 | A
4 | C
Tasks:
- With set.seed(2025), perform 1,000 simulations. In each simulation: randomly select half of the rows in prices to keep the same price; increase the other half by 10%. Then left join to catalog and compute: (i) overall mean price, and (ii) mean price by category A/B/C.
- Return a data frame with one row per simulation containing overall_mean and category means. Also return the empirical mean and SD across simulations for each statistic.
- Constraints: use dplyr verbs (e.g., slice_sample, mutate, case_when, left_join, group_by, summarise). Avoid for-loops; use vectorized operations or map-style iteration while ensuring no accidental reuse of mutated state across iterations. Your solution must be memory-safe for 1e6 items (outline changes needed).
Overview: This question evaluates a candidate's proficiency with data manipulation and simulation in R using dplyr, covering randomized sampling, vectorized transformations, left joins, grouped aggregation, and considerations for memory-safe processing at scale.
Read the full Google Data Scientist interview experience this question came from
Compute per-simulation overall and category mean prices after adjustments
You are given item prices, a catalog that assigns each item to a category, and a table of simulated pricing scenarios. In each simulation, some items keep their original price and others have their price increased by 10%.
Tables:
- prices: current price of each item.
- catalog: category (A, B, or C) for each item.
- simulations: for each simulation_id and item_id, a flag keep_same indicating whether the price remains unchanged (keep_same = TRUE) or is increased by 10% (keep_same = FALSE).
Task:
Write an SQL query to compute, for each simulation_id:
1. The overall mean adjusted price across all items in that simulation.
2. The mean adjusted price for category A.
3. The mean adjusted price for category B.
4. The mean adjusted price for category C.
The adjusted price for an item in a given simulation is:
- price_usd if keep_same = TRUE
- price_usd * 1.10 if keep_same = FALSE
Return one row per simulation_id with the following columns:
- simulation_id
- overall_mean_price
- mean_price_cat_A
- mean_price_cat_B
- mean_price_cat_C
Tables
prices(item_id INT, price_usd DECIMAL(10,2))
catalog(item_id INT, category CHAR(1))
simulations(simulation_id INT, item_id INT, keep_same BOOLEAN)
Hints
- First compute the adjusted price per (simulation_id, item_id) in a CTE or subquery.
- Use conditional AVG with CASE expressions to compute category-specific means in a single grouped query.
Compute empirical mean and standard deviation of simulation statistics
Using the same tables (prices, catalog, simulations) and the definition of adjusted price from the previous question, first compute per-simulation statistics:
- overall_mean_price
- mean_price_cat_A
- mean_price_cat_B
- mean_price_cat_C
Then, across all simulations, compute for each of these four statistics:
1. The empirical mean (average across simulations).
2. The empirical population standard deviation across simulations.
Return one row per statistic with columns:
- stat_name (one of 'overall_mean_price', 'mean_price_cat_A', 'mean_price_cat_B', 'mean_price_cat_C')
- empirical_mean (rounded to 4 decimal places)
- empirical_sd (population standard deviation, rounded to 4 decimal places)
Use PostgreSQL numeric casts for rate calculations that are rounded to a fixed number of decimal places.
Tables
prices(item_id INT, price_usd DECIMAL(10,2))
catalog(item_id INT, category CHAR(1))
simulations(simulation_id INT, item_id INT, keep_same BOOLEAN)
Hints
- First reuse the per-simulation aggregates (e.g., as a CTE) to get one row per simulation with all four statistics.
- Unpivot the per-simulation statistics into name/value pairs (using UNION ALL), then GROUP BY stat_name and use AVG and STDDEV_POP, rounding the results.