How should I practice Latest New Job?

Start by reading the full statement, solve the visible examples, then compare the pattern against related Sphere onsite interview problems in FastPrep.

Is Latest New Job associated with Sphere?

FastPrep tags this as a Sphere onsite interview coding practice problem when the problem catalog has that company and stage context.

Latest New Job

Sphere● EasyFULLTIMEONSITE INTERVIEW

Problem statement

A job table stores one row per background job. For this practice exercise, return the most recently created row whose status is new. Write a read-only SQL query or the configured dataframe function.

Input table

Column	Type	Meaning
`job_id`	integer	Unique job identifier
`status`	text	Job state
`created_on`	text	Creation time as fixed-width UTC text (`YYYY-MM-DD HH:mm:ss`)

For this practice schema, created_on is text, not a database timestamp type. Every value encodes a valid whole-second UTC instant in the same zero-padded format, so lexicographic ordering is chronological ordering. Do not convert values into a local timezone.

Selection rules

Keep only rows whose status equals the exact lowercase text new.
Choose the row with the greatest created_on timestamp.
If several matching rows have that timestamp, choose the smallest job_id.
Return the columns job_id, status, and created_on, in that order, with at most one row. If no row matches, return an empty table with those columns.

Optimization follow-up

The interview report asks how to optimize SELECT * FROM job WHERE status = 'new' ORDER BY created_on DESC LIMIT 1 for a table with about 100 million rows, of which about 20,000 are new. Explain which composite-index column order supports the equality predicate and the ordering before proposing a cache.

This practice exercise grades returned table values, not physical index DDL, measured latency or query plans. You do not need to create an index. The extra identifier tie rule above makes the practice result deterministic.

Table schema

MySQLPostgreSQLPandas

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.

job

A finite snapshot of job records.

Column	Type	Nullable	Description
job_idPK	Integer	No	Unique positive job identifier.
status	Text	No	Job state; only the exact lowercase value new is selected.
created_on	Text	No	A valid UTC creation instant encoded as fixed-width YYYY-MM-DD HH:mm:ss text; lexical order is chronological order.

Expected result

Your query or function must return these columns.

Column	Type	Nullable	Description
job_id	Integer	No	Unique positive job identifier.
status	Text	No	Job state; only the exact lowercase value new is selected.
created_on	Text	No	A valid UTC creation instant encoded as fixed-width YYYY-MM-DD HH:mm:ss text; lexical order is chronological order.

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

Constraints

The table contains between 0 and 2,000 rows for this exercise.
job_id is unique and is between 1 and 10^9, inclusive.
All three columns are non-null.
status contains 1 to 20 lowercase ASCII letters or underscores; match only new.
created_on is text encoding a valid UTC timestamp as YYYY-MM-DD HH:mm:ss at whole-second precision between 2000-01-01 00:00:00 and 2030-12-31 23:59:59, inclusive.
Input rows may appear in any order. Do not mutate the input dataframe.

See Sphere hiring insights