Hi! Can somebody point me in the direction on how would be the fastest/smartest way to calculate diffs between rows (per column)? Is this possible in pinot via transformation or does this only work within sql (for example with "lead" functions in the query)?

Hi I don't believe it's possible to use a transformation so likely needs to be via SQL, something along those lines should work using Multi-stage query engine (v2)

SELECT
  column1,
  column2,
  column3,
  column4,
  (column1 - LAG(column1) OVER (ORDER BY timestamp_column)) AS column1_diff,
  (column2 - LAG(column2) OVER (ORDER BY timestamp_column)) AS column2_diff,
  (column3 - LAG(column3) OVER (ORDER BY timestamp_column)) AS column3_diff,
  (column4 - LAG(column4) OVER (ORDER BY timestamp_column)) AS column4_diff
FROM
  my_table
ORDER BY
  timestamp_column;

thank you! I will give it a shot!

I checked the box Use Multi-Stage Engine in the SQL Editor

and still got the error? possibly lag isn't supported yet

Yes.

SELECT 
    current_row."timestamp",
    current_row.cpu_lat_running AS diff
FROM 
    (SELECT 
        "timestamp", 
        cpu_lat_running, 
        ROW_NUMBER() OVER (ORDER BY "timestamp") AS row_num 
     FROM 
        piaas_perf_icpu
    ) current_row
JOIN 
    (SELECT 
        "timestamp", 
        cpu_lat_running, 
        ROW_NUMBER() OVER (ORDER BY "timestamp") AS row_num 
     FROM 
        piaas_perf_icpu
    ) previous_row
ON 
    current_row.row_num = previous_row.row_num + 1;

This works (with v2). But it is a rather huge approach. Nearly all of my columns are incremental values.

Yup, window() are a nice to have and we working towards getting them supported. But no timelines as of yet.