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

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)

Dimensions

• event_hour
• event_date

Measures

• order_revenue

Derived

• date_number

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.