The E-Commerce SQL Cookbook — 8 Query Patterns That Answer 80% of Business Questions

There are hundreds of SQL tutorials online, and almost all of them teach syntax. This one teaches patterns — the reusable query shapes that answer real business questions — and, just as important, the decision each pattern drives. Because a query nobody acts on is just warm CPUs.

1. The Mental Model & Base Tables

Everything below assumes two tables you'll recognize from any e-commerce warehouse:

-- orders: one row per order
--   order_id, customer_id, order_ts, gmv, discount, status

-- events: one row per tracked user action (GA4-style)
--   user_id, event_ts, event_name  -- 'view_item','add_to_cart','purchase'...schema

And one meta-rule before any pattern: agree on the grain. Half of all dashboard disputes are two people computing the same metric at different grains (orders vs customers, gross vs net of cancellations, GMV vs revenue). Write the grain in a comment at the top of every saved query. You'll thank yourself in six months.

2. Pattern 1: The Conversion Funnel

Business question: "Where do we lose people between browsing and buying?" — the first query behind every conversion-rate investigation.

WITH stages AS (
  SELECT
    user_id,
    MAX(CASE WHEN event_name = 'view_item'    THEN 1 ELSE 0 END) AS viewed,
    MAX(CASE WHEN event_name = 'add_to_cart'  THEN 1 ELSE 0 END) AS carted,
    MAX(CASE WHEN event_name = 'begin_checkout'THEN 1 ELSE 0 END) AS checkout,
    MAX(CASE WHEN event_name = 'purchase'     THEN 1 ELSE 0 END) AS purchased
  FROM events
  WHERE event_ts >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 28 DAY)
  GROUP BY user_id
)
SELECT
  COUNT(*)                                AS visitors,
  SUM(viewed)                             AS viewed_item,
  SUM(carted)                             AS added_to_cart,
  SUM(checkout)                           AS began_checkout,
  SUM(purchased)                          AS purchased,
  ROUND(SAFE_DIVIDE(SUM(carted),    SUM(viewed))   * 100, 1) AS view_to_cart,
  ROUND(SAFE_DIVIDE(SUM(checkout),  SUM(carted))   * 100, 1) AS cart_to_checkout,
  ROUND(SAFE_DIVIDE(SUM(purchased), SUM(checkout)) * 100, 1) AS checkout_to_purchase
FROM stagesbigquery sql

How to read it like an operator: the absolute numbers matter less than (a) the trend week-over-week, and (b) the segment splits. Always cut by device and channel next — a checkout-to-purchase drop that exists only on Android app is an engineering ticket, not a merchandising problem. Typical shapes: view→cart weakness = price/assortment/content problem; checkout→purchase weakness = friction, payments, delivery options.

3. Pattern 2: Cohort Retention

Business question: "Are the customers we acquire this year better or worse than last year's?" — the single most honest view of business health, immune to growth masking churn.

WITH first_order AS (
  SELECT customer_id,
         DATE_TRUNC(MIN(DATE(order_ts)), MONTH) AS cohort_month
  FROM orders
  GROUP BY customer_id
),
activity AS (
  SELECT DISTINCT
    o.customer_id,
    f.cohort_month,
    DATE_DIFF(DATE_TRUNC(DATE(o.order_ts), MONTH),
              f.cohort_month, MONTH) AS month_n
  FROM orders o
  JOIN first_order f USING (customer_id)
)
SELECT
  cohort_month,
  COUNT(DISTINCT IF(month_n = 0, customer_id, NULL)) AS cohort_size,
  ROUND(SAFE_DIVIDE(
    COUNT(DISTINCT IF(month_n = 1, customer_id, NULL)),
    COUNT(DISTINCT IF(month_n = 0, customer_id, NULL))) * 100, 1) AS m1,
  ROUND(SAFE_DIVIDE(
    COUNT(DISTINCT IF(month_n = 3, customer_id, NULL)),
    COUNT(DISTINCT IF(month_n = 0, customer_id, NULL))) * 100, 1) AS m3,
  ROUND(SAFE_DIVIDE(
    COUNT(DISTINCT IF(month_n = 6, customer_id, NULL)),
    COUNT(DISTINCT IF(month_n = 0, customer_id, NULL))) * 100, 1) AS m6
FROM activity
GROUP BY cohort_month
ORDER BY cohort_monthbigquery sql

Reading it: scan down a column (is M1 retention improving across cohorts? that's product/CRM improvement) and across a row (where does this cohort's decay flatten? that's your loyal floor). Two traps: recent cohorts have immature late-month cells — grey them out, don't read them; and always check whether a "great" cohort was an acquisition-promo cohort (see the promo playbook on deal-trained customers).

4. Pattern 3: Repeat Rate & Time-to-Second-Order

Business question: "Do customers come back, and how long should we wait before intervening?" The second order is the biggest LTV inflection in most e-commerce datasets — this pattern tells your CRM team exactly when to fire the nudge.

WITH ranked AS (
  SELECT
    customer_id,
    order_ts,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_ts) AS order_n
  FROM orders
)
SELECT
  COUNT(DISTINCT IF(order_n = 1, customer_id, NULL)) AS customers,
  ROUND(SAFE_DIVIDE(
    COUNT(DISTINCT IF(order_n = 2, customer_id, NULL)),
    COUNT(DISTINCT IF(order_n = 1, customer_id, NULL))) * 100, 1) AS repeat_rate,
  -- median days between order 1 and order 2
  APPROX_QUANTILES(
    IF(order_n = 2,
       DATE_DIFF(DATE(order_ts),
                 DATE(LAG(order_ts) OVER (PARTITION BY customer_id
                                          ORDER BY order_ts)), DAY),
       NULL), 100)[OFFSET(50)] AS median_days_to_second
FROM rankedbigquery sql

The decision it drives: if the median time-to-second-order is 35 days, your "we miss you" campaign firing at day 90 is two months late. Set intervention triggers at ~p75 of the gap distribution — late enough to not subsidize organic returns, early enough to matter. This one query has paid my salary multiple times over.

5. Pattern 4: RFM Segmentation

Business question: "Who are our best customers and who's slipping away?" — still the highest value-to-effort ratio in customer analytics, and the backbone of the segment playbook.

WITH base AS (
  SELECT
    customer_id,
    DATE_DIFF(CURRENT_DATE(), MAX(DATE(order_ts)), DAY) AS recency_days,
    COUNT(*)  AS frequency,
    SUM(gmv)  AS monetary
  FROM orders
  WHERE order_ts >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 365 DAY)
  GROUP BY customer_id
),
scored AS (
  SELECT *,
    NTILE(5) OVER (ORDER BY recency_days DESC) AS r,  -- 5 = most recent
    NTILE(5) OVER (ORDER BY frequency)         AS f,
    NTILE(5) OVER (ORDER BY monetary)          AS m
  FROM base
)
SELECT
  CASE
    WHEN r >= 4 AND f >= 4 THEN 'Champions'
    WHEN r >= 3 AND f >= 3 THEN 'Loyal'
    WHEN r <= 2 AND f >= 3 THEN 'At Risk'       -- was good, going quiet
    WHEN r >= 4 AND f <= 2 THEN 'New / Promising'
    WHEN r <= 2 AND f <= 2 THEN 'Lost'
    ELSE 'Regular'
  END AS segment,
  COUNT(*)          AS customers,
  ROUND(SUM(monetary), 0) AS revenue_365d,
  ROUND(AVG(monetary), 0) AS avg_revenue
FROM scored
GROUP BY segment
ORDER BY revenue_365d DESCbigquery sql

The two numbers that always land in the exec deck: what % of revenue comes from Champions (usually a scary 40–60% from <10% of customers), and how many At-Risk customers exist × their historical annual value = the revenue currently walking out the door.

6. Pattern 5: Pareto & ABC Analysis

Business question: "Which SKUs actually matter?" — the prerequisite for any assortment, inventory, or content-investment decision.

WITH sku_rev AS (
  SELECT sku, SUM(gmv) AS revenue
  FROM order_items
  WHERE order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
  GROUP BY sku
)
SELECT *,
  CASE
    WHEN cum_share <= 0.80 THEN 'A'   -- ~top 20% of SKUs, 80% of revenue
    WHEN cum_share <= 0.95 THEN 'B'
    ELSE 'C'                          -- the long tail
  END AS abc_class
FROM (
  SELECT sku, revenue,
    SUM(revenue) OVER (ORDER BY revenue DESC)
      / SUM(revenue) OVER () AS cum_share
  FROM sku_rev
)bigquery sql

Decisions per class: A-SKUs get safety stock, content investment, price monitoring, and stockout alerts (a stockout on an A-SKU is a P1 incident). C-SKUs get assortment review — the question is never "how do we grow this C-SKU" but "why do we still carry it." Run the same pattern on contribution margin instead of revenue and watch a chunk of A-revenue SKUs turn out to be C-profit SKUs. That query starts very uncomfortable, very useful conversations.

7. Pattern 6: Inventory Aging & Weeks of Cover

Business question: "How much cash is trapped in stock that isn't selling?" — the analyst's view of the working-capital problem (full treatment in the inventory playbook).

WITH velocity AS (
  SELECT sku, SUM(units) / 4.0 AS weekly_run_rate   -- last 4 weeks
  FROM order_items
  WHERE order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY)
  GROUP BY sku
)
SELECT
  CASE
    WHEN i.age_days <= 30  THEN '0-30'
    WHEN i.age_days <= 90  THEN '31-90'
    WHEN i.age_days <= 180 THEN '91-180'
    ELSE '180+'
  END AS age_bracket,
  COUNT(DISTINCT i.sku)              AS skus,
  ROUND(SUM(i.units * i.unit_cost), 0) AS cost_value,
  -- weeks of cover: how long current stock lasts at current velocity
  ROUND(SAFE_DIVIDE(SUM(i.units),
        SUM(COALESCE(v.weekly_run_rate, 0))), 1) AS weeks_of_cover
FROM inventory i
LEFT JOIN velocity v USING (sku)
GROUP BY age_bracket
ORDER BY age_bracketbigquery sql

Reading it: value sitting in 180+ with weeks-of-cover above ~26 is your liquidation candidate list — every week it ages, recovery value drops. The follow-up query everyone asks for: the same cut by category and brand, with a drill-down list sorted by cost_value × age.

8. Pattern 7: Growth Accounting

Business question: "Is our growth from new customers or from squeezing existing ones — and is the churn drain accelerating?" This decomposition turns one GMV line into four honest ones.

WITH monthly AS (
  SELECT DISTINCT customer_id,
         DATE_TRUNC(DATE(order_ts), MONTH) AS m
  FROM orders
),
flags AS (
  SELECT customer_id, m,
    LAG(m) OVER (PARTITION BY customer_id ORDER BY m) AS prev_m,
    MIN(m)  OVER (PARTITION BY customer_id)            AS first_m
  FROM monthly
)
SELECT m,
  COUNTIF(m = first_m)                                        AS new_customers,
  COUNTIF(m > first_m AND DATE_DIFF(m, prev_m, MONTH) = 1)  AS retained,
  COUNTIF(m > first_m AND DATE_DIFF(m, prev_m, MONTH) > 1)  AS resurrected
FROM flags
GROUP BY m ORDER BY m
-- churned(m) = active(m-1) - retained(m); compute in a final step or in the BI layerbigquery sql

The metric to watch: the quick ratio — (new + resurrected) ÷ churned. Above 1.5, healthy compounding; near 1.0, you're refilling a leaky bucket at full marketing cost; the trend matters more than the level. This view has ended more "growth is fine" debates than any dashboard I know.

9. Pattern 8: Sessionization from Raw Events

Business question: anything involving "sessions" when all you have is a raw event stream — the gateway pattern to funnel-by-session, session conversion, and browse-abandonment triggers. The classic gaps-and-islands problem:

WITH gaps AS (
  SELECT user_id, event_ts, event_name,
    -- new session when >30 min since previous event
    CASE WHEN TIMESTAMP_DIFF(event_ts,
           LAG(event_ts) OVER (PARTITION BY user_id ORDER BY event_ts),
           MINUTE) > 30
         OR  LAG(event_ts) OVER (PARTITION BY user_id ORDER BY event_ts) IS NULL
         THEN 1 ELSE 0 END AS is_new_session
  FROM events
)
SELECT *,
  -- running sum of session starts = session number per user
  CONCAT(user_id, '-',
    SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY event_ts)
  ) AS session_id
FROM gapsbigquery sql

The LAG-flag-then-cumulative-SUM shape is worth internalizing — it reappears everywhere: streak detection, consecutive stockout days, price-change episodes, delivery attempt chains. Learn it once, reuse it for years.

10. The Craft: Habits That Make Queries Trustworthy

1. Declare the grain in a comment, always. "One row per customer per month." Most metric disputes are grain disputes wearing a costume.
2. Define statuses explicitly: does GMV include cancelled? returned? unpaid COD? Write the WHERE clause once in a view (orders_net) and make everyone query the view.
3. Use SAFE_DIVIDE everywhere. The dashboard that dies on division-by-zero at 8am Monday is a credibility tax.
4. Sanity-anchor every new query: before trusting a clever query, reconcile its total against a number you already trust (finance's monthly GMV). Off by 3%? Find out why before publishing, because someone in the readout will.
5. QUALIFY is your friend in BigQuery — QUALIFY ROW_NUMBER() OVER (...) = 1 deduplicates in one line, no subquery. The most common dedup need: "latest record per entity."
6. Partition-prune or pay: always filter on the table's partition column (usually date) first. It's the difference between scanning 40GB and 400MB — in money and in speed.
7. Name CTEs like sentences: first_order, monthly_active, ranked_by_revenue. Your reviewer — and future you — reads the CTE names as the query's table of contents.

Sources & Further Reading:
BigQuery Window Functions docs • RFM with BigQuery ML • Cohort Retention SQL Templates