Examples gallery

Standard runtimeDownload PDF (1.7 MB)

All examples use the sample sales.orders table from the Quickstart.

1. Shipped orders in the last quarter#

sql
SELECT order_id, customer, region, amount, order_date
FROM sales.orders
WHERE status = 'shipped'
  AND order_date >= DATE '2026-01-01'
ORDER BY order_date, order_id

2. Revenue by region with a Prep rollup#

sql
SELECT region, amount, status
FROM sales.orders
python
orders = pd.DataFrame(queryData)
shipped = orders[orders["status"] == "shipped"]
outputDF = (
    shipped.groupby("region", as_index=False)["amount"]
    .sum()
    .rename(columns={"amount": "revenue"})
    .sort_values("revenue", ascending=False)
)

3. Monthly trend#

sql
SELECT order_date, amount
FROM sales.orders
ORDER BY order_date
python
orders = pd.DataFrame(queryData)
orders["month"] = pd.to_datetime(orders["order_date"]).dt.strftime("%Y-%m")
outputDF = orders.groupby("month", as_index=False)["amount"].sum()

4. Largest order per region#

sql
SELECT order_id, region, customer, amount
FROM (
  SELECT o.*, ROW_NUMBER() OVER (
    PARTITION BY region ORDER BY amount DESC
  ) AS rn
  FROM sales.orders AS o
) ranked
WHERE rn = 1
ORDER BY region

5. Pivot status counts by region#

sql
SELECT region, status, order_id
FROM sales.orders
python
orders = pd.DataFrame(queryData)
outputDF = (
    orders.pivot_table(
        index="region", columns="status", values="order_id",
        aggfunc="count", fill_value=0,
    )
    .reset_index()
)
outputDF.columns.name = None

6. Orders owned by the signed-in user#

sql
SELECT order_id, customer, amount, status
FROM sales.orders
WHERE sales_rep = ${USR}
ORDER BY order_id

7. A single row of headline numbers#

sql
SELECT amount, quantity, status
FROM sales.orders
python
orders = pd.DataFrame(queryData)
outputDF = {
    "orders": int(len(orders)),
    "revenue": float(orders["amount"].sum()),
    "units": int(orders["quantity"].sum()),
    "shipped_share": float((orders["status"] == "shipped").mean()),
}

8. Customers with their segment (join in SQL)#

sql
SELECT o.customer, c.segment, SUM(o.amount) AS revenue
FROM sales.orders AS o
JOIN sales.customers AS c ON c.customer_id = o.customer_id
GROUP BY o.customer, c.segment
ORDER BY revenue DESC

9. Defensive Prep for empty results#

python
orders = pd.DataFrame(queryData)
if orders.empty:
    outputDF = []
else:
    orders["amount"] = orders["amount"].astype(float).round(0)
    outputDF = orders.sort_values("amount", ascending=False).head(5)

10. Test Prep locally before you save it#

Prep is plain pandas, so you can exercise it with a small list of rows on your own machine.

python
import pandas as pd

queryData = [
    {"order_id": 1, "region": "EMEA", "amount": 120.0},
    {"order_id": 2, "region": "APAC", "amount": 80.0},
    {"order_id": 3, "region": "EMEA", "amount": 40.0},
]
prep = '''
rows = pd.DataFrame(queryData)
outputDF = rows.groupby("region", as_index=False)["amount"].sum()
'''
scope = {"pd": pd, "queryData": queryData}
exec(compile(prep, "<prep>", "exec"), scope)
print(scope["outputDF"])