Create and query an e-commerce schema
Company: CVS Health
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Online Assessment
PostgreSQL only. 1) Create these tables with appropriate types and constraints (choose minimal correct types): products(product_id PK, name NOT NULL, category NOT NULL, price DECIMAL(10,2) CHECK (price > 0)); customers(customer_id PK, email UNIQUE NOT NULL, created_at DATE NOT NULL); orders(order_id PK, customer_id FK->customers, order_date DATE NOT NULL, status CHECK (status IN ('paid','refunded','cancelled')) NOT NULL, coupon_code TEXT NULL); order_items(order_id FK->orders ON DELETE CASCADE, product_id FK->products, qty INT CHECK (qty > 0) NOT NULL, unit_price DECIMAL(10,2) CHECK (unit_price > 0) NOT NULL, PRIMARY KEY(order_id, product_id)). 2) Insert rows so the tables exactly match the ASCII samples below. 3) Query A: Return per-customer gross revenue from orders with status = 'paid' whose order_date is between '2025-08-01' and '2025-08-31' inclusive. Revenue is SUM(qty * unit_price). Include customers with zero revenue as 0; output columns (customer_id, email, revenue_aug2025). Sort by revenue DESC, then customer_id ASC. 4) Query B: Using "today" = '2025-09-01', list products ordered in the last 7 days [window: '2025-08-26'..'2025-09-01'] from orders whose status NOT IN ('refunded','cancelled'). Output (product_id, name, first_order_date_in_window). 5) DML: Write one UPDATE that sets coupon_code = 'NONE' for orders with status = 'paid', order_date in August 2025, and coupon_code IS NULL; then a SELECT that verifies how many such rows exist after the update. ASCII samples to insert: customers: | customer_id | email | created_at | |------------|---------------------|------------| | 1 | alice@example.com | 2025-08-20 | | 2 | bob@example.com | 2025-08-28 | | 3 | charlie@example.com | 2025-08-30 | products: | product_id | name | category | price | |------------|---------|----------|-------| | 10 | Widget | hardware | 25.00 | | 11 | Gizmo | hardware | 40.00 | | 12 | Course | digital | 199.00| orders: | order_id | customer_id | order_date | status | coupon_code | |----------|-------------|------------|-----------|-------------| | 100 | 1 | 2025-08-29 | paid | NULL | | 101 | 1 | 2025-09-01 | refunded | NULL | | 102 | 2 | 2025-08-31 | paid | SUMMER10 | | 103 | 3 | 2025-09-01 | cancelled | NULL | | 104 | 1 | 2025-08-25 | paid | NULL | order_items: | order_id | product_id | qty | unit_price | |----------|------------|-----|------------| | 100 | 10 | 2 | 25.00 | | 100 | 11 | 1 | 40.00 | | 101 | 12 | 1 | 199.00 | | 102 | 10 | 1 | 25.00 | | 103 | 11 | 2 | 40.00 | | 104 | 10 | 1 | 25.00 | | 104 | 11 | 1 | 40.00 |
Overview: This question evaluates SQL schema design and data manipulation competencies, including defining PostgreSQL table types and constraints, inserting exact datasets, composing aggregate and join queries with date-based filters, and performing DML updates with verification.
Per-customer gross revenue for paid orders in August 2025
You are given an e-commerce schema with tables: products, customers, orders, and order_items (see table definitions and sample data). Write a PostgreSQL query to return per-customer gross revenue from orders with status = 'paid' whose order_date is between '2025-08-01' and '2025-08-31' inclusive.
Revenue is defined as SUM(qty * unit_price) across all qualifying order_items.
Requirements:
- Include ALL customers, even if they have zero qualifying revenue (show 0).
- Output columns: (customer_id, email, revenue_aug2025).
- Sort by revenue_aug2025 DESC, then customer_id ASC.
Tables
products(product_id INT, name VARCHAR(100), category VARCHAR(50), price DECIMAL(10,2))
customers(customer_id INT, email VARCHAR(255), created_at DATE)
orders(order_id INT, customer_id INT, order_date DATE, status VARCHAR(10), coupon_code TEXT)
order_items(order_id INT, product_id INT, qty INT, unit_price DECIMAL(10,2))
Hints
- Use LEFT JOIN from customers to orders so customers with no qualifying orders still appear.
- Put the date/status filters in the JOIN condition (or use conditional aggregation) to avoid turning the LEFT JOIN into an INNER JOIN.
Products ordered in the last 7 days window (2025-08-26 to 2025-09-01)
Using the same schema and data, list products ordered in the 7-day window FROM '2025-08-26' TO '2025-09-01' inclusive.
Include only orders whose status NOT IN ('refunded','cancelled').
For each product ordered in that window, output:
- product_id
- name
- first_order_date_in_window (the earliest order_date for that product within the window)
Each product should appear at most once in the output.
Tables
products(product_id INT, name VARCHAR(100), category VARCHAR(50), price DECIMAL(10,2))
customers(customer_id INT, email VARCHAR(255), created_at DATE)
orders(order_id INT, customer_id INT, order_date DATE, status VARCHAR(10), coupon_code TEXT)
order_items(order_id INT, product_id INT, qty INT, unit_price DECIMAL(10,2))
Hints
- Join orders -> order_items -> products and filter on order_date and status.
- Use MIN(order_date) grouped by product to get the first order date in the window.
Update NULL coupon codes for paid August 2025 orders and verify
Using the same schema and data, write:
1) One UPDATE statement that sets coupon_code = 'NONE' for orders where:
- status = 'paid'
- order_date is between '2025-08-01' and '2025-08-31' inclusive
- coupon_code IS NULL
2) A SELECT statement that verifies how many orders still match those conditions (i.e., remaining rows with coupon_code IS NULL under the same filters) after the UPDATE.
Output of the verification SELECT should be a single row with a single count column.
Tables
products(product_id INT, name VARCHAR(100), category VARCHAR(50), price DECIMAL(10,2))
customers(customer_id INT, email VARCHAR(255), created_at DATE)
orders(order_id INT, customer_id INT, order_date DATE, status VARCHAR(10), coupon_code TEXT)
order_items(order_id INT, product_id INT, qty INT, unit_price DECIMAL(10,2))
Hints
- Use a single UPDATE with a WHERE clause that exactly matches the required conditions.
- The verification query should repeat the same filters and COUNT(*) rows remaining with coupon_code IS NULL.