Create Views, Insert, and Update Correctly

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

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.

Table customers

column	type	constraints
cust_id	INT	PRIMARY KEY
name	VARCHAR(100)	NOT NULL
region	VARCHAR(50)	NOT NULL

Sample data:

cust_id	name	region
1	Alice	East
2	Bob	East
3	Chen	West

Table products

column	type	constraints
product_id	INT	PRIMARY KEY
name	VARCHAR(100)	NOT NULL
price_usd	DECIMAL(10,2)	NOT NULL

Sample data:

name	price_usd	product_id
WidgetA	20	10
WidgetB	50	11
WidgetC	100	12

Table orders

column	type	constraints
order_id	INT	PRIMARY KEY
cust_id	INT	NOT NULL, FOREIGN KEY REFERENCES customers(cust_id)
product_id	INT	NOT NULL, FOREIGN KEY REFERENCES products(product_id)
qty	INT	NOT NULL
order_date	DATE	NOT NULL
status	VARCHAR(20)	NOT NULL

Sample data:

cust_id	order_date	order_id	product_id	qty	status
1	2025-05-26	1001	10	2	completed
1	2025-05-29	1002	11	1	pending
2	2025-05-31	1003	12	1	completed
3	2025-06-01	1004	11	3	completed
2	2025-05-20	1005	10	5	completed

Expected output

One row per customer with a completed order in 2025-05-26..2025-06-01, giving the windowed sum of qty*price_usd, ordered by cust_id.

cust_id	name	total_spend_usd
1	Alice	40.00
2	Bob	100.00
3	Chen	150.00

Hints

1. Join orders to customers (for the name) and to products (for price_usd).
2. Use status = 'completed' and order_date BETWEEN '2025-05-26' AND '2025-06-01' — BETWEEN is inclusive in Postgres.
3. GROUP BY cust_id, name and SUM(qty * price_usd); add ORDER BY cust_id.

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.

Table customers

column	type	constraints
cust_id	INT	PRIMARY KEY
name	VARCHAR(100)	NOT NULL
region	VARCHAR(50)	NOT NULL

Sample data:

cust_id	name	region
1	Alice	East
2	Bob	East
3	Chen	West

Table products

column	type	constraints
product_id	INT	PRIMARY KEY
name	VARCHAR(100)	NOT NULL
price_usd	DECIMAL(10,2)	NOT NULL

Sample data:

name	price_usd	product_id
WidgetA	20	10
WidgetB	50	11
WidgetC	100	12

Table orders

column	type	constraints
order_id	INT	PRIMARY KEY
cust_id	INT	NOT NULL, FOREIGN KEY REFERENCES customers(cust_id)
product_id	INT	NOT NULL, FOREIGN KEY REFERENCES products(product_id)
qty	INT	NOT NULL
order_date	DATE	NOT NULL
status	VARCHAR(20)	NOT NULL

Sample data:

cust_id	order_date	order_id	product_id	qty	status
1	2025-05-26	1001	10	2	completed
1	2025-05-29	1002	11	1	pending
2	2025-05-31	1003	12	1	completed
3	2025-06-01	1004	11	3	completed
2	2025-05-20	1005	10	5	completed

Expected output

Both INSERT statements should succeed. After execution, products will contain a new row (13, 'WidgetD', 35.00) and orders will contain a new row (1006, 2, 13, 3, '2025-06-01', 'pending'). The INSERT commands themselves do not return any result rows.

Hints

1. Use two separate INSERT statements, one for products and one for orders.
2. 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.

Table customers

column	type	constraints
cust_id	INT	PRIMARY KEY
name	VARCHAR(100)	NOT NULL
region	VARCHAR(50)	NOT NULL

Sample data:

cust_id	name	region
1	Alice	East
2	Bob	East
3	Chen	West

Table products

column	type	constraints
product_id	INT	PRIMARY KEY
name	VARCHAR(100)	NOT NULL
price_usd	DECIMAL(10,2)	NOT NULL

Sample data:

name	price_usd	product_id
WidgetA	20	10
WidgetB	50	11
WidgetC	100	12

Table orders

column	type	constraints
order_id	INT	PRIMARY KEY
cust_id	INT	NOT NULL, FOREIGN KEY REFERENCES customers(cust_id)
product_id	INT	NOT NULL, FOREIGN KEY REFERENCES products(product_id)
qty	INT	NOT NULL
order_date	DATE	NOT NULL
status	VARCHAR(20)	NOT NULL

Sample data:

cust_id	order_date	order_id	product_id	qty	status
1	2025-05-26	1001	10	2	completed
1	2025-05-29	1002	11	1	pending
2	2025-05-31	1003	12	1	completed
3	2025-06-01	1004	11	3	completed
2	2025-05-20	1005	10	5	completed

Expected output

The UPDATE should affect exactly one row: order_id = 1002. After execution, that row's status will be 'completed' instead of 'pending'. The UPDATE command itself does not return any result rows.

Hints

1. Use an UPDATE statement with a WHERE clause that matches order_id = 1002.
2. 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.

Table customers

column	type	constraints
cust_id	INT	PRIMARY KEY
name	VARCHAR(100)	NOT NULL
region	VARCHAR(50)	NOT NULL

Sample data:

cust_id	name	region
1	Alice	East
2	Bob	East
3	Chen	West

Table products

column	type	constraints
product_id	INT	PRIMARY KEY
name	VARCHAR(100)	NOT NULL
price_usd	DECIMAL(10,2)	NOT NULL

Sample data:

name	price_usd	product_id
WidgetA	20	10
WidgetB	50	11
WidgetC	100	12

Table orders

column	type	constraints
order_id	INT	PRIMARY KEY
cust_id	INT	NOT NULL, FOREIGN KEY REFERENCES customers(cust_id)
product_id	INT	NOT NULL, FOREIGN KEY REFERENCES products(product_id)
qty	INT	NOT NULL
order_date	DATE	NOT NULL
status	VARCHAR(20)	NOT NULL

Sample data:

cust_id	order_date	order_id	product_id	qty	status
1	2025-05-26	1001	10	2	completed
1	2025-05-29	1002	11	1	pending
2	2025-05-31	1003	12	1	completed
3	2025-06-01	1004	11	3	completed
2	2025-05-20	1005	10	5	completed

Expected output

After reducing WidgetB from 50.00 to 45.00, querying the view logic again recalculates historical completed-order spend using the current products.price_usd. Chen's 3 units of WidgetB now total 135.00 instead of 150.00; Alice and Bob are unchanged.

cust_id	name	total_spend_usd
1	Alice	40
2	Bob	100
3	Chen	135

Hints

1. Use UPDATE ... RETURNING in a CTE to capture the changed WidgetB price.
2. Build an effective product-price CTE that uses the returned price for product_id 11.
3. Historical reporting should usually use a stored transaction price, not a mutable product dimension price.