examples · sql · 9 / 13 · roadmap

9. Window functions (roadmap)

roadmap

Window functions - ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), and cumulative aggregates - aren't supported yet. The SQL surface focuses on JOINs and GROUP BY today. Window functions are tracked on the roadmap.

why you might want them

Per-group ranking is the canonical use - "rank each customer's orders by amount". You'd write:

SELECT id, customer_id, amount_cents,
       ROW_NUMBER() OVER (
         PARTITION BY customer_id
         ORDER BY amount_cents DESC
       ) AS rn
  FROM shop.orders

Today this returns 400 unsupported_sql.

workaround - compute ranks client-side

Fetch the rows, then group + sort in your application code.

# Compute per-customer ranks client-side.
result = db.sql("""
    SELECT id, customer_id, amount_cents
      FROM shop.orders
""")

from collections import defaultdict
by_cust = defaultdict(list)
for row in result.rows:
    by_cust[row["customer_id"]].append(row)

for cust_id, orders in by_cust.items():
    orders.sort(key=lambda r: r["amount_cents"], reverse=True)
    for rank, order in enumerate(orders, start=1):
        order["rn"] = rank
        print(order)

when to ask for the feature

If you're hitting this and the workload doesn't fit "fetch + rank in app code" (e.g. you need ranks computed server-side for pagination), tell us - prioritization is driven by real demand.