Unexpected result when composing aggregations with case expressions

[brunorpinho]

What happened?

Hi,

I am experiencing a bug where prql generates an invalid SQL because there is a case expression inside a sum. Let me demonstrate.

PRQL input

prql target:sql.clickhouse

from `t`.`table`
group {(group_variable)} (aggregate {
  `new_variable` =  (sum (case [(var | as int) < 10 => 1, true => 0])) * 100 / (count null)
})

SQL output

WITH table_0 AS (
  SELECT
    group_variable,
    COALESCE(
      SUM(
        CASE
          WHEN CAST(var AS int) < 10 THEN 1
          ELSE 0
        END
      ),
      0
    ) AS _expr_0,
    var
  FROM
    t.`table`
)
SELECT
  group_variable,
  (_expr_0 * 100 / COUNT(*)) AS new_variable
FROM
  table_0
GROUP BY
  group_variable

-- Generated by PRQL compiler version:0.13.3 (https://prql-lang.org)

Expected SQL output

SELECT
  group_variable,
  (COALESCE(SUM(
        CASE
          WHEN CAST(var AS int) < 10 THEN 1
          ELSE 0
        END
   ), 0) * 100 / COUNT(*)) AS new_variable
FROM
  t.`table`
GROUP BY
  group_variable

MVCE confirmation

• Minimal example
• New issue

Anything else?

For some reason the system breaks the expression into a intermediary CTE for the sum when the case expr exists.