Coding questions · CVS Health
Create and query an e-commerce schema
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.
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 |
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.
Table products
| column | type | constraints |
|---|---|---|
| product_id | INT | PRIMARY KEY, NOT NULL |
| name | VARCHAR(100) | NOT NULL |
| category | VARCHAR(50) | NOT NULL |
| price | DECIMAL(10,2) | NOT NULL, CHECK (price > 0) |
Sample data:
| category | name | price | product_id |
|---|---|---|---|
| hardware | Widget | 25 | 10 |
| hardware | Gizmo | 40 | 11 |
| digital | Course | 199 | 12 |
Table customers
| column | type | constraints |
|---|---|---|
| customer_id | INT | PRIMARY KEY, NOT NULL |
| VARCHAR(255) | NOT NULL, UNIQUE | |
| created_at | DATE | NOT NULL |
Sample data:
| created_at | customer_id | |
|---|---|---|
| 2025-08-20 | 1 | alice@example.com |
| 2025-08-28 | 2 | bob@example.com |
| 2025-08-30 | 3 | charlie@example.com |
Table orders
| column | type | constraints |
|---|---|---|
| order_id | INT | PRIMARY KEY, NOT NULL |
| customer_id | INT | NOT NULL, REFERENCES customers(customer_id) |
| order_date | DATE | NOT NULL |
| status | VARCHAR(10) | NOT NULL, CHECK (status IN ('paid','refunded','cancelled')) |
| coupon_code | TEXT |
Sample data:
| coupon_code | customer_id | order_date | order_id | status |
|---|---|---|---|---|
| NULL | 1 | 2025-08-29 | 100 | paid |
| NULL | 1 | 2025-09-01 | 101 | refunded |
| SUMMER10 | 2 | 2025-08-31 | 102 | paid |
| NULL | 3 | 2025-09-01 | 103 | cancelled |
| NULL | 1 | 2025-08-25 | 104 | paid |
Table order_items
| column | type | constraints |
|---|---|---|
| order_id | INT | NOT NULL, REFERENCES orders(order_id) ON DELETE CASCADE |
| product_id | INT | NOT NULL, REFERENCES products(product_id) |
| qty | INT | NOT NULL, CHECK (qty > 0) |
| unit_price | DECIMAL(10,2) | NOT NULL, CHECK (unit_price > 0) |
Sample data:
| order_id | product_id | qty | unit_price |
|---|---|---|---|
| 100 | 10 | 2 | 25 |
| 100 | 11 | 1 | 40 |
| 101 | 12 | 1 | 199 |
| 102 | 10 | 1 | 25 |
| 103 | 11 | 2 | 40 |
| 104 | 10 | 1 | 25 |
| 104 | 11 | 1 | 40 |
Expected output
One row per customer with gross revenue from paid orders in August 2025; customers with no qualifying orders have 0 revenue.
| customer_id | revenue_aug2025 | |
|---|---|---|
| 1 | alice@example.com | 155 |
| 2 | bob@example.com | 25 |
| 3 | charlie@example.com | 0 |
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.
Table products
| column | type | constraints |
|---|---|---|
| product_id | INT | PRIMARY KEY, NOT NULL |
| name | VARCHAR(100) | NOT NULL |
| category | VARCHAR(50) | NOT NULL |
| price | DECIMAL(10,2) | NOT NULL, CHECK (price > 0) |
Sample data:
| category | name | price | product_id |
|---|---|---|---|
| hardware | Widget | 25 | 10 |
| hardware | Gizmo | 40 | 11 |
| digital | Course | 199 | 12 |
Table customers
| column | type | constraints |
|---|---|---|
| customer_id | INT | PRIMARY KEY, NOT NULL |
| VARCHAR(255) | NOT NULL, UNIQUE | |
| created_at | DATE | NOT NULL |
Sample data:
| created_at | customer_id | |
|---|---|---|
| 2025-08-20 | 1 | alice@example.com |
| 2025-08-28 | 2 | bob@example.com |
| 2025-08-30 | 3 | charlie@example.com |
Table orders
| column | type | constraints |
|---|---|---|
| order_id | INT | PRIMARY KEY, NOT NULL |
| customer_id | INT | NOT NULL, REFERENCES customers(customer_id) |
| order_date | DATE | NOT NULL |
| status | VARCHAR(10) | NOT NULL, CHECK (status IN ('paid','refunded','cancelled')) |
| coupon_code | TEXT |
Sample data:
| coupon_code | customer_id | order_date | order_id | status |
|---|---|---|---|---|
| NULL | 1 | 2025-08-29 | 100 | paid |
| NULL | 1 | 2025-09-01 | 101 | refunded |
| SUMMER10 | 2 | 2025-08-31 | 102 | paid |
| NULL | 3 | 2025-09-01 | 103 | cancelled |
| NULL | 1 | 2025-08-25 | 104 | paid |
Table order_items
| column | type | constraints |
|---|---|---|
| order_id | INT | NOT NULL, REFERENCES orders(order_id) ON DELETE CASCADE, PRIMARY KEY (order_id, product_id) |
| product_id | INT | NOT NULL, REFERENCES products(product_id) |
| qty | INT | NOT NULL, CHECK (qty > 0) |
| unit_price | DECIMAL(10,2) | NOT NULL, CHECK (unit_price > 0) |
Sample data:
| order_id | product_id | qty | unit_price |
|---|---|---|---|
| 100 | 10 | 2 | 25 |
| 100 | 11 | 1 | 40 |
| 101 | 12 | 1 | 199 |
| 102 | 10 | 1 | 25 |
| 103 | 11 | 2 | 40 |
| 104 | 10 | 1 | 25 |
| 104 | 11 | 1 | 40 |
Expected output
One row per product that was ordered in the window 2025-08-26..2025-09-01 from non-refunded/non-cancelled orders, with the earliest order date in that window.
| first_order_date_in_window | name | product_id |
|---|---|---|
| 2025-08-29 | Widget | 10 |
| 2025-08-29 | Gizmo | 11 |
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:
- 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
- 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.
Table products
| column | type | constraints |
|---|---|---|
| product_id | INT | PRIMARY KEY, NOT NULL |
| name | VARCHAR(100) | NOT NULL |
| category | VARCHAR(50) | NOT NULL |
| price | DECIMAL(10,2) | NOT NULL, CHECK (price > 0) |
Sample data:
| category | name | price | product_id |
|---|---|---|---|
| hardware | Widget | 25 | 10 |
| hardware | Gizmo | 40 | 11 |
| digital | Course | 199 | 12 |
Table customers
| column | type | constraints |
|---|---|---|
| customer_id | INT | PRIMARY KEY, NOT NULL |
| VARCHAR(255) | NOT NULL, UNIQUE | |
| created_at | DATE | NOT NULL |
Sample data:
| created_at | customer_id | |
|---|---|---|
| 2025-08-20 | 1 | alice@example.com |
| 2025-08-28 | 2 | bob@example.com |
| 2025-08-30 | 3 | charlie@example.com |
Table orders
| column | type | constraints |
|---|---|---|
| order_id | INT | PRIMARY KEY, NOT NULL |
| customer_id | INT | NOT NULL, REFERENCES customers(customer_id) |
| order_date | DATE | NOT NULL |
| status | VARCHAR(10) | NOT NULL, CHECK (status IN ('paid','refunded','cancelled')) |
| coupon_code | TEXT |
Sample data:
| coupon_code | customer_id | order_date | order_id | status |
|---|---|---|---|---|
| NULL | 1 | 2025-08-29 | 100 | paid |
| NULL | 1 | 2025-09-01 | 101 | refunded |
| SUMMER10 | 2 | 2025-08-31 | 102 | paid |
| NULL | 3 | 2025-09-01 | 103 | cancelled |
| NULL | 1 | 2025-08-25 | 104 | paid |
Table order_items
| column | type | constraints |
|---|---|---|
| order_id | INT | NOT NULL, REFERENCES orders(order_id) ON DELETE CASCADE, PRIMARY KEY (order_id, product_id) |
| product_id | INT | NOT NULL, REFERENCES products(product_id) |
| qty | INT | NOT NULL, CHECK (qty > 0) |
| unit_price | DECIMAL(10,2) | NOT NULL, CHECK (unit_price > 0) |
Sample data:
| order_id | product_id | qty | unit_price |
|---|---|---|---|
| 100 | 10 | 2 | 25 |
| 100 | 11 | 1 | 40 |
| 101 | 12 | 1 | 199 |
| 102 | 10 | 1 | 25 |
| 103 | 11 | 2 | 40 |
| 104 | 10 | 1 | 25 |
| 104 | 11 | 1 | 40 |
Expected output
After the UPDATE, no paid August 2025 orders should have coupon_code still NULL, so the verification query returns a count of 0.
| remaining_null_coupon_paid_aug2025 |
|---|
| 0 |
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.