from invoices
filter invoice_date >= @1970-01-16
derive {
transaction_fees = 0.8,
income = total - transaction_fees
}
filter income > 1Here's a simple example:
# define the "take_smallest" function
let take_smallest = func n col tbl<relation> -> (
from tbl
sort col
take n
)
# find smallest 3 tracks by milliseconds
from tracks
take_smallest 3 milliseconds
That's simple enough and there's not that much gained there but say you now want to find the 3 smallest tracks per album by bytes?
That's really simple in PRQL and you can just reuse the "take_smallest" function and pass a different column name as an argument:
from tracks
group album_id (
take_smallest 3 bytes
) # specify the target SQL dialect
prql target:sql.duckdb
# define the "sessionize" function
let sessionize = func user_col date_col max_gap:365 tbl<relation> -> (
from tbl
group user_col (
window rows:-1..0 (
sort date_col
derive prev_date=(lag 1 (date_col|as date))
)
)
derive {
date_diff = (date_col|as date) - prev_date,
is_new_session = case [date_diff > max_gap || prev_date==null => 1, true => 0],
}
window rows:..0 (
group user_col (
sort {date_col}
derive user_session_id = (sum is_new_session)
)
sort {user_col, date_col}
derive global_session_id = (sum is_new_session)
)
select !{prev_date, date_diff, is_new_session}
)
# main query
from invoices
select {customer_id, invoice_date}
sessionize customer_id invoice_date max_gap:365
sort {customer_id, invoice_date}
Example:
CREATE TEMP TABLE FUNCTION ExtendDates(input ANY TABLE, num_days INT64)
AS
FROM input
|> EXTEND date AS original_date
|> EXTEND max(date) OVER () AS max_date
|> JOIN UNNEST(generate_array(0, num_days - 1)) diff_days
|> SET date = date_add(date, INTERVAL diff_days DAY)
|> WHERE date <= max_date
|> SELECT * EXCEPT (max_date, diff_days);
FROM Orders
|> RENAME o_orderdate AS date, o_custkey AS user_id
|> CALL ExtendDates(7)
|> LIMIT 10;
(PRQL uses regex for stuff like this, which sounds great but then isn't supported on some databases like MSSQL)
0 - https://duckdb.org/community_extensions/extensions/prql.html