Create and query an e-commerce schema

Q: Create and query an e-commerce schema

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

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 |

Create and query an e-commerce schema

Comments (0)