PRQL
from employees
filter country == "USA" # Each line transforms the previous result.
derive [ # This adds columns / variables.
gross_salary = salary + payroll_tax,
gross_cost = gross_salary + benefits_cost # Variables can use other variables.
]
filter gross_cost > 0
group [title, country] ( # For each group use a nested pipeline
aggregate [ # Aggregate each group to a single row
average salary,
average gross_salary,
sum salary,
sum gross_salary,
average gross_cost,
sum_gross_cost = sum gross_cost,
ct = count,
]
)
sort sum_gross_cost
filter ct > 200
take 20
SQL
WITH table_1 AS (
SELECT
title,
country,
salary + payroll_tax + benefits_cost AS _expr_0,
salary + payroll_tax AS _expr_1,
salary
FROM
employees
WHERE
country = 'USA'
)
SELECT
title,
country,
AVG(salary),
AVG(_expr_1),
SUM(salary),
SUM(_expr_1),
AVG(_expr_0),
SUM(_expr_0) AS sum_gross_cost,
COUNT(*) AS ct
FROM
table_1 AS table_0
WHERE
_expr_0 > 0
GROUP BY
title,
country
HAVING
COUNT(*) > 200
ORDER BY
sum_gross_cost
LIMIT
20
PRQL
from employees
group [emp_no] (
aggregate [
emp_salary = average salary # average salary resolves to "AVG(salary)" (from stdlib)
]
)
join titles [==emp_no]
group [title] (
aggregate [
avg_salary = average emp_salary
]
)
select salary_k = avg_salary / 1000 # avg_salary should resolve to "AVG(emp_salary)"
take 10 # induces new SELECT
derive salary = salary_k * 1000 # salary_k should not resolve to "avg_salary / 1000"
SQL
WITH table_3 AS (
SELECT
AVG(salary) AS _expr_1,
emp_no
FROM
employees
GROUP BY
emp_no
),
table_1 AS (
SELECT
AVG(table_2._expr_1) AS _expr_0
FROM
table_3 AS table_2
JOIN titles ON table_2.emp_no = titles.emp_no
GROUP BY
titles.title
)
SELECT
_expr_0 / 1000 AS salary_k,
_expr_0 / 1000 * 1000 AS salary
FROM
table_1 AS table_0
LIMIT
10