Overview

This query compares revenue between the first and second halves of a selected date range. It’s useful for evaluating how performance evolved during a promotion, campaign, or seasonal period, helping you detect mid-period trends, dropoffs, or surges in customer activity.

SQL Query

WITH orders AS
(
  SELECT
    COALESCE(o.created_at,date_add(o.event_date, INTERVAL o.event_hour HOUR)) AS created_at,
    o.order_revenue AS order_revenue,
  FROM
    orders_table o
  WHERE
    event_date BETWEEN @start_date AND @end_date
)
SELECT
  toDate(@start_date) AS date_range_from,
  toDate(@end_date) AS date_range_to,
  toDateTime(@start_date) + (toDateTime(@end_date) - toDateTime(@start_date)) / 2 AS date_range_middle_point,
  sumIf(order_revenue, created_at <= toDateTime(@start_date) + (toDateTime(@end_date) - toDateTime(@start_date)) / 2) AS order_revenue_1st_half,
  sumIf(order_revenue, created_at >  toDateTime(@start_date) + (toDateTime(@end_date) - toDateTime(@start_date)) / 2) AS order_revenue_2nd_half,
FROM
  orders

Tables and Metrics Used

Table

• orders_table (Orders Table)

Dimensions

• created_at
• event_date
• event_hour

Measures

• order_revenue

Customization Options

This query splits revenue into two halves of the selected date range. You can customize it in the following ways:

Change the metric analyzed

• Replace order_revenue with other measures like orders_quantity or number_of_customers for different types of comparisons.

Shift from midpoint to other thresholds

• Instead of a 50/50 split, use a different percentage by adjusting the midpoint expression — e.g., use @start_date + 0.3 * (end - start) to compare early vs. late performance.

Break down by campaign or channel

• Add fields like campaign_name or channel to the SELECT and GROUP BY clauses in the base CTE to analyze trends by segment.