FastPrep
Problem · Database

Debug Fair-Value Lookup

● HardMillennium logoMillenniumNEW GRADINTERNOA

Problem statement

The provided Pandas starter attempts an as-of fair-value lookup, but it mishandles timestamp order, exact-time observations, invalid prices, and the staleness boundary. Fix fair_value_at so it returns a one-row DataFrame with the column price.

Lookup rules

  1. Read the single row in query_params and convert all timestamps before comparing them.
  2. Consider only price rows whose timestamp is at or before query_time.
  3. Starting with the most recent eligible row, walk backward until the requested asset has a non-null, strictly positive price.
  4. If no valid observation exists, return NULL.
  5. Otherwise, calculate the candidate's age from its own timestamp. Return NULL only when that age is strictly greater than max_staleness_minutes; an observation exactly on the boundary is accepted.

Table schema

Pandas

Use the same input data with any supported language. Open the Schema tab in the editor to see the generated SQL setup or Pandas DataFrames.

prices

Price tape with one timestamp and three asset columns.

ColumnTypeNullableDescription
timestampPKTimestampNo—
Asset_1DecimalYes—
Asset_2DecimalYes—
Asset_3DecimalYes—

query_params

The one requested asset, query time, and allowed staleness.

ColumnTypeNullableDescription
assetTextNo—
query_timeTimestampNo—
max_staleness_minutesIntegerNo—

Expected result

Your query or function must return these columns.

ColumnTypeNullableDescription
priceDecimalYes—

Row order: must match exactly. Numeric tolerance: 0.

Constraints

  • The prices table contains at least 1 row and at most 2000 rows.
  • Price timestamps are unique but may arrive out of order.
  • The query_params table contains exactly one row.
  • The requested asset is exactly Asset_1, Asset_2, or Asset_3.
  • max_staleness_minutes is a nonnegative integer.
  • Return exactly one row; its price may be NULL.

More Millennium problems