Create Views, Insert, and Update Correctly

Q: Create Views, Insert, and Update Correctly

This is a Data Manipulation (SQL/Python) interview question from CVS Health for Data Scientist roles. View the full question and solution on PracHub.

Q: How do I approach Data Manipulation (SQL/Python) interview questions?

Data Manipulation (SQL/Python) questions require understanding of core concepts and practice. PracHub provides solutions with explanations to help you master data manipulation (sql/python) interviews.

Question

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:

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).

Create Views, Insert, and Update Correctly

Comments (0)