Calculate data reception frequency table

I needed to calculate a table of data reception frequencies.

This is the schema:

CREATE TABLE ‘measurements’ (
index_time TIMESTAMP,
val DOUBLE
) timestamp(index_time) PARTITION BY DAY WAL
DEDUP UPSERT KEYS(index_time);

I used this query:
(With small modifications it can also be useful to check for any reception failures)

SELECT
(t1-t2)::long / 1000000 AS tim, count(*) AS cnt
FROM (
SELECT
timestamp_ceil(‘s’, index_time) AS t1,
lag(timestamp_ceil(‘s’, index_time)) OVER (ORDER BY index_time) AS t2
FROM measurements
) w1
WHERE t1-t2 IS NOT NULL
GROUP BY tim
ORDER BY cnt DESC
;

the result is:
(tim=seconds, cnt=number of rows)

While writing the query I noticed a couple of things:
A) The lag() function cannot be used in a mathematical operation
ex: timestamp_ceil(‘s’, index_time) - lag(timestamp_ceil(‘s’, index_time)) OVER (ORDER BY index_time)

B)The lag() function always returns a double type field, no matter what field is read.

I hope this can be useful to someone.

Thanks

Re: A, subquery is the way to go to perform arithmetic operations on the LAG value, which you have found!

Re: B, Indeed, LAG only supports DOUBLE at the moment: LAG/LEAD functions do not support long data type · Issue #5415 · questdb/questdb · GitHub

This is also true for some other window functions: First_value should support other datatypes, specifically timestamp · Issue #4395 · questdb/questdb · GitHub

Ok, I missed some posts.
Thanks