> ## Documentation Index
> Fetch the complete documentation index at: https://docs.withorb.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Querying data with SQL

You often want to query the event data that Orb is storing, to better understand attribution to specific dimensions of your event – even if these dimensions aren't directly relevant to how you bill customers.

Since Orb's architecture is designed to run aggregation queries over large sets of event data at query time rather than at ingestion time, the product also exposes this capability directly for ad-hoc querying.

For example, you might want to:

* View usage for your compute metric broken down by `cluster_name`, where a user may have hundreds of clusters active in a given time period.
* Understand how much a specific transaction contributed to a processing charge by point-filtering to a known `transaction_id`.
* Provide more detailed analytics, such as breaking down usage and cost data by `bucket_name` and `data_tier` which is included in events you send to Orb.
* Build an interactive explorer in your application to view hourly data over a day time range, rather than the default daily views available through Orb's subscription usage and cost APIs.
* Provide a view into end customer usage by `api_key` prefix, allowing users to understand the source of API requests.

## Evaluate prices API

The [evaluate prices](/api-reference/price/evaluate-multiple-prices) API is Orb's most powerful querying tool, enabling you to slice, dice, and rate event data with SQL-like flexibility. This endpoint allows
you to evaluate prices on existing data with advanced filtering and grouping capabilities.

### Core Capabilities

The endpoint can evaluate:

* **Up to 100 price evaluations** in a single request (existing or inline-defined prices)
* **Historical data** up to 100 days in the past (for ingested events)
* **Complex filtering and grouping** using computed properties with SQL-like expressions
* **Results limited to 1000 rows** to ensure performance

**Request Structure:**

* **`timeframe_start`** (required): Inclusive lower bound for event timestamps
* **`timeframe_end`** (required): Exclusive upper bound for event timestamps
* **`customer_id`** (optional): Required when querying ingested events, omit for preview events
* **`external_customer_id`** (optional): Alternative to customer\_id
* **`price_evaluations`** (required): Array of prices to evaluate with optional filtering and grouping

### Interpreting evaluated amounts

Use evaluate prices as a rating and attribution tool: it applies the selected prices to the events in your query and returns grouped quantities and amounts.

For complex billing setups, these amounts may not equal finalized invoice totals:

* Invoice-level or multi-price adjustments (such as minimums) are applied during invoice calculation.
* Some non-linear models depend on period-level context, so there is not always a single fully adjusted "cost per event."

If you need finalized totals, use invoice resources or [subscription costs](/api-reference/subscription/fetch-subscription-costs). If you need event-level attribution by dimensions like `user_id` or `api_key`, evaluate prices is the recommended endpoint.

## Key use cases

### 1. Customer-Facing Usage Analytics

**Scenario**: Provide your customers with detailed breakdowns of their usage and costs, going beyond standard invoice line items.

**Example**: A cloud storage provider wants to show customers their costs broken down by storage tier, region, and time period.

```json theme={null}
{
  "customer_id": "customer_123",
  "timeframe_start": "2024-01-01T00:00:00Z",
  "timeframe_end": "2024-01-31T23:59:59Z",
  "price_evaluations": [
    {
      "price_id": "storage_price_id",
      "grouping_keys": [
        "storage_tier",
        "region", 
        "floor(hour_floor_timestamp_millis(timestamp_millis) / 86400000) * 86400000"
      ],
      "filter": "storage_tier IN ('standard', 'premium')"
    }
  ]
}
```

This query provides daily usage and cost data segmented by storage tier and region, enabling rich customer dashboards.

<Note>
  Orb supports `hour_floor_timestamp_millis(timestamp_millis)` for retrieving the floored hour of usage (UTC) in milliseconds. To get the floored day in milliseconds, use the syntax above.
</Note>

### 2. Invoice Line Item Auditing

**Scenario**: Provide detailed breakdowns of invoice charges to help customers understand exactly what they're being billed for.

**Example**: Breaking down a monthly API usage charge by endpoint and customer tier.

```json theme={null}
{
  "customer_id": "customer_456",
  "timeframe_start": "2024-01-01T00:00:00Z",
  "timeframe_end": "2024-01-31T23:59:59Z",
  "price_evaluations": [
    {
      "price_id": "api_usage_price",
      "grouping_keys": [
        "endpoint",
        "customer_tier"
      ],
      "filter": "response_code = '200'"
    }
  ]
}
```

### 3. High-Dimensional Usage Analysis

**Scenario**: Analyze usage patterns across multiple dimensions that aren't part of your standard billing structure.

**Example**: A data processing platform analyzing usage by job type, data source, and processing complexity.

```json theme={null}
{
  "customer_id": "customer_789",
  "timeframe_start": "2024-01-01T00:00:00Z",
  "timeframe_end": "2024-01-07T23:59:59Z",
  "price_evaluations": [
    {
      "price_id": "data_processing_price",
      "grouping_keys": [
        "job_type",
        "data_source",
        "complexity_tier",
        "hour_floor_timestamp_millis(timestamp_millis)"
      ],
      "filter": "job_status = 'completed' AND data_size_gb > 1"
    }
  ]
}
```

### 4. Transaction-Level Cost Attribution

**Scenario**: Understand the cost impact of specific transactions or operations.

**Example**: Analyzing the cost contribution of individual database queries or API calls.

```json theme={null}
{
  "customer_id": "customer_101",
  "timeframe_start": "2024-01-15T00:00:00Z",
  "timeframe_end": "2024-01-15T23:59:59Z",
  "price_evaluations": [
    {
      "price_id": "database_operations_price",
      "filter": "transaction_id = 'txn_abc123' OR query_type = 'complex_join'"
    }
  ]
}
```

## Evaluate preview events API

The [evaluate preview events](/api-reference/price/evaluate-preview-events) API rates hypothetical events on existing prices in Orb allowing you to build price calculators and "what-if" scenarios off
prices in Orb.

### Core Capabilities

The endpoint can evaluate:

* **Up to 100 price evaluations** in a single request (existing or inline-defined prices)
* **Up to 500 preview events** in a single request
* **Complex filtering and grouping** using computed properties with SQL-like expressions
* **Results limited to 1000 rows** to ensure performance

**Request Structure:**

* **`timeframe_start`** (required): Inclusive lower bound for event timestamps
* **`timeframe_end`** (required): Exclusive upper bound for event timestamps
* **`customer_id`** (optional): Required when querying ingested events, omit for preview events
* **`events`** (required): Array of preview events
* **`external_customer_id`** (optional): Alternative to customer\_id
* **`price_evaluations`** (required): Array of prices to evaluate with optional filtering and grouping

## Key use cases

### 1. Price Calculator and Modeling

**Scenario**: Build an interactive price calculator that shows potential costs before customers commit to a plan.

**Example**: A compute platform wants to let prospects estimate costs based on expected usage patterns.

```json theme={null}
{
  "timeframe_start": "2024-01-15T00:00:00Z",
  "timeframe_end": "2024-01-15T23:59:59Z",
  "customer_id": "customer_123",
  "events": [
    {
      "event_name": "compute_usage",
      "timestamp": "2024-01-15T10:00:00Z",
      "properties": {
        "compute_hours": 100,
        "instance_type": "large",
        "region": "us-east-1"
      }
    },
    {
      "event_name": "compute_usage", 
      "timestamp": "2024-01-15T14:00:00Z",
      "properties": {
        "compute_hours": 50,
        "instance_type": "medium",
        "region": "us-west-2"
      }
    }
  ],
  "price_evaluations": [
    {
      "price": {
        "name": "Compute Hours",
        "item_id": "compute_hours_price",
        "cadence": "monthly",
        "model_type": "unit",
        "unit_config": {
          "unit_amount": "0.10"
        },
        "currency": "USD",
        "billable_metric_id": "compute_hours_metric"
      },
      "grouping_keys": [
        "instance_type",
        "region"
      ]
    }
  ]
}
```

This enables real-time cost estimation as prospects adjust their expected usage parameters.

## Evaluate prices API vs. evaluate preview events API

### Preview Events (Rating Mode)

Use preview events when you want to:

* Build price calculators
* Model "what-if" scenarios
* Test new pricing structures
* Provide cost estimates before usage occurs

**Key characteristics**:

* Events are provided directly in the request
* No historical data required
* Perfect for forward-looking analysis
* Limited to 500 events per request
* **No customer\_id required** - the API rates the provided events directly

### Ingested Events (Query Mode)

Use ingested events when you want to:

* Analyze historical usage patterns
* Provide detailed billing breakdowns
* Audit past charges
* Build usage analytics dashboards

**Key characteristics**:

* Uses events already stored in Orb
* **Requires customer\_id** to specify which customer's events to query
* Can query up to 100 days of historical data
* Supports complex filtering and aggregation
* Ideal for retrospective analysis

## Advanced filtering and grouping

### Computed Properties

The API supports computed properties for sophisticated data manipulation using Orb's supported functions:

#### Time-based Functions

```sql theme={null}
-- Group by hour
hour_floor_timestamp_millis(timestamp_millis)

-- Group by day (with timezone support)
floor(hour_floor_timestamp_millis(timestamp_millis) / 86400000) * 86400000
```

#### Aggregation Functions

```sql theme={null}
-- Count events
COUNT(*)

-- Count distinct values
COUNT(DISTINCT region)

-- Get earliest value by timestamp
EARLIEST_BY(region, timestamp_millis)

-- Get latest value by timestamp
LATEST_BY(instance_type, timestamp_millis)

-- Mathematical operations
LEAST(cpu_hours, max_cpu_hours)
GREATEST(cpu_hours, min_cpu_hours)

-- Rounding
ROUND(data_gb, 2)
```

### Filtering Examples

#### Basic Property Filtering

```sql theme={null}
-- Exact matches
region = 'us-east-1'
instance_type = 'large'

-- Numeric comparisons
cpu_hours > 10
data_gb >= 100

-- Multiple conditions with AND/OR
region = 'us-east-1' AND cpu_hours > 10
tier = 'premium' OR tier = 'enterprise'

-- IN clauses
region IN ('us-east-1', 'us-west-2')
tier IN ('premium', 'enterprise')

-- Null checks
customer_segment IS NOT NULL
api_key IS NULL
```

#### Advanced Filtering

```sql theme={null}
-- Combining multiple conditions
environment = 'production' AND region = 'us-east-1' AND cpu_hours > 50

-- Complex boolean logic
(tier = 'premium' AND region = 'us-east-1') OR (tier = 'enterprise' AND cpu_hours > 100)

-- Timestamp filtering
timestamp_millis >= 1704067200000
```

## Response structure and interpretation

The API returns results grouped according to your specified `grouping_keys`. The response contains a `data` array where each element represents the results for a specific price, with the following structure:

* **price\_groups**: Array of grouped results for this price
  * **grouping\_values**: The specific values for each grouping dimension
  * **quantity**: The aggregated metric value for the group
  * **amount**: The calculated cost for the group
* **currency**: The currency of the price
* **price\_id**: The ID of the price (for existing prices)
* **inline\_price\_index**: The index of the inline price (for inline-defined prices)

### Example Response

```json theme={null}
{
  "data": [
    {
      "price_groups": [
        {
          "grouping_values": [
            "us-east-1",
            "large"
          ],
          "quantity": 150.0,
          "amount": "15.00"
        },
        {
          "grouping_values": [
            "us-west-2", 
            "medium"
          ],
          "quantity": 75.0,
          "amount": "7.50"
        }
      ],
      "currency": "USD",
      "price_id": "price_123"
    }
  ]
}
```

### Multiple Prices Response

When evaluating multiple prices, you'll get separate entries in the `data` array:

```json theme={null}
{
  "data": [
    {
      "price_groups": [
        {
          "grouping_values": ["compute"],
          "quantity": 100.0,
          "amount": "50.00"
        }
      ],
      "currency": "USD",
      "price_id": "compute_price_id"
    },
    {
      "price_groups": [
        {
          "grouping_values": ["storage"],
          "quantity": 500.0,
          "amount": "25.00"
        }
      ],
      "currency": "USD",
      "inline_price_index": 0
    }
  ]
}
```

## Best practices

### Performance Optimization

1. **Limit result sets**: Use filters to reduce the number of rows returned
2. **Choose appropriate time ranges**: Shorter ranges perform better
3. **Optimize grouping**: Avoid excessive grouping dimensions
4. **Use specific filters**: More specific filters improve query performance

### Data Accuracy

1. **Validate preview events**: Ensure preview events match your actual event schema
2. **Test with small datasets**: Start with limited time ranges when exploring
3. **Verify metric definitions**: Ensure your billable metrics align with expectations
4. **Handle edge cases**: Consider how your filters handle null or missing properties

### Integration Patterns

1. **Caching**: Cache results for frequently accessed data
2. **Pagination**: Use time-based pagination for large datasets
3. **Error handling**: Implement robust error handling for API limits
4. **Rate limiting**: Respect API rate limits in high-frequency scenarios

## Common patterns and examples

### Building a Usage Dashboard

Combine multiple API calls to build comprehensive usage dashboards:

```javascript theme={null}
// Get daily usage trends
const dailyUsage = await evaluatePrices({
  customer_id: customerId,
  timeframe_start: startDate,
  timeframe_end: endDate,
  price_evaluations: [
    {
      price_id: priceId,
      grouping_keys: ["day_floor_timestamp_millis(timestamp_millis)"]
    }
  ]
});

// Get usage by service
const serviceBreakdown = await evaluatePrices({
  customer_id: customerId,
  timeframe_start: startDate,
  timeframe_end: endDate,
  price_evaluations: [
    {
      price_id: priceId,
      grouping_keys: ["service_name"]
    }
  ]
});

// Get top consumers
const topConsumers = await evaluatePrices({
  customer_id: customerId,
  timeframe_start: startDate,
  timeframe_end: endDate,
  price_evaluations: [
    {
      price_id: priceId,
      grouping_keys: ["user_id"],
      filter: "user_id IS NOT NULL"
    }
  ]
});
```

### Price Comparison Tool

Compare costs across different pricing models:

```javascript theme={null}
const priceComparison = await evaluatePrices({
  timeframe_start: startDate,
  timeframe_end: endDate,
  events: previewEvents,
  price_evaluations: [
    {
      // Current pricing
      price_id: "current_price_id"
    },
    {
      // Alternative unit pricing
      price: {
        name: "Alternative Unit Pricing",
        item_id: "alt_unit_price",
        cadence: "monthly",
        model_type: "unit",
        unit_config: { 
          unit_amount: "0.05" 
        },
        currency: "USD",
        billable_metric_id: "api_calls_metric"
      }
    }
  ]
});
```

The evaluate prices API transforms how you can interact with your billing data, enabling everything from simple usage queries to sophisticated pricing analysis. By leveraging its filtering and grouping capabilities, you can build rich, customer-facing analytics and powerful internal tools for pricing optimization.