---
updatedAt: 2025-08-04T11:33:21.000Z
agentTools:
  projectIndex: https://triplewhale.readme.io/llms.txt
---

# SQL Example: Top 200 Customers by Total Order Revenue

# Overview

This query returns a ranked list of customers based on total revenue within a defined date range. It includes order count, revenue, and date range of their activity in the period — useful for identifying high-value customers and segmenting by activity recency or acquisition status.

# SQL Query

```sql
select
  customer_id,
  customer_email,
  sum(order_revenue) as order_revenue,
  count(1) as orders,
  min(event_date) as first_order_date_in_period,
  max(event_date) as last_order_date_in_period,
  max(is_new_customer) as is_new_customer_in_period
from
  orders_table o
where
  event_date BETWEEN '2024-01-01' AND '2024-12-31'
group by
  grouping sets ((customer_id, customer_email), ())
order by 
  order_revenue desc
limit 200
```

# Tables and Metrics Used

#### Table

* `orders_table` ([Orders Table](https://triplewhale.readme.io/docs/orders-table#/))

#### Dimensions

* `customer_id`
* `customer_email`
* `event_date`
* `is_new_customer`

#### Measures

* `order_revenue`

#### Derived

* `orders`

# Customization Options

This query summarizes customer-level revenue and order data within a specific calendar year. You can customize it in the following ways:

#### Change the cohort window

* Adjust the `event_date` filter to report on a different calendar year or rolling period.

#### Filter by new vs. returning customers

* Add `HAVING is_new_customer_in_period = 1` to focus only on customers acquired during the selected period.
* Use `HAVING is_new_customer_in_period = 0` to view only returning customers who had purchased before the period.

#### Add segment filters

* Include filters such as `platform = 'Shopify'` or `region = 'US'` to analyze customer behavior by channel or geography.