If your ecommerce data lives in a PostgreSQL or MySQL database, SQL is the fastest way to extract insights. But knowing which queries to run is half the battle.
Here are 7 essential SQL queries that every ecommerce team should have in their toolkit.
1. Monthly Revenue Trend
The most fundamental business query. Track how revenue changes month over month.
SELECT
DATE_TRUNC('month', created_at) AS month,
SUM(total_amount) AS revenue,
COUNT(DISTINCT order_id) AS order_count
FROM orders
WHERE created_at >= NOW() - INTERVAL '12 months'
GROUP BY 1
ORDER BY 1;
Why it matters: Spot seasonal patterns, measure growth rate, and identify anomalies early.
2. Customer Lifetime Value (CLV)
Understand how much each customer is worth over their entire relationship with your store.
SELECT
customer_id,
COUNT(DISTINCT order_id) AS total_orders,
SUM(total_amount) AS lifetime_value,
MIN(created_at) AS first_order,
MAX(created_at) AS last_order,
MAX(created_at) - MIN(created_at) AS customer_lifespan
FROM orders
GROUP BY customer_id
ORDER BY lifetime_value DESC
LIMIT 100;
Why it matters: CLV tells you how much you can afford to spend on acquisition and which segments deserve more attention.
3. Cohort Retention Analysis
Group customers by their first purchase month and track how many return in subsequent months.
WITH first_purchase AS (
SELECT
customer_id,
DATE_TRUNC('month', MIN(created_at)) AS cohort_month
FROM orders
GROUP BY customer_id
)
SELECT
fp.cohort_month,
DATE_TRUNC('month', o.created_at) AS order_month,
COUNT(DISTINCT o.customer_id) AS active_customers
FROM first_purchase fp
JOIN orders o ON o.customer_id = fp.customer_id
GROUP BY 1, 2
ORDER BY 1, 2;
Why it matters: Retention is the single most important growth lever. Cohort analysis shows you exactly where customers drop off.
4. Top Products by Revenue
Identify your best sellers and understand product-level performance.
SELECT
p.product_name,
p.category,
COUNT(*) AS units_sold,
SUM(oi.quantity * oi.unit_price) AS total_revenue,
AVG(oi.unit_price) AS avg_selling_price
FROM order_items oi
JOIN products p ON p.product_id = oi.product_id
GROUP BY p.product_name, p.category
ORDER BY total_revenue DESC
LIMIT 20;
Why it matters: Know which products drive revenue so you can optimize inventory, marketing, and pricing.
5. New vs Returning Customers
Track the balance between acquisition and retention each month.
WITH first_orders AS (
SELECT customer_id, MIN(created_at) AS first_order_date
FROM orders
GROUP BY customer_id
)
SELECT
DATE_TRUNC('month', o.created_at) AS month,
COUNT(DISTINCT CASE
WHEN DATE_TRUNC('month', o.created_at) = DATE_TRUNC('month', fo.first_order_date)
THEN o.customer_id
END) AS new_customers,
COUNT(DISTINCT CASE
WHEN DATE_TRUNC('month', o.created_at) > DATE_TRUNC('month', fo.first_order_date)
THEN o.customer_id
END) AS returning_customers
FROM orders o
JOIN first_orders fo ON fo.customer_id = o.customer_id
GROUP BY 1
ORDER BY 1;
Why it matters: A healthy business grows new customers while keeping existing ones coming back.
6. Average Order Value by Channel
Understand which marketing channels bring the highest-value orders.
SELECT
COALESCE(utm_source, 'direct') AS channel,
COUNT(DISTINCT order_id) AS orders,
ROUND(AVG(total_amount), 2) AS avg_order_value,
SUM(total_amount) AS total_revenue
FROM orders
WHERE created_at >= NOW() - INTERVAL '3 months'
GROUP BY 1
ORDER BY avg_order_value DESC;
Why it matters: Optimize ad spend by focusing on channels that bring higher-value customers, not just more clicks.
7. Products Frequently Bought Together
Find natural product bundles to increase average order value.
SELECT
a.product_id AS product_a,
b.product_id AS product_b,
COUNT(*) AS times_bought_together
FROM order_items a
JOIN order_items b
ON a.order_id = b.order_id
AND a.product_id < b.product_id
GROUP BY 1, 2
ORDER BY times_bought_together DESC
LIMIT 20;
Why it matters: Product bundling and cross-sell recommendations can increase AOV by 10–30%.
Skip the SQL — Just Ask
Every query above can be generated automatically with Smart Query. Instead of writing SQL, just ask:
"Show me monthly revenue for the past 12 months"
"What's the average order value by marketing channel?"
"Which products are frequently bought together?"
Smart Query understands your database schema, generates optimized SQL, and returns charts and tables instantly. Whether you're a SQL expert or a complete beginner, it saves hours every week.