PRQL

from track_plays
filter plays > 10_000                # Readable numbers
filter (length | in 60..240)         # Ranges with `..`
filter recorded > @2008-01-01        # Simple date literals
filter released - recorded < 180days # Nice interval literals
sort {-length}                       # Concise order direction

SELECT
  *
FROM
  track_plays
WHERE
  plays > 10000
  AND length BETWEEN 60 AND 240
  AND recorded > DATE '2008-01-01'
  AND released - recorded < INTERVAL 180 DAY
ORDER BY
  length DESC

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) > 100000

from track_plays
derive {
  finished = started + unfinished,
  fin_share = finished / started,        # Use previous definitions
  fin_ratio = fin_share / (1-fin_share), # BTW, hanging commas are optional!
}

SELECT
  *,
  started + unfinished AS finished,
  (started + unfinished) / started AS fin_share,
  (started + unfinished) / started / (1 - (started + unfinished) / started)
   AS fin_ratio
FROM
  track_plays

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

SELECT
  CONCAT('https://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
  *,
  SUM(paycheck) OVER (
    PARTITION BY employee_id
    ORDER BY
      month ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
  ) AS trail_12_m_comp
FROM
  employees

let 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
    *,
    ROW_NUMBER() OVER (
      PARTITION BY role
      ORDER BY
        join_date
    ) AS _expr_0
  FROM
    employees
)
SELECT
  *
FROM
  table_0
WHERE
  _expr_0 <= 1

# There's no `version` in PRQL, but s-strings
# let us embed SQL as an escape hatch:
from x
derive db_version = s"version()"

SELECT
  *,
  version() AS db_version
FROM x

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
  *,
  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) *
FROM
  employees
ORDER BY
  age