How should I practice As-Of Join?

Start by reading the full statement, solve the visible examples, then compare the pattern against related Headlands Technologies online assessment and onsite interview problems in FastPrep.

Is As-Of Join associated with Headlands Technologies?

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

As-Of Join

Headlands Technologies● MediumFULLTIMEOAONSITE INTERVIEW

Learn

Problem statement

You are given two timestamp-sorted tables, leftRows and rightRows. Each row is an integer array whose first value is its timestamp and whose remaining values are payload columns.

matchColumns contains the payload column indices that must match between the two tables. For every left row, find the last right row whose timestamp is less than or equal to the left timestamp and whose value equals the left value at every index in matchColumns.

Append that right row's payload columns, excluding its timestamp, to the complete left row. If several matching right rows have the same eligible timestamp, use the last one in rightRows. If no right row is eligible and matches all selected columns, append -1 once for each right-table payload column.

Return joined rows in the original left-table order. An empty matchColumns array applies no payload filters and performs the ordinary unkeyed as-of join.

Function

asOfJoin(leftRows: int[][], rightRows: int[][], matchColumns: int[]) → int[][]

Examples

Example 1

leftRows = [[1,10],[4,20],[6,30]]rightRows = [[2,100,101],[4,200,201],[5,300,301]]matchColumns = []return = [[1,10,-1,-1],[4,20,200,201],[6,30,300,301]]

Timestamp 1 has no eligible right row. Timestamp 4 joins the right row at 4, and timestamp 6 joins the right row at 5.

Example 2

leftRows = [[2,1],[3,2]]rightRows = [[2,7],[2,8],[5,9]]matchColumns = []return = [[2,1,8],[3,2,8]]

The two right rows at timestamp 2 are both eligible. The last one wins, so payload 8 is appended to both left rows.

Example 3

leftRows = [[10,4,5]]rightRows = [[1,6],[9,7]]matchColumns = []return = [[10,4,5,7]]

The latest right timestamp not exceeding 10 is 9, so its payload 7 is appended.

Example 4

leftRows = [[2,7],[3,9],[4,7]]rightRows = [[1,7,100],[2,9,200],[3,7,300]]matchColumns = [1]return = [[2,7,7,100],[3,9,9,200],[4,7,7,300]]

Each query selects the latest eligible row for its own key; a newer different key cannot replace it.

Example 5

leftRows = [[2,7,1],[3,7,2],[3,8,1]]rightRows = [[1,7,1,10],[2,7,2,20],[3,7,1,30],[3,7,2,40]]matchColumns = [1,2]return = [[2,7,1,7,1,10],[3,7,2,7,2,40],[3,8,1,-1,-1,-1]]

Both selected columns must match. The final query has no eligible row for key (8,1).

Example 6

leftRows = [[2,7,1]]rightRows = [[2,7,1,10],[2,8,1,20],[2,7,1,30]]matchColumns = [1,2]return = [[2,7,1,7,1,30]]

The last eligible row for the matching key wins at equal timestamps; the intervening other key is ignored.

Constraints

1 <= leftRows.length, rightRows.length <= 100000.
Every row in one table has the same length, and each row has between 2 and 10 columns.
Within each table, timestamps are sorted in nondecreasing order.
Every value is within the signed 32-bit integer range.
matchColumns contains distinct indices. Each is at least 1 and less than both tables' row widths. It may be empty, and its order does not change the matching rule.
The returned table contains at most 10^6 integers.