Variables

We can define a relation — similar to a CTE in SQL — as a variable with let:

let top_50 = (
  from employees
  sort salary
  take 50
  aggregate [total_salary = sum salary]
)

from top_50      # Starts a new pipeline

WITH table_1 AS (
  SELECT
    salary
  FROM
    employees
  ORDER BY
    salary
  LIMIT
    50
), top_50 AS (
  SELECT
    SUM(salary) AS total_salary
  FROM
    table_1 AS table_0
)
SELECT
  total_salary
FROM
  top_50

We can even place a whole CTE in an s-string, enabling us to use features which PRQL doesn’t yet support.

let grouping = s"""
  SELECT SUM(a)
  FROM tbl
  GROUP BY
    GROUPING SETS
    ((b, c, d), (d), (b, d))
"""

from grouping

WITH table_0 AS (
  SELECT
    SUM(a)
  FROM
    tbl
  GROUP BY
    GROUPING SETS ((b, c, d), (d), (b, d))
),
grouping AS (
  SELECT
    *
  FROM
    table_0 AS table_1
)
SELECT
  *
FROM
  grouping

Currently defining variables with let is restricted to relations. We’d like to extend this to expressions that evaluate to scalars.