Every e-commerce business runs on data: what's selling, what's sitting in the warehouse, which customers are spending the most, and which months are the strongest. All of that lives in relational tables — and SQL is the fastest way to get answers.
In this guide, we'll walk through the most valuable SQL queries for e-commerce analytics, building from a realistic data model you can run right in your browser.
The E-Commerce Data Model
Before we query anything, let's understand the structure. A typical e-commerce database has these core tables:
Each order contains multiple order_items — one row per product line. This separation makes it easy to analyze both the order level (total spend, status) and the item level (which specific products were bought).
Query 1: Top-Selling Products by Revenue
The first dashboard any e-commerce manager wants: which products are generating the most revenue?
Notice the WHERE o.status != 'cancelled' clause — always filter out cancelled orders before aggregating revenue. Including them would overstate your numbers.
[!TIP] Slow on big tables? Add an index on
order_items_ecom_1.product_idandorders_ecom_1.status— those are your JOIN and WHERE columns. Check out our guide on Understanding Database Indexes to learn how.
Query 2: Revenue by Category, Month over Month
Breaking revenue down by category and month reveals trends: is Gaming growing? Is Audio declining? Month-over-month comparisons are essential for spotting seasonality.
The strftime('%Y-%m', order_date) function formats dates as YYYY-MM, which sorts correctly as text. This is a SQLite standard; other databases use DATE_FORMAT() (MySQL) or TO_CHAR() (PostgreSQL).
Query 3: Inventory Health Report
Stockouts kill sales. Overstock ties up capital. A good inventory health query shows you both risks at once:
The CASE inside ORDER BY lets us define a custom sort priority — problems first. This is a pattern worth knowing: you can use CASE anywhere an expression is valid, including ORDER BY, GROUP BY, and WHERE.
Query 4: Customer Lifetime Value Ranking
Which customers are your most valuable? Customer Lifetime Value (CLV) is the total revenue a customer has generated across all their orders:
COUNT(DISTINCT o.id) counts unique orders per customer — if you used COUNT(*) here you'd count order items, inflating the order count. Always be explicit about what you're counting.
Query 5: Finding Products Bought Together
Which products are frequently purchased in the same order? This is the foundation of "customers also bought" recommendations:
The self-join on order_items_ecom_1 with the condition oi1.product_id < oi2.product_id is the key trick — it creates all unique pairs within the same order, without duplicating (A, B) and (B, A) as separate rows. This is a classic market basket analysis pattern.
Best Practices for E-Commerce SQL
- Always filter cancelled orders: Revenue queries should consistently exclude
status = 'cancelled'unless you specifically need to analyze cancellations. Make this a habit. - Round monetary values:
ROUND(revenue, 2)prevents floating-point noise from appearing in reports. - Use
COUNT(DISTINCT order_id)vsCOUNT(*): Know which one you need. The difference between "number of orders" and "number of order lines" trips up many analysts. - Store prices in
order_items, not justproducts: Product prices change over time. Always captureunit_priceat order time — never re-join to the currentproducts.priceto calculate historical revenue. - Partition large tables by date: On real e-commerce databases with millions of orders, date partitioning on
order_datedramatically improves query performance.
Tool Workflow
Use tools when commerce reporting SQL grows from one dashboard card into a full workflow
E-commerce analysis mixes joins, filters, revenue logic, and product behavior patterns. Use the tools to inspect the queries before they become weekly reports or decision inputs.
Conclusion
With the five queries above, you can answer the most common e-commerce analytics questions:
- What's making us money? — Top products by revenue
- Which categories are growing? — Monthly breakdown by category
- Are we going to run out of anything? — Inventory health report
- Who are our best customers? — Customer lifetime value ranking
- What should we recommend? — Frequently bought together
The underlying SQL is straightforward — it's just JOINs, GROUP BY, and CASE statements working together. The key is understanding your data model well enough to know which tables to connect and what to filter out.
Want to take this further? See our guide on Cohort Analysis with SQL to segment customers by when they first purchased and track their behavior over time.
Related Articles
- SQL for Data Analysis: The Ultimate Guide for the broader analytical workflow that ecommerce reporting fits into.
- Market Basket Analysis in SQL: What Do Customers Buy Together? for the association pattern behind “bought together” recommendations.
- Calculating Customer Lifetime Value in SQL for the deeper revenue lens behind top-customer analysis.