Questions / Quant development, SQL & data pipelines

Define the running-total order

Applied, about 6 minutes. Question D035.

A running quantity total should advance one trade at a time. Trades may share timestamps. What makes the intended row-by-row result deterministic?

  1. AUse a RANGE frame ordered only by the nonunique timestamp.
  2. BOrder by timestamp and use the default RANGE frame.
  3. CUse a complete ordering key and an explicit ROWS frame.
  4. DUse ROWS ordered by timestamp alone.

Answer it in the app It's free. You get hints, a second try if you miss, and a follow-up question.

Show the answer and worked solution

Answer: C. Use a complete ordering key and an explicit ROWS frame.

  1. Add a stable tie-breaker such as a trade sequence to timestamp ordering. This determines which equal-time trade comes first.
  2. Specify ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Each row then extends the running sum by exactly its own quantity.

Interview shortcut. Running totals need a unique order and a ROWS frame.

Takeaway. Running totals need both deterministic order and the intended frame.

Why the other choices fail

  • Use a RANGE frame ordered only by the nonunique timestamp. RANGE can aggregate timestamp peers together, so the running result need not advance one trade at a time.
  • Order by timestamp and use the default RANGE frame. Quantity is not a unique tie-breaker.
  • Use ROWS ordered by timestamp alone. ROWS controls frame membership, but a timestamp tie still leaves the relative order of those trades unspecified.

In the app this has a follow-up where one thing about the setup changes. Try the follow-up.

More sql & data pipelines questions