prql.test

# name: test/sql/prql.test
# description: test prql extension
# group: [prql]

statement ok
CREATE TABLE t1(i INTEGER);

# Before we load the extension, this will fail
statement error
from t1 | sort i
----
Parser Error: syntax error at or near "|"

# Require statement will ensure this test is run with this extension loaded
require prql

# Confirm the extension works
statement ok
from t1 | sort i

# Trailing semicolon should work (e.g. used as part of CLI)
statement ok
from t1 | sort i;

# Bad prql statement
statement error
from t1 | srt j
----
Parser Error

statement ok
INSTALL httpfs;

statement ok
LOAD httpfs;

statement ok
CREATE TABLE invoices AS SELECT * FROM
  read_csv_auto('https://raw.githubusercontent.com/PRQL/prql/0.8.0/prql-compiler/tests/integration/data/chinook/invoices.csv');

statement ok
CREATE TABLE customers AS SELECT * FROM
  read_csv_auto('https://raw.githubusercontent.com/PRQL/prql/0.8.0/prql-compiler/tests/integration/data/chinook/customers.csv');

statement ok
from invoices | filter invoice_date >= @1970-01-16 | derive { transaction_fees = 0.8, income = total - transaction_fees } | filter income > 1 | group customer_id ( aggregate { average total, sum_income = sum income, ct = count total, }) | sort {-sum_income} | take 10 | join c=customers (==customer_id) | derive name = f"{c.last_name}, {c.first_name}" | select { c.customer_id, name, sum_income } | derive db_version = s"version()";

# scoped transforms
statement ok
create table t2 as (| from invoices | filter invoice_date >= @1970-01-16 | derive { transaction_fees = 0.8, income = total - transaction_fees } | filter income > 1 | group customer_id ( aggregate { average total, sum_income = sum income, ct = count total, }) | sort {-sum_income} | take 10 | join c=customers (==customer_id) | derive name = f"{c.last_name}, {c.first_name}" | select { c.customer_id, name, sum_income } |);

statement ok
from t2 | derive db_version = s"version()";

statement ok
create view invoices_filtered as (| from invoices | filter invoice_date >= @1970-01-16 | derive { transaction_fees = 0.8, income = total - transaction_fees } | filter income > 1 |);

statement ok
from invoices_filtered | group customer_id ( aggregate { average total, sum_income = sum income, ct = count total, }) | sort {-sum_income} | take 10 | join c=customers (==customer_id) | derive name = f"{c.last_name}, {c.first_name}" | select { c.customer_id, name, sum_income };

statement ok
WITH inv AS (FROM read_csv_auto('https://raw.githubusercontent.com/PRQL/prql/0.8.0/prql-compiler/tests/integration/data/chinook/invoices.csv')) (| from inv | filter invoice_date >= @1970-01-16 | derive { transaction_fees = 0.8, income = total - transaction_fees } |);

# multiple scoped transforms
statement ok
with x as (| from t1 | derive db_version = s"version()" |), y as (| from t1 | derive db_version = s"version()" |) from x union from y;