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

# SQL Example: Hourly Revenue Indexed by Day

# Overview

This query breaks down daily revenue by hour of day and calculates a relative index (`date_number`) to help analyze time-based trends in order activity. It's useful for visualizing hourly revenue patterns over time, such as performance by weekday vs. weekend or spotting time-of-day conversion peaks.

# SQL Query

```sql
SELECT
  o.event_hour as event_hour,
  date_diff('day', date_add(toDate(@start_date), INTERVAL o.event_hour HOUR), date_add(o.event_date, INTERVAL o.event_hour HOUR)) as date_number,
  sum(order_revenue) as order_revenue
FROM
  orders_table o
WHERE
  event_date BETWEEN @start_date and @end_date
GROUP BY ALL
ORDER BY ALL
```

# Tables and Metrics Used

#### Table

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

#### Dimensions

* `event_hour`
* `event_date`

#### Measures

* `order_revenue`

# Customization Options

This query shifts event timestamps by `event_hour` to align hourly revenue across dates, allowing normalized comparison. You can customize it in the following ways:

#### Analyze different time windows

* Adjust the `event_date` filter or modify `@start_date` to use a different comparison baseline.

#### Use raw event timestamp

* Replace the date math logic with a direct `timestamp` column if available, or use `toDateTime(event_date) + toIntervalHour(event_hour)` for clearer alignment.

#### Add channel or segment filters

* Filter for specific order types (e.g., `platform = 'Shopify'` or `is_new_customer = 1`) to isolate hourly trends by cohort.