sqladvanced60 minutes

Design and Query a Scalable E-commerce Order Management Schema

Build a normalized SQL schema to efficiently model orders, customers, products, and payments for a growing e-commerce system. Write an optimized SQL query to report monthly revenue per product category, including handling edge cases such as refunds and partial payments.

Challenge prompt

You are tasked with designing a robust, normalized SQL schema to support a large e-commerce platform's order management system. The data model should include tables for customers, products (grouped by categories), orders, order items, and payments (which may involve multiple partial payments and refunds). After creating the schema, write an optimized SQL query that returns total monthly revenue aggregated by product category, accounting for refunds and partial payments in the calculation. Your schema should support scalability and flexibility for additions such as new payment methods or promotions. Requirements: 1. Create SQL tables with appropriate keys and constraints reflecting the entities and their relationships. 2. Ensure that an order can contain multiple products, and payments can be partial or refunds. 3. Write an SQL query that outputs, for each month and each product category, the net revenue (payments minus refunds) generated. Provide the CREATE TABLE statements followed by the query to retrieve the monthly revenue by product category.

Guidance

• Focus first on designing a normalized and scalable schema representing entities and their relationships with primary and foreign keys.
• Consider how to model payments separately to allow multiple partial payments and refunds for one order.
• In your query, use appropriate JOINs and aggregation to calculate monthly revenue grouped by product category, carefully subtracting refunds.

Hints

• A linking table is necessary between orders and products to model order items with quantities and prices.
• Use a payment type or status field to distinguish refunds from standard payments.
• Leverage date functions to group revenues by month in your final aggregation query.

Starter code

/* Create tables skeleton */
CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE
);

CREATE TABLE Categories (
    category_id INT PRIMARY KEY,
    category_name VARCHAR(100) NOT NULL
);

CREATE TABLE Products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    category_id INT,
    price DECIMAL(10, 2),
    FOREIGN KEY (category_id) REFERENCES Categories(category_id)
);

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

CREATE TABLE OrderItems (
    order_item_id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT,
    item_price DECIMAL(10, 2),
    FOREIGN KEY (order_id) REFERENCES Orders(order_id),
    FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

CREATE TABLE Payments (
    payment_id INT PRIMARY KEY,
    order_id INT,
    payment_date DATE,
    amount DECIMAL(10, 2),
    payment_type VARCHAR(50), -- e.g., 'payment', 'refund'
    FOREIGN KEY (order_id) REFERENCES Orders(order_id)
);

/* Write query here */

Expected output

Core concepts

database normalizationcomplex data modelingSQL joins and aggregationshandling partial payments and refunds

Challenge a Friend

Send this duel to someone else and see if they can solve it.