E-commerce Schema Design and Querying

This task requires you to design and interact with a PostgreSQL database schema for an e-commerce platform. You will create tables, populate them with sample data, and then write several analytical queries and a data manipulation statement.

Part 1: Schema Creation (DDL)

Create the following tables in PostgreSQL, ensuring appropriate data types and constraints are applied. Choose the minimal correct types for each column.

• products:
  • product_id: Primary Key
  • name: Not Null
  • category: Not Null
  • price: Decimal (10,2), Check (price > 0)
• customers:
  • customer_id: Primary Key
  • email: Unique, Not Null
  • created_at: Date, Not Null
• orders:
  • order_id: Primary Key
  • customer_id: Foreign Key referencing customers
  • order_date: Date, Not Null
  • status: Check (status IN ('paid', 'refunded', 'cancelled')), Not Null
  • coupon_code: Text, Nullable
• order_items:
  • order_id: Foreign Key referencing orders (ON DELETE CASCADE)
  • product_id: Foreign Key referencing products
  • qty: Integer, Check (qty > 0), Not Null
  • unit_price: Decimal (10,2), Check (unit_price > 0), Not Null
  • Primary Key: Composite key on (order_id, product_id)

Part 2: Data Insertion (DML)

Insert rows into the created tables to exactly match the provided sample data:

customers:

products:

orders:

order_items:

Part 3: Query A - Monthly Gross Revenue

Return the per-customer gross revenue from orders that meet the following criteria:

• Order status is 'paid'.
• order_date is between '2025-08-01' and '2025-08-31' (inclusive).

Revenue should be calculated as SUM(qty * unit_price) for each order item. Include all customers, even those with zero revenue for the specified period, displaying their revenue as 0.

Output columns: customer_id, email, revenue_aug2025. Sort the results by revenue_aug2025 in descending order, then by customer_id in ascending order.

Part 4: Query B - Recent Product Orders

Using '2025-09-01' as "today's" date, list all products that were ordered in the last 7 days (inclusive, i.e., from '2025-08-26' to '2025-09-01'). Exclude orders with status 'refunded' or 'cancelled'.

Output columns: product_id, name, first_order_date_in_window. The first_order_date_in_window should be the earliest order_date for that specific product within the defined 7-day window.

Part 5: Data Manipulation (DML)

Write a single UPDATE statement to modify the orders table:

• Set coupon_code to 'NONE'.
• Apply this update only to orders where the status is 'paid', the order_date is in August 2025, and the coupon_code is currently NULL.

Immediately following the UPDATE statement, write a SELECT query to verify the exact number of rows that were affected by the update.