PRQL (pronounced “Prequel”) aims to be “a simpler and more powerful SQL”
Like SQL, it’s readable, explicit and declarative. Unlike SQL, it forms a logical pipeline of transformations, and supports abstractions such as variables and functions. It can be used with any database that uses SQL, since it transpiles to SQL.
To get an idea on PRQL’s design, they provide this SQL statement as an example:
SELECT TOP 20
AVG(salary) AS average_salary,
SUM(salary) AS sum_salary,
AVG(salary + payroll_tax) AS average_gross_salary,
SUM(salary + payroll_tax) AS sum_gross_salary,
AVG(salary + payroll_tax + benefits_cost) AS average_gross_cost,
SUM(salary + payroll_tax + benefits_cost) AS sum_gross_cost,
COUNT(*) as count
WHERE salary + payroll_tax + benefits_cost > 0 AND country = 'USA'
GROUP BY title, country
ORDER BY sum_gross_cost
HAVING count > 200
And then translate it to PRQL, which looks like:
filter country = "USA" # Each line transforms the previous result.
let gross_salary = salary + payroll_tax # This _adds_ a column / variable.
let gross_cost = gross_salary + benefits_cost # Variables can use other variables.
filter gross_cost > 0
aggregate by:[title, country] [ # `by` are the columns to group by.
average salary, # These are the calcs to run on the groups.
sort sum_gross_cost # Uses the auto-generated column name.
filter count > 200