Sample and Simulate Price Adjustments in R with dplyr
Company: Google
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Products
+----+-----------+-------+
| id | product | price |
| 1 | phone | 500 |
| 2 | tablet | 300 |
| 3 | laptop | 1000 |
| 4 | headset | 80 |
| 5 | charger | 20 |
+----+-----------+-------+
Discounts
+----+----------+
| id | discount |
| 1 | 0.05 |
| 3 | 0.10 |
| 4 | 0.02 |
+----+----------+
##### Scenario
Data-wrangling and simulation tasks in R (dplyr) involving sampling, joins and price adjustments.
##### Question
Using dplyr, show how to randomly sample exactly 50% of a data frame. Perform a left join between a product table and a discount table on product id. Write a simulation that, for each run, keeps half the products at the same price, increases the rest by 10%, and returns the average simulated price.
##### Hints
slice_sample(), left_join(), mutate with runif() or sample() inside replicate().
Overview: This question evaluates proficiency in R data manipulation with dplyr, specifically sampling, left joins, conditional price transformations and running simple simulations to estimate average prices.
You are given two tables: products and discounts. First, write a query that randomly samples exactly 50% of the rows from the products table. Second, write a query that performs a left join between products and discounts on product id, returning all products along with any matching discount. Third, write a query that simulates one pricing scenario: for each run, randomly keep half of the products at their original price and increase the remaining products' prices by 10%. Return a single-row result containing the average simulated price across all products for that run. For the fixed sample output, make the simulation deterministic by treating even product ids as the unchanged half and odd product ids as the 10% increase half.
Tables
products(id INTEGER, product VARCHAR(50), price DECIMAL(10,2))
discounts(id INTEGER, discount DECIMAL(5,2))
Hints
- Use ORDER BY random() with a LIMIT based on 50% of the row count to sample products (in PostgreSQL).
- Use a LEFT JOIN from products to discounts on id to include all products and any matching discounts.