Questions / Quant development, SQL & data pipelines

The latest record needs a tie-breaker

Applied, about 6 minutes. Question D032.

You select the latest row per symbol using ROW_NUMBER over ORDER BY timestamp DESC. Several rows for one symbol share the same timestamp. What is missing for deterministic selection?

  1. AUse RANK instead of ROW_NUMBER with the same timestamp order.
  2. BSort the final output after filtering to row one.
  3. CAdd a secondary key that establishes the intended unique row order.
  4. DUse DENSE_RANK with the same timestamp-only ordering.

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. Add a secondary key that establishes the intended unique row order.

  1. Timestamp alone leaves tied rows without a specified internal order.
  2. ROW_NUMBER assigns different numbers, but the selected tied row is not determined by the intended feed order.
  3. Add a domain-appropriate secondary key that resolves ties before filtering to row one.

Interview shortcut. ROW_NUMBER on ties is arbitrary; add a tie-breaker.

Takeaway. A deterministic output requires a complete ordering contract.

Why the other choices fail

  • Use RANK instead of ROW_NUMBER with the same timestamp order. RANK can expose the tie but does not choose one intended row from it; multiple tied rows may retain rank one.
  • Sort the final output after filtering to row one. An outer sort happens after row selection and cannot determine which tied row the window function assigned first.
  • Use DENSE_RANK with the same timestamp-only ordering. Both still leave tied rows in an unspecified order.

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

More sql & data pipelines questions