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_1 AS (
  SELECT
    AVG(salary) AS _expr_0,
    emp_no
  FROM
    employees
  GROUP BY
    emp_no
)
SELECT
  AVG(table_0._expr_0) / 1000 AS salary_k,
  AVG(table_0._expr_0) / 1000 * 1000 AS salary
FROM
  table_1 AS table_0
  JOIN titles ON table_0.emp_no = titles.emp_no
GROUP BY
  titles.title
LIMIT
  10