counter_diff analytic window function

Applies to: Databricks SQL Databricks Runtime 19.0 and above

Computes the differences between consecutive cumulative counter values in a time series, converting cumulative counters to delta format.

Syntax

counter_diff( value [, start_time] ) OVER clause

Arguments

• value: A non-negative numeric expression that represents a cumulative counter.
• start_time: An optional TIMESTAMP or TIMESTAMP_NTZ expression that indicates when the counter was last reset to zero. Use it to signal counter resets independently of the counter value.
• OVER clause: The clause that describes the windowing.
  • Use PARTITION BY to separate independent counters. Typical partition columns are the metric name and any attributes tied to the metric.
  • Use ORDER BY to define the order. This is typically a timestamp column tied to the metric.

Returns

The result type matches value.

Returns the difference between the current counter value and the previous counter value according to the order defined by ORDER BY in the OVER clause.

The function handles the following cases:

• If value is NULL, the result is NULL, and the row is not used when computing differences for later rows.
• If value is negative, or start_time is earlier than the previous row's start_time, Databricks raises an error.
• For the first row in the partition, the result is NULL.
• On a counter reset, the result is NULL.
• If either the previous or the current start_time is NULL, that value is not used to detect counter resets.

Databricks detects a counter reset when either of the following is true:

• The previous value is greater than the current value.
• start_time is provided and is greater than the previous row's start_time.

You must provide an ORDER BY clause.

Common error conditions

• COUNTER_DIFF_NEGATIVE_COUNTER_VALUE
• COUNTER_DIFF_START_TIME_DECREASED
• WINDOW_FUNCTION_WITHOUT_OVER_CLAUSE
• WINDOW_FUNCTION_FRAME_NOT_ORDERED

Examples

SQL

> SELECT m, t, counter_diff(c) OVER (PARTITION BY m ORDER BY t)
    FROM VALUES
      ('http_requests', TIMESTAMP '2026-01-01T00:00:00', 100),
      ('http_requests', TIMESTAMP '2026-01-01T00:01:00', 200),
      ('http_requests', TIMESTAMP '2026-01-01T00:02:00', 400)
      AS tab(m, t, c);
 http_requests  2026-01-01 00:00:00  NULL
 http_requests  2026-01-01 00:01:00  100
 http_requests  2026-01-01 00:02:00  200

-- Counter reset detected by a decrease in value
> SELECT m, t, counter_diff(c) OVER (PARTITION BY m ORDER BY t)
    FROM VALUES
      ('http_requests', TIMESTAMP '2026-01-01T00:00:00', 100),
      ('http_requests', TIMESTAMP '2026-01-01T00:01:00', 200),
      ('http_requests', TIMESTAMP '2026-01-01T00:02:00', 400),
      ('http_requests', TIMESTAMP '2026-01-01T00:04:00', 50)
      AS tab(m, t, c);
 http_requests  2026-01-01 00:00:00  NULL
 http_requests  2026-01-01 00:01:00  100
 http_requests  2026-01-01 00:02:00  200
 http_requests  2026-01-01 00:04:00  NULL

-- Counter reset detected by a change in start_time
> SELECT m, t, counter_diff(c, st) OVER (PARTITION BY m ORDER BY t)
    FROM VALUES
      ('http_requests', TIMESTAMP '2026-01-01T00:01:00', TIMESTAMP '2026-01-01T00:00:00', 100),
      ('http_requests', TIMESTAMP '2026-01-01T00:02:00', TIMESTAMP '2026-01-01T00:00:00', 200),
      ('http_requests', TIMESTAMP '2026-01-01T00:04:00', TIMESTAMP '2026-01-01T00:02:31', 400)
      AS tab(m, t, st, c);
 http_requests  2026-01-01 00:01:00  NULL
 http_requests  2026-01-01 00:02:00  100
 http_requests  2026-01-01 00:04:00  NULL

Related functions

• lag analytic window function
• lead analytic window function
• Window functions