# Math operations with lag()

**URL:** <https://community.questdb.com/t/math-operations-with-lag/820>\
**Category:** Community\
**Tags:** sql, question\
**Created:** [April 28, 2025, 3:43pm UTC](https://community.questdb.com/t/math-operations-with-lag/820 "2025-04-28T15:43:21Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![vins](https://avatars.discourse-cdn.com/v4/letter/v/a88e57/32.png) [@vins](https://community.questdb.com/u/vins)\
**Post date:** [April 28, 2025, 3:43pm UTC](https://community.questdb.com/t/math-operations-with-lag/820/1 "2025-04-28T15:43:21Z")

</div>

i have a questdb 8.3.0 and i want to graph a typical snmp counter containing network incoming traffic (ifHCInOctets).

if i run this query:

```auto
SELECT 
    timestamp,
    ifDescr,
    ifHCInOctets,
    (LAG(ifHCInOctets,1)) OVER (PARTITION BY ifDescr ORDER BY timestamp) as previous
FROM snmpInterface
WHERE ifDescr = 'ether8' AND timestamp > 1745792550000000;

```

i get the current value and the previous one:

 ![image](https://canada1.discourse-cdn.com/flex004/uploads/questdb/original/1X/2662167fdf8041d4d78c60e1b6213bfb5d4aabe9.png)

however, i want to get the substracted value (ifHCInOctets - LAG(…)) and I can’t get it ☹

this query works with InfluxDB 3.0:

```auto
SELECT ("ifHCInOctets" - LAG("ifHCInOctets", 1) OVER (PARTITION BY "ifDescr" ORDER BY time
  )) AS in, ("ifHCOutOctets" - LAG("ifHCOutOctets", 1) OVER (PARTITION BY "ifDescr" ORDER BY time
  )) AS out, time FROM "snmpInterface" WHERE "ifDescr" == 'ether8' AND "time" >= $ __timeFrom AND "time" <= $__ timeTo ORDER BY "time" ASC 

```

is it possible to have the same query with QuestDB ? how ? thanks.

---

<div class="post-metadata">

**Author:** ![nwoolmer](https://yyz2.discourse-cdn.com/flex004/user_avatar/community.questdb.com/nwoolmer/32/24_2.png) [@nwoolmer](https://community.questdb.com/u/nwoolmer)\
**Post date:** [April 28, 2025, 3:44pm UTC](https://community.questdb.com/t/math-operations-with-lag/820/2 "2025-04-28T15:44:56Z")

</div>

Does it work using a subquery? i.e.

```sql
SELECT timestamp, ifDescr, ifHCInOctets - previous AS difference FROM (
    SELECT 
        timestamp,
        ifDescr,
        ifHCInOctets,
        (LAG(ifHCInOctets,1)) OVER (PARTITION BY ifDescr ORDER BY timestamp) as previous
    FROM snmpInterface
    WHERE ifDescr = 'ether8' AND timestamp > 1745792550000000
);

```

---

<div class="post-metadata">

**Author:** ![vins](https://avatars.discourse-cdn.com/v4/letter/v/a88e57/32.png) [@vins](https://community.questdb.com/u/vins)\
**Post date:** [April 29, 2025, 12:48am UTC](https://community.questdb.com/t/math-operations-with-lag/820/3 "2025-04-29T00:48:35Z")

</div>

yes, it does work. it’s slow, though ☹

both prometheus and influxdb have a function (rate() and non-negative-derivative(), respectively) that returns the change rate.

is there any way of doing this with questdb? i find a bit dirty/hacky to run subqueries or grafana transformations when other time series databases natively support this.

i love questdb, but with all the respects i think if questdb wants to compete with other time series databases it should provide features the others don’t have instead of lacking functionality.

---

<div class="post-metadata">

**Author:** ![nwoolmer](https://yyz2.discourse-cdn.com/flex004/user_avatar/community.questdb.com/nwoolmer/32/24_2.png) [@nwoolmer](https://community.questdb.com/u/nwoolmer)\
**Post date:** [April 29, 2025, 12:51am UTC](https://community.questdb.com/t/math-operations-with-lag/820/4 "2025-04-29T00:51:06Z")

</div>

What do you mean by slow? Subqueries are very light in QuestDB. Please can you run it with `EXPLAIN` and post the plan here?

Feel free to open a feature request for a rate function on GitHub 🙂

---

<div class="post-metadata">

**Author:** ![vins](https://avatars.discourse-cdn.com/v4/letter/v/a88e57/32.png) [@vins](https://community.questdb.com/u/vins)\
**Post date:** [May 2, 2025, 3:35pm UTC](https://community.questdb.com/t/math-operations-with-lag/820/5 "2025-05-02T15:35:56Z")

</div>

yeah, it was slow because a network issue i had.

anyway, now it’s working fine, but when the counters restart, there’s a spike.

 ![image](https://canada1.discourse-cdn.com/flex004/uploads/questdb/original/1X/9e9fedbd5cf0db745b31ac50824959bcb8121b6a.png)

this is the query i’m currently using:

```auto
SELECT timestamp,
  host,
  AccessRequests - previous AS "Requests" FROM (
    SELECT timestamp,
      AccessRequests,
      host,
      (LAG(AccessRequests,1)) OVER (PARTITION BY host ORDER BY timestamp) as previous
    FROM "myService"
    WHERE $__timeFilter(timestamp) SAMPLE BY $__sampleByInterval ALIGN TO CALENDAR ORDER BY timestamp ASC
);

```

any clue how to remove the spikes?

---

<div class="post-metadata">

**Author:** ![nwoolmer](https://yyz2.discourse-cdn.com/flex004/user_avatar/community.questdb.com/nwoolmer/32/24_2.png) [@nwoolmer](https://community.questdb.com/u/nwoolmer)\
**Post date:** [May 2, 2025, 4:45pm UTC](https://community.questdb.com/t/math-operations-with-lag/820/6 "2025-05-02T16:45:25Z")

</div>

I suppose that is a true reflection of the delta when a counter goes from 100k to zero.

Best bet is probably just to clamp it:

```auto
SELECT host, greatest(AccessRequests - previous, 0) AS Requests -- etc. 

```

---

<div class="post-metadata">

**Author:** ![vins](https://avatars.discourse-cdn.com/v4/letter/v/a88e57/32.png) [@vins](https://community.questdb.com/u/vins)\
**Post date:** [June 12, 2025, 6:57am UTC](https://community.questdb.com/t/math-operations-with-lag/820/7 "2025-06-12T06:57:31Z")

</div>

I have upgraded to 8.3.3 and the query stopped working. now i’m getting “pq: at least one aggregation function must be present in ‘select’ clause”. i roll back to 8.2.3 and it works fine.

this is the query i’m using:

```auto
SELECT timestamp,
  host,
  AccessRequests - previous AS "Requests" FROM (
    SELECT timestamp,
      AccessRequests,
      host,
      (LAG(AccessRequests,1)) OVER (PARTITION BY host ORDER BY timestamp) as previous
    FROM "myService"
    WHERE $__timeFilter(timestamp) SAMPLE BY $__sampleByInterval ALIGN TO CALENDAR ORDER BY timestamp ASC
);

```

what has changed? how can i fix it? i’ve tried with avg() but now i get “pq: Window function is not allowed in context of aggregation. Use sub-query”. any ideas?

---

<div class="post-metadata">

**Author:** ![nwoolmer](https://yyz2.discourse-cdn.com/flex004/user_avatar/community.questdb.com/nwoolmer/32/24_2.png) [@nwoolmer](https://community.questdb.com/u/nwoolmer)\
**Post date:** [June 12, 2025, 7:03am UTC](https://community.questdb.com/t/math-operations-with-lag/820/8 "2025-06-12T07:03:59Z")

</div>

`SAMPLE BY` is used for time-based aggregations. Essentially, a `GROUP BY` but with specified time buckets.

What is the purpose of `SAMPLE BY` in this query? I guess it worked before because we translated it to a `GROUP BY` for you. In that case, a `GROUP BY` with only keys, no aggregates, is a `DISTINCT`.

But taking the delta between rows doesn’t require sampling.

---

<div class="post-metadata">

**Author:** ![vins](https://avatars.discourse-cdn.com/v4/letter/v/a88e57/32.png) [@vins](https://community.questdb.com/u/vins)\
**Post date:** [June 12, 2025, 11:44am UTC](https://community.questdb.com/t/math-operations-with-lag/820/9 "2025-06-12T11:44:49Z")

</div>

i have many RADIUS servers (tag “host”). AccessRequests is a counter, so i just want to draw the increase (non-negative, to avoid spikes due to counter reset).

with prometheus is very easy with rate(), with influxdb is very easy with non-negative-derivative() and non-negative-difference(), but with questdb is a nightmare. I’m a bit frustrated it requires subqueries and the fact it can’t be done in a single query makes it less flexible/powerful/attractive than other monitoring solutions.

---

<div class="post-metadata">

**Author:** ![nwoolmer](https://yyz2.discourse-cdn.com/flex004/user_avatar/community.questdb.com/nwoolmer/32/24_2.png) [@nwoolmer](https://community.questdb.com/u/nwoolmer)\
**Post date:** [June 12, 2025, 11:47am UTC](https://community.questdb.com/t/math-operations-with-lag/820/10 "2025-06-12T11:47:21Z")

</div>

Please can you post your schema and your prometheus/influx query, I’ll help translate it for you.

Subqueries are very lightweight with QuestDB and are common ways to force optimisations for you query. That being said, we could add a `rate()` function to simplify things in future.

Let’s figure out the ideal solution and I’ll make an issue 🙂

---

<div class="post-metadata">

**Author:** ![vins](https://avatars.discourse-cdn.com/v4/letter/v/a88e57/32.png) [@vins](https://community.questdb.com/u/vins)\
**Post date:** [June 13, 2025, 5:49pm UTC](https://community.questdb.com/t/math-operations-with-lag/820/11 "2025-06-13T17:49:52Z")

</div>

sure, this is the schema:

| column | type | indexed | indexBlockCapacity | symbolCached | symbolCapacity | designated | upsertKey |
| --- | --- | --- | --- | --- | --- | --- | --- |
| host | SYMBOL | false | 0 | true | 256 | false | false |
| AccessRequests | DOUBLE | false | 0 | false | 0 | false | false |
| AccessAccepts | DOUBLE | false | 0 | false | 0 | false | false |
| AccessRejects | DOUBLE | false | 0 | false | 0 | false | false |
| AccessChallenges | DOUBLE | false | 0 | false | 0 | false | false |
| AuthResponses | DOUBLE | false | 0 | false | 0 | false | false |
| AuthDuplicateRequests | DOUBLE | false | 0 | false | 0 | false | false |
| AuthMalformedRequests | DOUBLE | false | 0 | false | 0 | false | false |
| AuthInvalidRequests | DOUBLE | false | 0 | false | 0 | false | false |
| AuthDroppedRequests | DOUBLE | false | 0 | false | 0 | false | false |
| AuthUnknownTypes | DOUBLE | false | 0 | false | 0 | false | false |
| timestamp | TIMESTAMP | false | 0 | false | 0 | true | false |

this is the query i’m using with QuestDB:

```auto
SELECT timestamp,
  host,
  AccessRequests - previous AS "Requests" FROM (
    SELECT timestamp,
      AccessRequests,
      host,
      (LAG(AccessRequests,1)) OVER (PARTITION BY host ORDER BY timestamp) as previous
    FROM "freeradius"
    WHERE $__timeFilter(timestamp) SAMPLE BY $__sampleByInterval ALIGN TO CALENDAR ORDER BY timestamp ASC
);

```

but as i said, it returns the “pq: at least one aggregation function must be present in ‘select’ clause” error

with influxdb, the query is this simple:

```auto
SELECT non_negative_derivative(mean("AccessRequests")) FROM "freeradius" WHERE $timeFilter GROUP BY time($__interval) fill(null)

```

non\_negative\_derivative() calculates the derivative (delta time is considered) while non\_negative\_difference() just calculates the difference with the previous value no matter the elapsed time.

---

<div class="post-metadata">

**Author:** ![nwoolmer](https://yyz2.discourse-cdn.com/flex004/user_avatar/community.questdb.com/nwoolmer/32/24_2.png) [@nwoolmer](https://community.questdb.com/u/nwoolmer)\
**Post date:** [June 15, 2025, 3:33pm UTC](https://community.questdb.com/t/math-operations-with-lag/820/12 "2025-06-15T15:33:44Z")

</div>

Hey, thank you. I agree with you, it is not convenient at the moment.

The window functions we have are strictly more powerful, but also more verbose.

I have this query for difference/derivative:

```sql
DECLARE @15m := (15 * 60 * 1E6)
WITH averaged AS (
  SELECT timestamp, avg(price) as price
  FROM trades
  WHERE symbol = 'ETH-USD'
  SAMPLE BY 15m
), lagged AS (
  SELECT
   timestamp as curr_time,
   lag(timestamp, 1) OVER (ORDER BY timestamp) AS prev_time,
   price as curr_price,
   lag(price, 1) OVER (ORDER BY timestamp) as prev_price
   FROM averaged
), diffed AS (
  SELECT 
  curr_time,
  curr_price,
  curr_price - prev_price as price_delta,
  curr_time - prev_time as time_delta
  FROM lagged
)
SELECT curr_time, curr_price, price_delta as difference, price_delta / (time_delta / @15m) as derivative
FROM diffed;

```

This query should give you something close to what you want. You can remove the `avg` and `SAMPLE BY`, and instead calculate the point-by-point values, no grouping required. You can add an `abs` and/or `greatest` to handle the ‘non-negative’ part.

I will explain why it requires the multiple subqueries, based on the current functionality we have.

1. The InfluxQL query allows you to nest aggregates like `non_negative_derivative(mean("foo"))` whereas QuestDB doesn’t. We could resolve this by adding `CURSOR` variants of the functions, or by automatically rewriting them into the subquery version (we do this in other cases).
2. Window functions like `lag` do not support inline arithmetic, this a limitation of the implementation. Therefore, the delta calculation has to be lifted into an outer query.
3. Since the function is not a ‘one-shot’, you have to do the final derivative arithemetic yourself.

Therefore you end up with four selects - the sample by, then the window, then the difference, and then the final derivative. You could condense the last two if you wanted.

I will feed this back and see what we can do to make things more convenient. It may be that the shortest route is just adding a few functions like `derivative`, `rate`. But in the long run, making these things a bit more flexible would be great.

P.S There may be better ways than the example I have given.

---

<div class="post-metadata">

**Author:** ![vins](https://avatars.discourse-cdn.com/v4/letter/v/a88e57/32.png) [@vins](https://community.questdb.com/u/vins)\
**Post date:** [June 16, 2025, 12:07pm UTC](https://community.questdb.com/t/math-operations-with-lag/820/13 "2025-06-16T12:07:42Z")

</div>

splendid! that query works like a charm!

```DECLARE
WITH averaged AS (
  SELECT timestamp, avg(AccessRequests) as AccessRequests
  FROM freeradius
  WHERE $__timeFilter(timestamp)
  SAMPLE BY 15m
), lagged AS (
  SELECT
   timestamp as curr_time,
   lag(timestamp, 1) OVER (ORDER BY timestamp) AS prev_time,
   AccessRequests as curr_AccessRequests,
   lag(AccessRequests, 1) OVER (ORDER BY timestamp) as prev_AccessRequests
   FROM averaged
), diffed AS (
  SELECT 
  curr_time,
  curr_AccessRequests,
  curr_AccessRequests - prev_AccessRequests as AccessRequests_delta,
  curr_time - prev_time as time_delta
  FROM lagged
)
SELECT curr_time, AccessRequests_delta / (time_delta / @15m) as Requests
FROM diffed;

```

however, data is sampled to fixed intervals of 15m regardless of the “zoom” level. if i select a 30-day time window, a fixed 15m interval is overkilling, and on the other hand, if i select a 1-hour time window, 15m interval would only display 4 points; not enough resolution.  
i seem to remember grafana has an inteval macro ($\_\_interval) that automatically sets a suitable interval depending on the selected time window. i tried to replace the “15m” macro directly with $\_\_interval, but it didn’t work. any idea how to make it work? (sorry if it’s slightly offtopic!)

---

<div class="post-metadata">

**Author:** ![vins](https://avatars.discourse-cdn.com/v4/letter/v/a88e57/32.png) [@vins](https://community.questdb.com/u/vins)\
**Post date:** [June 16, 2025, 12:07pm UTC](https://community.questdb.com/t/math-operations-with-lag/820/14 "2025-06-16T12:07:52Z")

</div>

also, that query is showing a single value (average). how can i show the AccessRequests per host? i tried to add the host column:

```DECLARE
WITH averaged AS (
  SELECT timestamp, host, avg(AccessRequests) as AccessRequests
  FROM freeradius
  WHERE $__timeFilter(timestamp)
  SAMPLE BY 5m
), lagged AS (
  SELECT
   timestamp as curr_time, host,
   lag(timestamp, 1) OVER (ORDER BY timestamp) AS prev_time,
   AccessRequests as curr_AccessRequests,
   lag(AccessRequests, 1) OVER (ORDER BY timestamp) as prev_AccessRequests
   FROM averaged
), diffed AS (
  SELECT 
  host, curr_time,
  curr_AccessRequests,
  curr_AccessRequests - prev_AccessRequests as AccessRequests_delta,
  curr_time - prev_time as time_delta
  FROM lagged
)
SELECT host, curr_time, AccessRequests_delta / (time_delta / @5m) as Requests
FROM diffed

```

but it produces a broken graph:

 ![image](https://canada1.discourse-cdn.com/flex004/uploads/questdb/original/1X/24fcddcf882058c1ffdd9141295371fc9cddad93.png)

i tried to use group by host, but it’s complaining i need an aggregate function, which i don’t understand because we are already using avg()

---

<div class="post-metadata">

**Author:** ![vins](https://avatars.discourse-cdn.com/v4/letter/v/a88e57/32.png) [@vins](https://community.questdb.com/u/vins)\
**Post date:** [June 16, 2025, 12:07pm UTC](https://community.questdb.com/t/math-operations-with-lag/820/15 "2025-06-16T12:07:57Z")

</div>

alright, i got how to get the automatic interval. for anyone interested:

```auto
WITH averaged AS (
  SELECT timestamp, avg(AccessRequests) as AccessRequests
  FROM freeradius
  WHERE $__timeFilter(timestamp)
  SAMPLE BY $__interval
), lagged AS (
  SELECT
   timestamp as curr_time,
   lag(timestamp, 1) OVER (ORDER BY timestamp) AS prev_time,
   AccessRequests as curr_AccessRequests,
   lag(AccessRequests, 1) OVER (ORDER BY timestamp) as prev_AccessRequests
   FROM averaged
), diffed AS (
  SELECT 
  curr_time,
  curr_AccessRequests,
  curr_AccessRequests - prev_AccessRequests as AccessRequests_delta,
  curr_time - prev_time as time_delta
  FROM lagged
)
SELECT curr_time, AccessRequests_delta / (time_delta / ($__interval_ms * 1000)) as Requests
FROM diffed;

```

how i’m trying to figure out how to get the rate growth per host 😃

---

<div class="post-metadata">

**Author:** ![nwoolmer](https://yyz2.discourse-cdn.com/flex004/user_avatar/community.questdb.com/nwoolmer/32/24_2.png) [@nwoolmer](https://community.questdb.com/u/nwoolmer)\
**Post date:** [June 16, 2025, 12:15pm UTC](https://community.questdb.com/t/math-operations-with-lag/820/16 "2025-06-16T12:15:42Z")

</div>

You will need to ‘key’ the query. If you provide the `host` column, the query can be keyed and run for each symbol. Likewise for the window functions.

```sql
WITH averaged AS (
  SELECT timestamp, host, avg(AccessRequests) as AccessRequests
  FROM freeradius
  WHERE timestamp IN $__timeFilter(timestamp)
  SAMPLE BY $__interval
), lagged AS (
  SELECT
   timestamp as curr_time,
   host,
   lag(timestamp, 1) OVER (PARTITION BY host ORDER BY timestamp) AS prev_time,
   AccessRequests as curr_AccessRequests,
   lag(AccessRequests, 1) OVER (PARTITION BY host ORDER BY timestamp) as prev_AccessRequests
   FROM averaged
), diffed AS (
  SELECT 
  host,
  curr_time,
  curr_AccessRequests,
  curr_AccessRequests - prev_AccessRequests as AccessRequests_delta,
  curr_time - prev_time as time_delta
  FROM lagged
)
SELECT curr_time, host, AccessRequests_delta, AccessRequests_delta / (time_delta / ($__interval_ms * 1000)) as Requests
FROM diffed
ORDER BY curr_time;

```

We will shortly be releasing `PIVOT`, this will allow you to have a column per symbol i.e

`timestamp, host1, host2, host3 ....`

That will make it easy to plot on a Grafana time-series chart.

---

<div class="post-metadata">

**Author:** ![vins](https://avatars.discourse-cdn.com/v4/letter/v/a88e57/32.png) [@vins](https://community.questdb.com/u/vins)\
**Post date:** [June 16, 2025, 3:00pm UTC](https://community.questdb.com/t/math-operations-with-lag/820/17 "2025-06-16T15:00:41Z")

</div>

there’s something odd.

when i run this query (either in grafana explorer or as a dashboard panel):

```WITH
  SELECT timestamp, avg(AccessRequests) as requests
  FROM freeradius
  WHERE $__timeFilter(timestamp)
  SAMPLE BY $__interval
), lagged AS (
  SELECT
   timestamp as curr_time,
   lag(timestamp, 1) OVER (ORDER BY timestamp) AS prev_time,
   requests as curr_requests,
   lag(requests, 1) OVER (ORDER BY timestamp) as prev_requests
   FROM averaged
), diffed AS (
  SELECT 
  curr_time,
  curr_requests,
  curr_requests - prev_requests as requests_delta,
  curr_time - prev_time as time_delta
  FROM lagged
)
SELECT curr_time, requests_delta / (time_delta / ($__interval_ms * 1000)) as Requests
FROM diffed

```

sometimes i get a graph like this (which is the expected graph) :

 ![image](https://canada1.discourse-cdn.com/flex004/uploads/questdb/original/1X/302e8670e455b657c52e684d76e3efb3a41c7050.png)

but sometimes i get something like this:

 ![image](https://canada1.discourse-cdn.com/flex004/uploads/questdb/original/1X/e75a2c9b9eee074e0d5972b61869b2c1aab30771.png)

it’s quite random. i just refresh the graph and sometimes i get the correct one, sometimes the wrong one. it looks like the last value gives a wrong delta value either for timestamp or for accessrequests value.

---

<div class="post-metadata">

**Author:** ![nwoolmer](https://yyz2.discourse-cdn.com/flex004/user_avatar/community.questdb.com/nwoolmer/32/24_2.png) [@nwoolmer](https://community.questdb.com/u/nwoolmer)\
**Post date:** [June 16, 2025, 3:02pm UTC](https://community.questdb.com/t/math-operations-with-lag/820/18 "2025-06-16T15:02:56Z")

</div>

I suppose you are grouping on an average over a time range. If that final bucket does not have complete data, the average may be lower and skew the dataset?

Can you share the raw table data for the wonky graph?

---

<div class="post-metadata">

**Author:** ![vins](https://avatars.discourse-cdn.com/v4/letter/v/a88e57/32.png) [@vins](https://community.questdb.com/u/vins)\
**Post date:** [June 16, 2025, 4:28pm UTC](https://community.questdb.com/t/math-operations-with-lag/820/19 "2025-06-16T16:28:35Z")

</div>

Sure. This JSON contains the request and also the returning dataset for a “correct” query:

```auto
{
  "request": {
    "url": "api/ds/query?ds_type=questdb-questdb-datasource&requestId=explore_xx0",
    "method": "POST",
    "data": {
      "queries": [
        {
          "refId": "A",
          "datasource": {
            "type": "questdb-questdb-datasource",
            "uid": "denc7cxgco2rkc"
          },
          "queryType": "sql",
          "rawSql": "WITH averaged AS (\r\n SELECT timestamp, avg(AccessRequests) as requests\r\n FROM freeradius\r\n WHERE $__timeFilter(timestamp)\r\n SAMPLE BY 200ms\r\n), lagged AS (\r\n SELECT\r\n timestamp as curr_time,\r\n lag(timestamp, 1) OVER (ORDER BY timestamp) AS prev_time,\r\n requests as curr_requests,\r\n lag(requests, 1) OVER (ORDER BY timestamp) as prev_requests\r\n FROM averaged\r\n), diffed AS (\r\n SELECT \r\n curr_time,\r\n curr_requests,\r\n curr_requests - prev_requests as requests_delta,\r\n curr_time - prev_time as time_delta\r\n FROM lagged\r\n)\r\nSELECT curr_time, requests_delta / (time_delta / (200 * 1000)) as Requests\r\nFROM diffed",
          "meta": {
            "builderOptions": {
              "mode": "list",
              "fields": [],
              "limit": "",
              "timeField": ""
            },
            "timezone": "Europe/Paris"
          },
          "format": 1,
          "selectedFormat": 1,
          "datasourceId": 3,
          "intervalMs": 200,
          "maxDataPoints": 1070
        }
      ],
      "from": "1750086946250",
      "to": "1750087246250"
    },
    "hideFromInspector": false
  },
  "response": {
    "results": {
      "A": {
        "status": 200,
        "frames": [
          {
            "schema": {
              "name": "A",
              "refId": "A",
              "meta": {
                "typeVersion": [
                  0,
                  0
                ],
                "preferredVisualisationType": "table",
                "executedQueryString": "WITH averaged AS (\r\n SELECT timestamp, avg(AccessRequests) as requests\r\n FROM freeradius\r\n WHERE timestamp >= cast(1750086946250000 as timestamp) AND timestamp <= cast(1750087246250000 as timestamp)\r\n SAMPLE BY 200ms\r\n), lagged AS (\r\n SELECT\r\n timestamp as curr_time,\r\n lag(timestamp, 1) OVER (ORDER BY timestamp) AS prev_time,\r\n requests as curr_requests,\r\n lag(requests, 1) OVER (ORDER BY timestamp) as prev_requests\r\n FROM averaged\r\n), diffed AS (\r\n SELECT \r\n curr_time,\r\n curr_requests,\r\n curr_requests - prev_requests as requests_delta,\r\n curr_time - prev_time as time_delta\r\n FROM lagged\r\n)\r\nSELECT curr_time, requests_delta / (time_delta / (200 * 1000)) as Requests\r\nFROM diffed"
              },
              "fields": [
                {
                  "name": "curr_time",
                  "type": "time",
                  "typeInfo": {
                    "frame": "time.Time",
                    "nullable": true
                  }
                },
                {
                  "name": "Requests",
                  "type": "number",
                  "typeInfo": {
                    "frame": "float64",
                    "nullable": true
                  }
                }
              ]
            },
            "data": {
              "values": [
                [
                  1750086950000,
                  1750086960000,
                  1750086970000,
                  1750086980000,
                  1750086990000,
                  1750087000000,
                  1750087010000,
                  1750087020000,
                  1750087030000,
                  1750087040000,
                  1750087050000,
                  1750087060000,
                  1750087070000,
                  1750087080000,
                  1750087090000,
                  1750087100000,
                  1750087110000,
                  1750087120000,
                  1750087130000,
                  1750087140000,
                  1750087150000,
                  1750087160000,
                  1750087170000,
                  1750087180000,
                  1750087190000,
                  1750087200000,
                  1750087210000,
                  1750087220000,
                  1750087230000,
                  1750087240000
                ],
                [
                  null,
                  0.02,
                  0,
                  0,
                  0.05,
                  0,
                  0,
                  0.01,
                  0.05,
                  0.03,
                  0.02,
                  0.05,
                  0.01,
                  0.04,
                  0.01,
                  0.07,
                  0.04,
                  0.05,
                  0.02,
                  0.04,
                  0.01,
                  0,
                  0.01,
                  0.06,
                  0.02,
                  0.03,
                  0.01,
                  0.04,
                  0.03,
                  0.1
                ]
              ]
            }
          }
        ],
        "refId": "A"
      }
    }
  }
}

```

and this one for the wrong query (note the -2.74) at the end:

```auto
{
  "request": {
    "url": "api/ds/query?ds_type=questdb-questdb-datasource&requestId=explore_xx0",
    "method": "POST",
    "data": {
      "queries": [
        {
          "refId": "A",
          "datasource": {
            "type": "questdb-questdb-datasource",
            "uid": "xxx"
          },
          "queryType": "sql",
          "rawSql": "WITH averaged AS (\r\n SELECT timestamp, avg(AccessRequests) as requests\r\n FROM freeradius\r\n WHERE $__timeFilter(timestamp)\r\n SAMPLE BY 200ms\r\n), lagged AS (\r\n SELECT\r\n timestamp as curr_time,\r\n lag(timestamp, 1) OVER (ORDER BY timestamp) AS prev_time,\r\n requests as curr_requests,\r\n lag(requests, 1) OVER (ORDER BY timestamp) as prev_requests\r\n FROM averaged\r\n), diffed AS (\r\n SELECT \r\n curr_time,\r\n curr_requests,\r\n curr_requests - prev_requests as requests_delta,\r\n curr_time - prev_time as time_delta\r\n FROM lagged\r\n)\r\nSELECT curr_time, requests_delta / (time_delta / (200 * 1000)) as Requests\r\nFROM diffed",
          "meta": {
            "builderOptions": {
              "mode": "list",
              "fields": [],
              "limit": "",
              "timeField": ""
            },
            "timezone": "Europe/Paris"
          },
          "format": 1,
          "selectedFormat": 1,
          "datasourceId": 3,
          "intervalMs": 200,
          "maxDataPoints": 1070
        }
      ],
      "from": "1750086784632",
      "to": "1750087084632"
    },
    "hideFromInspector": false
  },
  "response": {
    "results": {
      "A": {
        "status": 200,
        "frames": [
          {
            "schema": {
              "name": "A",
              "refId": "A",
              "meta": {
                "typeVersion": [
                  0,
                  0
                ],
                "preferredVisualisationType": "table",
                "executedQueryString": "WITH averaged AS (\r\n SELECT timestamp, avg(AccessRequests) as requests\r\n FROM freeradius\r\n WHERE timestamp >= cast(1750086784632000 as timestamp) AND timestamp <= cast(1750087084632000 as timestamp)\r\n SAMPLE BY 200ms\r\n), lagged AS (\r\n SELECT\r\n timestamp as curr_time,\r\n lag(timestamp, 1) OVER (ORDER BY timestamp) AS prev_time,\r\n requests as curr_requests,\r\n lag(requests, 1) OVER (ORDER BY timestamp) as prev_requests\r\n FROM averaged\r\n), diffed AS (\r\n SELECT \r\n curr_time,\r\n curr_requests,\r\n curr_requests - prev_requests as requests_delta,\r\n curr_time - prev_time as time_delta\r\n FROM lagged\r\n)\r\nSELECT curr_time, requests_delta / (time_delta / (200 * 1000)) as Requests\r\nFROM diffed"
              },
              "fields": [
                {
                  "name": "curr_time",
                  "type": "time",
                  "typeInfo": {
                    "frame": "time.Time",
                    "nullable": true
                  }
                },
                {
                  "name": "Requests",
                  "type": "number",
                  "typeInfo": {
                    "frame": "float64",
                    "nullable": true
                  }
                }
              ]
            },
            "data": {
              "values": [
                [
                  1750086790000,
                  1750086800000,
                  1750086810000,
                  1750086820000,
                  1750086830000,
                  1750086840000,
                  1750086850000,
                  1750086860000,
                  1750086870000,
                  1750086880000,
                  1750086890000,
                  1750086900000,
                  1750086910000,
                  1750086920000,
                  1750086930000,
                  1750086940000,
                  1750086950000,
                  1750086960000,
                  1750086970000,
                  1750086980000,
                  1750086990000,
                  1750087000000,
                  1750087010000,
                  1750087020000,
                  1750087030000,
                  1750087040000,
                  1750087050000,
                  1750087060000,
                  1750087070000,
                  1750087080000
                ],
                [
                  null,
                  0.01,
                  0.04,
                  0.04,
                  0.04,
                  0.04,
                  0.04,
                  0.01,
                  0.08,
                  0,
                  0.02,
                  0.05,
                  0.02,
                  0.07,
                  0.06,
                  0.03,
                  0.05,
                  0.02,
                  0,
                  0,
                  0.05,
                  0,
                  0,
                  0.01,
                  0.05,
                  0.03,
                  0.02,
                  0.05,
                  0.01,
                  -2.74
                ]
              ]
            }
          }
        ],
        "refId": "A"
      }
    }
  }
}

```

---

<div class="post-metadata">

**Author:** ![nwoolmer](https://yyz2.discourse-cdn.com/flex004/user_avatar/community.questdb.com/nwoolmer/32/24_2.png) [@nwoolmer](https://community.questdb.com/u/nwoolmer)\
**Post date:** [June 16, 2025, 4:30pm UTC](https://community.questdb.com/t/math-operations-with-lag/820/20 "2025-06-16T16:30:51Z")

</div>

> [@vins](#):
>
> `WITH averaged AS (\r\n SELECT timestamp, avg(AccessRequests) as requests\r\n FROM freeradius\r\n WHERE timestamp >= cast(1750086784632000 as timestamp) AND timestamp <= cast(1750087084632000 as timestamp)\r\n SAMPLE BY 200ms\r\n), lagged AS (\r\n SELECT\r\n timestamp as curr_time,\r\n lag(timestamp, 1) OVER (ORDER BY timestamp) AS prev_time,\r\n requests `

The query isn’t keyed, so it is global across all `hosts`. Maybe one `host` crashes during the final bucket and messes up the numbers. Perhaps try running a keyed version and see if it is isolated to one host.

[Next page](https://community.questdb.com/t/math-operations-with-lag/820.md?page=2)