Using LAG in SQL returns 'unqualified name' error

[lara-bellatin]

Describe the bug
Using LAG to compare results with previous time periods returns the following error:

Query 1 ERROR at Line 1: : ERROR:  Error during rewrite: Error during planning: No field with unqualified name 'LAG(SUM(source.importeVenta),Int64(1)) PARTITION BY [#source.cliente] ORDER BY [#source.mes ASC NULLS LAST]'. Valid fields are 'LAG(SUM(source.importeVenta),Int64(1)) PARTITION BY [#source.cliente AS cliente] ORDER BY [#source.mes AS mes ASC NULLS LAST]', 'source.mes', 'source.cliente', 'SUM(source.importeVenta)'.. Please check logs for additional information.
QUERY: SELECT "source"."mes" AS "mes", "source"."cliente" AS "cliente", SUM("source"."importeVenta") AS "sum", LAG(SUM("source"."importeVenta"), 1) OVER (PARTITION BY "source"."cliente" ORDER BY "source"."mes" ASC) AS "Sum of ImporteVenta (anterior año)", (CAST(SUM("source"."importeVenta") AS FLOAT) / NULLIF(LAG(SUM("source"."importeVenta"), 1) OVER (PARTITION BY "source"."cliente" ORDER BY "source"."mes" ASC), 0)) - 1 AS "Sum of ImporteVenta (% vs anterior año)" FROM (SELECT DATE_TRUNC('year', "Venta"."mesFactura") AS "mes", "Cliente"."nombre" AS "cliente", "Venta"."importeVenta" AS "importeVenta" FROM "Venta" LEFT JOIN "Cliente" ON "Venta"."__cubeJoinField" = "Cliente"."__cubeJoinField") AS "source" GROUP BY "source"."mes", "source"."cliente" ORDER BY "source"."mes" ASC, "source"."cliente" ASC

To Reproduce
SQL Query used:

SELECT
  "source"."mes" AS "mes",
  "source"."cliente" AS "cliente",
  SUM("source"."importeVenta") AS "sum",
  LAG(SUM("source"."importeVenta"), 1) OVER (
    PARTITION BY "source"."cliente"
   
ORDER BY
      "source"."mes" ASC
  ) AS "Sum of ImporteVenta (anterior año)",
  (
    CAST(SUM("source"."importeVenta") AS float) / NULLIF(
      LAG(SUM("source"."importeVenta"), 1) OVER (
        PARTITION BY "source"."cliente"
        ORDER BY
          "source"."mes" ASC
      ),
      0
    )
  ) - 1 AS "Sum of ImporteVenta (% vs anterior año)"
FROM
  (
    SELECT
      DATE_TRUNC('year', "Venta"."mesFactura") AS "mes",
      "Cliente"."nombre" AS "cliente",
      "Venta"."importeVenta" AS "importeVenta"
    FROM
      "Venta"
     
LEFT JOIN "Cliente" ON "Venta"."__cubeJoinField" = "Cliente"."__cubeJoinField"
  ) AS "source"
GROUP BY
  "source"."mes",
  "source"."cliente"
ORDER BY
  "source"."mes" ASC,
  "source"."cliente" ASC

Expected behavior
I expected to see the total sales for each client in a given month, their total sales last month and the percentage change
importeVenta is a simple number-type property and the results do show up when I don't include the lag part of the query

Version:
0.36.6

[igorlukanin]

Hi @lara-bellatin 👋

I believe any window functions, including LAG, are not currently supported by the SQL API.

I feel like calculations like this should be moved to your data model so that Cube generates SQL for you and the "client-side" queries are free of window functions.