Design Database: Schema Design and Query Optimization for an E-commerce Platform

Question

## Round 1 - SQL / Schema Design

## Problem

Design a normalized relational schema for an e-commerce platform that supports products, customers, orders, and order items. Then write queries to answer business questions.

```sql
-- Proposed schema (you may modify)
CREATE TABLE customers (id INT PRIMARY KEY, name TEXT, email TEXT);
CREATE TABLE products  (id INT PRIMARY KEY, name TEXT, price DECIMAL, stock INT);
CREATE TABLE orders    (id INT PRIMARY KEY, customer_id INT, created_at TIMESTAMP);
CREATE TABLE order_items (order_id INT, product_id INT, qty INT, unit_price DECIMAL);
```

## Example Queries

```sql
-- Q1: Total revenue per customer in the last 30 days
SELECT c.name, SUM(oi.qty * oi.unit_price) AS revenue
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
WHERE o.created_at >= NOW() - INTERVAL '30 days'
GROUP BY c.name
ORDER BY revenue DESC;

-- Q2: Products never ordered
SELECT p.name FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.id
WHERE oi.product_id IS NULL;
```

## Follow-ups

1. A customer can have multiple shipping addresses per order — how do you extend the schema?
2. Which columns would you index and why?
3. How would you query the top 3 best-selling products per product category?
4. How do you handle `unit_price` to preserve historical price at order time even if `products.price` changes?

Design Database: Schema Design and Query Optimization for an E-commerce Platform

Interview Experience

Full Details

About This Question

Topics

More Glean Interview Questions