PRQL

from order               # This is a comment
filter status == "done"
sort [-amount]           # sort order

SELECT
  order.*
FROM
  order
WHERE
  status = 'done'
ORDER BY
  amount DESC

from employees
derive [
  age_at_year_end = (@2022-12-31 - dob),
  first_check_in = start + 10days,
]

SELECT
  *,
  DATE '2022-12-31' - dob AS age_at_year_end,
  start + INTERVAL 10 DAY AS first_check_in
FROM
  employees

from employees
# Filter before aggregations
filter start_date > @2021-01-01
group country (
  aggregate [max_salary = max salary]
)
# And filter after aggregations!
filter max_salary > 100_000

SELECT
  country,
  MAX(salary) AS max_salary
FROM
  employees
WHERE
  start_date > DATE '2021-01-01'
GROUP BY
  country
HAVING
  MAX(salary) > 100_000

from web
# Just like Python
select url = f"http://www.{domain}.{tld}/{page}"

SELECT CONCAT('http://www.', domain, '.', tld,
  '/', page) AS url
FROM web

from employees
group employee_id (
  sort month
  window rolling:12 (
    derive [trail_12_m_comp = sum paycheck]
  )
)

SELECT
  employees.*,
  SUM(paycheck) OVER (
    PARTITION BY employee_id
    ORDER BY
      month ROWS BETWEEN 11 PRECEDING
      AND CURRENT ROW
  ) AS trail_12_m_comp
FROM
  employees

func fahrenheit_from_celsius temp -> temp * 9/5 + 32

from weather
select temp_f = (fahrenheit_from_celsius temp_c)

SELECT
  temp_c * 9/5 + 32 AS temp_f
FROM
  weather

# Most recent employee in each role
# Quite difficult in SQL...
from employees
group role (
  sort join_date
  take 1
)

WITH table_0 AS (
  SELECT
    employees.*,
    ROW_NUMBER() OVER (
      PARTITION BY role
      ORDER BY
        join_date
    ) AS _rn
  FROM
    employees
)
SELECT
  table_0.*
FROM
  table_0
WHERE
  _rn <= 1

# There's no `version` in PRQL, but
# we have an escape hatch:
derive db_version = s"version()"

SELECT
  version() AS db_version

from employees
join b=benefits [==employee_id]
join side:left p=positions [p.id==employees.employee_id]
select [employees.employee_id, p.role, b.vision_coverage]

SELECT
  employees.employee_id,
  p.role,
  b.vision_coverage
FROM
  employees
  JOIN benefits AS b ON employees.employee_id = b.employee_id
  LEFT JOIN positions AS p ON p.id = employees.employee_id

from users
filter last_login != null
filter deleted_at == null
derive channel = channel ?? "unknown"

SELECT
  users.*,
  COALESCE(channel, 'unknown') AS channel
FROM
  users
WHERE
  last_login IS NOT NULL
  AND deleted_at IS NULL

prql target:sql.mssql  # Will generate TOP rather than LIMIT

from employees
sort age
take 10

SELECT
  TOP (10) employees.*
FROM
  employees
ORDER BY
  age