Create Views, Insert, and Update Correctly
Company: CVS Health
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Online Assessment
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).
Overview: 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.
Create a 7-day recent_customer_spend_7d view
You are given three tables, `customers`, `products`, and `orders` (schema and sample data below).
Write a single `SELECT` query that reports, for each customer who placed at least one completed order in the 7-day window **from 2025-05-26 through 2025-06-01 (inclusive)**, their total spend over that window.
A customer's total spend is the sum of `qty * price_usd` (taken from the matching product) across only their orders that:
- have `status = 'completed'`, **and**
- have an `order_date` between 2025-05-26 and 2025-06-01 inclusive.
This is the defining query you would put inside `CREATE VIEW recent_customer_spend_7d AS ...`, but you only need to return the `SELECT`.
**Output:** one row per qualifying customer with columns `cust_id`, `name`, and `total_spend_usd` (the windowed total). **Sort the result by `cust_id` ascending.** Customers with no completed order in the window should not appear.
Tables
customers(cust_id INT, name VARCHAR(100), region VARCHAR(50))
products(product_id INT, name VARCHAR(100), price_usd DECIMAL(10,2))
orders(order_id INT, cust_id INT, product_id INT, qty INT, order_date DATE, status VARCHAR(20))
Hints
- Join orders to customers (for the name) and to products (for price_usd).
- Use status = 'completed' and order_date BETWEEN '2025-05-26' AND '2025-06-01' — BETWEEN is inclusive in Postgres.
Insert a new product and a new order
Using the same schema and initial data, write SQL to: (1) insert a new product with product_id = 13, name = 'WidgetD', price_usd = 35.00; and then (2) insert a new order with order_id = 1006 for customer 2 buying 3 units of product 13 on 2025-06-01 with status = 'pending'. Ensure the product insert happens before the order insert so foreign key constraints are satisfied.
Tables
customers(cust_id INT, name VARCHAR(100), region VARCHAR(50))
products(product_id INT, name VARCHAR(100), price_usd DECIMAL(10,2))
orders(order_id INT, cust_id INT, product_id INT, qty INT, order_date DATE, status VARCHAR(20))
Hints
- Use two separate INSERT statements, one for products and one for orders.
- Insert the product first so that the foreign key from orders.product_id to products.product_id is satisfied.
Update an order status to completed
Write a SQL statement to update order 1002 so that its status becomes 'completed'. Do not modify any other columns or rows.
Tables
customers(cust_id INT, name VARCHAR(100), region VARCHAR(50))
products(product_id INT, name VARCHAR(100), price_usd DECIMAL(10,2))
orders(order_id INT, cust_id INT, product_id INT, qty INT, order_date DATE, status VARCHAR(20))
Hints
- Use an UPDATE statement with a WHERE clause that matches order_id = 1002.
- Do not forget the WHERE clause; otherwise all orders would be updated.
Apply a price reduction and reason about view correctness
Apply a 10% price reduction to WidgetB (product_id = 11) in the products table. Then return the rows that the recent_customer_spend_7d view from Question 1 would show after that price change.
The live console expects one PostgreSQL statement, so use a data-modifying CTE for the update and feed the updated price into the SELECT. The view logic joins completed orders in the inclusive 2025-05-26 through 2025-06-01 window to products and computes SUM(qty * price_usd). Return columns cust_id, name, and total_spend_usd ordered by cust_id.
In your explanation, state whether the totals change and give one SQL-compatible schema or data-change approach that prevents historical spend from changing retroactively when product prices are updated.
Tables
customers(cust_id INT, name VARCHAR(100), region VARCHAR(50))
products(product_id INT, name VARCHAR(100), price_usd DECIMAL(10,2))
orders(order_id INT, cust_id INT, product_id INT, qty INT, order_date DATE, status VARCHAR(20))
Hints
- Use UPDATE ... RETURNING in a CTE to capture the changed WidgetB price.
- Build an effective product-price CTE that uses the returned price for product_id 11.