Business Scenario & Relational Schema
You are a Data Analyst at Apex Retail, a fast-growing multi-channel e-commerce retailer. Executive leadership needs daily insights into product performance, regional demand, customer purchasing power, and revenue velocity.
customers
customer_id (PK)
customer_name, city, region
orders
order_id (PK)
customer_id (FK), order_date, region
order_items
item_id (PK), order_id (FK)
product_id (FK), quantity, unit_price, discount
products
product_id (PK)
product_name, category, unit_price
The Sales Data Model: Order vs. Order Item
An Order Item represents an individual product line inside that order.
For example, Order #1001 contains 2 distinct line items: 1 Laptop ($1,200) and 2 Keyboards ($120 each). Running COUNT(*) on order_items returns 2, but the number of customer checkout orders is 1!
Core Sales Metrics Definition
SUM(qty * price * (1 - discount))
Total Net Revenue / COUNT(DISTINCT order_id)
Category Sales / SUM(Sales) OVER ()
(Sales - LAG(Sales)) / LAG(Sales) * 100
Total Sales & Net Revenue Calculation
SELECT
ROUND(SUM(quantity * unit_price * (1 - discount)), 2) AS net_revenue
FROM order_items;
Average Order Value (AOV)
ROUND(SUM(quantity * unit_price * (1 - discount)) / COUNT(DISTINCT order_id), 2) AS average_order_value
FROM order_items;
Monthly Sales & Month-over-Month Growth (LAG)
SELECT
SUBSTRING(o.order_date, 1, 7) AS month,
ROUND(SUM(oi.quantity * oi.unit_price * (1 - oi.discount)), 2) AS revenue
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY month
)
SELECT
month,
revenue,
LAG(revenue) OVER (ORDER BY month) AS previous_month_revenue,
ROUND(revenue - LAG(revenue) OVER (ORDER BY month), 2) AS revenue_variance
FROM monthly_sales;
Think Like a Data Analyst
Executive leaders do not ask for SQL queries — they ask strategic business questions. As an analytics professional, your job is to translate business ambiguity into rigorous SQL:
"Which product lines should we discontinue or heavily promote?"
➔ Metric: Revenue ranking, contribution %, and unit margin volume."Are our marketing campaigns acquiring high-value repeat buyers?"
➔ Metric: Customer LTV, multi-order frequency, and AOV distribution.Calculate the total net sales revenue generated by the business, taking product quantities, unit prices, and discounts into account.