Create Views, Insert, and Update Correctly
Company: CVS Health
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: Medium
Interview Round: Take-home Project
Treat “today” as 2025-09-01. Use the schema and sample data below to write SQL for each part. Schema: customers(cust_id INT PRIMARY KEY, name TEXT, region TEXT); products(product_id INT PRIMARY KEY, name TEXT, price_usd DECIMAL(10,2)); orders(order_id INT PRIMARY KEY, cust_id INT, product_id INT, qty INT, order_date DATE, status TEXT, FOREIGN KEY (cust_id) REFERENCES customers(cust_id), FOREIGN KEY (product_id) REFERENCES products(product_id)). Sample tables:
customers
+---------+-------+--------+
| cust_id | name | region |
+---------+-------+--------+
| 1 | Alice | East |
| 2 | Bob | East |
| 3 | Chen | West |
+---------+-------+--------+
products
+------------+---------+-----------+
| product_id | name | price_usd |
+------------+---------+-----------+
| 10 | WidgetA | 20.00 |
| 11 | WidgetB | 50.00 |
| 12 | WidgetC | 100.00 |
+------------+---------+-----------+
orders
+----------+---------+------------+-----+------------+-----------+
| order_id | cust_id | product_id | qty | order_date | status |
+----------+---------+------------+-----+------------+-----------+
| 1001 | 1 | 10 | 2 | 2025-08-26 | completed |
| 1002 | 1 | 11 | 1 | 2025-08-29 | pending |
| 1003 | 2 | 12 | 1 | 2025-08-31 | completed |
| 1004 | 3 | 11 | 3 | 2025-09-01 | completed |
| 1005 | 2 | 10 | 5 | 2025-08-20 | completed |
+----------+---------+------------+-----+------------+-----------+
Tasks:
(a) Create a view recent_customer_spend_7d(cust_id, name, total_spend_usd) that shows, for each customer, the total spend in the last 7 days relative to today (i.e., from 2025-08-26 through 2025-09-01 inclusive), summing qty * price_usd for orders with status='completed'.
(b) Insert a new product (product_id=13, name='WidgetD', price_usd=35.00). Then insert a new order (order_id=1006) for customer 2 buying 3 units of product 13 on 2025-09-01 with status='pending'.
(c) Update order 1002 to status='completed'.
(d) Apply a 10% price reduction to WidgetB (product_id=11). After this update, explain whether the totals in your view from (a) will change and propose one SQL-compatible approach to avoid retroactively changing historical spend (no need to execute DDL, just describe precisely).
Quick Answer: This question evaluates SQL data-manipulation competencies including view creation, aggregate joins, INSERT/UPDATE operations, and reasoning about temporal effects on computed aggregates such as historical customer spend.