Home / Documentation / Worked examples

Worked examples

These five queries ship in examples/real/ with # source directives, so each one runs with no flags. The commands below assume the repository root is the working directory after installing the compiler.

1. Top products by revenue

A join, a derived column, a grouped aggregate, and top-N — the everyday shape.

# source orders: ../spec/orders.csv
# source products: ../spec/products.csv
from orders
join products on orders.product_id = products.product_id
derive line_total = quantity * unit_price
group by product_name
aggregate revenue = SUM(line_total), units = SUM(quantity)
sort -revenue
take 5

unsequel run examples/real/r01_top_products.unsequel

product_name  revenue  units
------------  -------  -----
Desk Lamp     168      7
Coffee Beans  132      11
Water Bottle  117      13
Notebook      94.5     21
Pen Set       82.5     11

5 row(s)

2. Revenue by customer segment

Joining a dimension table after the fact and deriving a metric from two aggregates.

# source orders: ../spec/orders.csv
# source customers: ../spec/customers.csv
from orders
join customers on orders.customer = customers.customer
group by segment
aggregate orders = COUNT(*), revenue = SUM(quantity * unit_price)
derive avg_order = revenue / orders
sort -revenue

unsequel run examples/real/r02_segment_revenue.unsequel

segment         orders  revenue  avg_order
--------------  ------  -------  ---------
wholesale       7       339      48.4286
enterprise      5       175.5    35.1
small-business  3       79.5     26.5

3 row(s)

3. Repeat customers

A group filter: having runs on the grouped relation.

# source orders: ../spec/orders.csv
from orders
group by customer
aggregate orders = COUNT(*), revenue = SUM(quantity * unit_price)
having orders > 1
sort -orders, customer

unsequel run examples/real/r03_repeat_customers.unsequel

customer   orders  revenue
---------  ------  -------
Ada        4       162
Grace      4       151.5
Alan       3       79.5
Katherine  3       177

4 row(s)

4. Monthly revenue trend

Native date functions replace what the original draft did with the SQL hatch.

# source orders: ../spec/orders.csv
from orders
derive month = strftime(order_date, '%Y-%m')
group by month
aggregate revenue = SUM(quantity * unit_price), orders = COUNT(*)
sort month

unsequel run examples/real/r04_monthly_revenue.unsequel

month     revenue  orders
--------  -------  ------
2024-01   93       2
2024-02   82.5     3
2024-03   165      3
2024-04   121.5    3
2024-05   84       3
2024-06   48       1

6 row(s)

A whole-relation window (over ()) computes the grand total once per row.

# source orders: ../spec/orders.csv
from orders
derive line_total = quantity * unit_price
window total_revenue = SUM(line_total) over ()
derive share = line_total / total_revenue
select order_id, line_total, share
sort -share
take 3

unsequel run examples/real/r05_share_of_total.unsequel --format json

[
  { "order_id": 5006, "line_total": 96, "share": 0.16161616161616163 },
  { "order_id": 5009, "line_total": 63, "share": 0.10606060606060606 },
  { "order_id": 5003, "line_total": 60, "share": 0.10101010101010101 }
]

More things to try

# canonical form of any file (and a diff-friendly check)
unsequel fmt examples/real/r01_top_products.unsequel --check

# step through stage 4 of the full reference pipeline
unsequel preview examples/reference/q20_full_pipeline.unsequel --stage 4 \
  --data orders=examples/spec/orders.csv \
  --data products=examples/spec/products.csv

# read the generated SQL with one CTE per unit
unsequel compile examples/real/r03_repeat_customers.unsequel

The reference corpus under examples/reference/ covers every stage, and examples/coverage/ adds fixtures for aliases, windows, set operators, CTEs, and subquery sources.