sqladvanced60 minutes

Design and Optimize a Sales Database Schema with Advanced Queries

Create a normalized database schema for a retail sales system that handles customers, products, orders, and shipments. Then, write optimized SQL queries to retrieve key business insights including sales trends, customer segmentation, and shipment statuses.

Challenge prompt

You are tasked with building a comprehensive sales database system for an e-commerce platform. Your mini project has two parts: Part 1: Design a normalized relational schema to manage customers, products, categories, orders, order items, and shipments. Ensure minimal redundancy and support for historical data tracking. Part 2: Using your schema, write the following optimized SQL queries: - Retrieve the top 5 customers by total purchase value in the last 12 months, - Identify products that have not been ordered in the last 6 months, - Generate a monthly summary report showing total revenue, number of orders, and shipment delays. Deliver the DDL statements for your schema and the SQL queries requested.

Guidance

• Normalize tables to at least 3NF to reduce data redundancy and improve data integrity.
• Include appropriate primary keys, foreign keys, and indexes to optimize query performance.
• Use window functions and aggregation to efficiently calculate rankings and summary statistics.

Hints

• Consider a separate table to represent order items since orders can contain multiple products.
• Track shipment status and estimated delivery dates to analyze delays effectively.
• Use DATE functions to filter data based on recent timeframes like last 6 or 12 months.

Starter code

/* Sample table structure for customers and products */
CREATE TABLE customers (
  customer_id SERIAL PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  email VARCHAR(100) UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products (
  product_id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  category_id INT,
  price NUMERIC(10,2),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

/* Add additional tables such as categories, orders, order_items, and shipments */

Expected output

/* Expected results from queries might include: */ -- Top 5 customers with total purchase amount ordered from highest to lowest customer_id | total_spent ------------|------------ 23 | 12500.00 7 | 9800.50 ... -- List of unused products in the last 6 months product_id | name -----------|------ 14 | Vintage Vase 33 | Leather Bag ... -- Monthly sales report month | total_revenue | order_count | avg_shipment_delay ---------|---------------|-------------|-------------------- 2023-01 | 150000.00 | 1200 | 2.3 2023-02 | 175000.00 | 1350 | 1.9 ...

Core concepts

Normalized schema designRelational database modelingSQL aggregation and window functionsPerformance optimization with indexes

Challenge a Friend

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