Overview

This query shows how often customers place orders within a given time period by calculating how many customers placed 1, 2, 3, or more orders. It helps you understand purchase frequency distribution and evaluate retention, loyalty, and cohort behavior at a glance.

SQL Query

WITH orders AS
(
  SELECT
    customer_id,
    count(1) AS orders
  FROM
    orders_table o
  WHERE
    event_date BETWEEN @start_date AND @end_date
  GROUP BY ALL
)
SELECT
  orders,
  uniq (o.customer_id) as unique_customers
FROM
  orders
GROUP BY
  grouping sets ((orders), ())
ORDER BY 1

Tables and Metrics Used

Table

• orders_table (Orders Table)

Dimensions

• customer_id

Derived

• orders

Customization Options

This query gives a high-level distribution of how frequently customers buy during a selected time range. You can customize it in the following ways:

Adjust the analysis period

• Change the event_date range in the WHERE clause to look at different time windows (e.g. quarterly, yearly).

Group by acquisition cohort

• Add MAX(is_new_customer) and filter or group based on customer acquisition timing to see how first-time vs. repeat cohorts behave.

Bucket order frequency

• Replace the exact orders count with buckets (e.g. CASE WHEN orders >= 4 THEN '4+' ELSE toString(orders) END) to simplify the distribution view.