PRQL

1. from employees
2. filter country == "USA"                       # Each line transforms the previous result.
3. derive [                                     # This adds columns / variables.
4. gross_salary = salary + payroll_tax,
5. gross_cost = gross_salary + benefits_cost  # Variables can use other variables.
6. ]
7. filter gross_cost > 0
8. group [title, country] (                     # For each group use a nested pipeline
9. aggregate [                                # Aggregate each group to a single row
10. average salary,
11. average gross_salary,
12. sum salary,
13. sum gross_salary,
14. average gross_cost,
15. sum_gross_cost = sum gross_cost,
16. ct = count,
17. ]
18. )
19. sort sum_gross_cost
20. filter ct > 200
21. take 20

SQL

1. WITH table_1 AS (
2. SELECT
3. title,
4. country,
5. salary + payroll_tax + benefits_cost AS _expr_0,
6. salary + payroll_tax AS _expr_1,
7. salary
8. FROM
9. employees
10. WHERE
11. country = 'USA'
12. )
13. SELECT
14. title,
15. country,
16. AVG(salary),
17. AVG(_expr_1),
18. SUM(salary),
19. SUM(_expr_1),
20. AVG(_expr_0),
21. SUM(_expr_0) AS sum_gross_cost,
22. COUNT(*) AS ct
23. FROM
24. table_1 AS table_0
25. WHERE
26. _expr_0 > 0
27. GROUP BY
28. title,
29. country
30. HAVING
31. COUNT(*) > 200
32. ORDER BY
33. sum_gross_cost
34. LIMIT
35. 20

PRQL

1. from employees
2. group [emp_no] (
3. aggregate [
4. emp_salary = average salary     # average salary resolves to "AVG(salary)" (from stdlib)
5. ]
6. )
7. join titles [==emp_no]
8. group [title] (
9. aggregate [
10. avg_salary = average emp_salary
11. ]
12. )
13. select salary_k = avg_salary / 1000 # avg_salary should resolve to "AVG(emp_salary)"
14. take 10                             # induces new SELECT
15. derive salary = salary_k * 1000     # salary_k should not resolve to "avg_salary / 1000"

SQL

1. WITH table_3 AS (
2. SELECT
3. AVG(salary) AS _expr_1,
4. emp_no
5. FROM
6. employees
7. GROUP BY
8. emp_no
9. ),
10. table_1 AS (
11. SELECT
12. AVG(table_2._expr_1) AS _expr_0
13. FROM
14. table_3 AS table_2
15. JOIN titles ON table_2.emp_no = titles.emp_no
16. GROUP BY
17. titles.title
18. )
19. SELECT
20. _expr_0 / 1000 AS salary_k,
21. _expr_0 / 1000 * 1000 AS salary
22. FROM
23. table_1 AS table_0
24. LIMIT
25. 10