We can define a temporary table — similar to a CTE in SQL — with
let top_50 = ( from employees sort salary take 50 aggregate [total_salary = sum salary] ) from top_50 # Starts a new pipeline
WITH table_0 AS ( SELECT salary FROM employees ORDER BY salary LIMIT 50 ), top_50 AS ( SELECT SUM(salary) AS total_salary FROM 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
tables are far less common than CTEs are in SQL, since a linear series
of CTEs can be represented with a single pipeline.