Software Engineer · Onsite · System Design

OpenAI — Software Engineer

Level: Senior-Level

Round: Onsite · Type: System Design · Difficulty: 5/10 · Duration: 60 min · Interviewer: Neutral

Topics: System Design, Hash Table

Location: San Francisco Bay Area

Interview date: 2025-11-25

Summary

This was an onsite interview question involving designing an in-memory SQL database system.

Details

The coding question I got was:

Implement the SQLManager class:

• SQLManager() Initializes the empty database system.

• void createTable(String tableName, List<String> columnNames) Creates a new table with the given tableName and a list of fixed columnNames.

  • The tableName is guaranteed to be unique.
  • Columns are initially untyped, but data inserted will be inferred as either integers or strings.

• void insert(String tableName, List<String> values) Inserts a row into the specified table.

  • The row ID is auto-incremented starting from 0 for each table.
  • values corresponds to the column names defined during table creation (in the same order).
  • Your implementation must infer the data type. If a value represents a valid integer (e.g., "123"), it is treated as a number for comparison; otherwise, it is treated as a string.

• List<Integer> select(String tableName, List<List<String>> conditions, List<String> orderBy) Selects rows from the table that satisfy all conditions (only consider "AND" logic) and returns their IDs in correct order.

  • conditions: A list of filters, where each filter is [<columnName>, <operator>, <value>].
    • Supported operators: "=" , ">" and "<" .
  • orderBy: A list of column names. Results should be sorted by these columns in ascending order.
    • If orderBy is empty, return rows in insertion order.
    • If two rows have the same value for the first order column, use the second, and so on.

Constraints:

• 1 ≤ columnNames.length ≤ 100
• All queries are valid (referenced tables and columns always exist).

My approach:

1. First, I would analyze the requirements for creating tables, inserting data with dynamic type inference, and performing queries with filtering and sorting.
2. Then I would design the data structures to hold table metadata, row data, and indexes.

Key insights:

• The importance of handling dynamic type inference during data insertion.
• Efficient querying and sorting based on different conditions and orderings.

Code

` from typing import List, Optional from collections import OrderedDict

class Table: def init(self, columnNames): self.colIndex = {} for i in range(len(columnNames)): self.colIndex[columnNames[i]] = i self.rows = OrderedDict() self.nextId = 0

class SQLManager: def init(self): self.tables = {}

def createTable(self, tableName: str, columnNames: List[str]) -> None:
    table = Table(columnNames)
    self.tables[tableName] = table

def insert(self, tableName: str, values: List[str]) -> None:
    table = self.tables[tableName]
    parsedValues = []
    for value in values:
        parsedValues.append(self.parseValue(value))
    rowId = table.nextId
    table.nextId = table.nextId + 1
    table.rows[rowId] = parsedValues

def select(self, tableName: str, conditions: List[List[str]], orderBy: List[str]) -> List[int]:
    table = self.tables[tableName]
    matchingIds = []

    # Filter rows based on conditions
    for rowId, row in table.rows.items():
        matchesAll = True
        for condition in conditions:
            if not self.match(table, row, condition):
                matchesAll = False
                break

        if matchesAll:
            matchingIds.append(rowId)

    # Sort if orderBy is specified
    if orderBy:
        def compare_rows(id1, id2):
            row1 = table.rows[id1]
            row2 = table.rows[id2]

            for colName in orderBy:
                colIdx = table.colIndex[colName]
                val1 = row1[colIdx]
                val2 = row2[colIdx]

                cmp = self.compare(val1, val2)
                if cmp != 0:
                    return cmp
            return 0

        from functools import cmp_to_key
        matchingIds.sort(key=cmp_to_key(compare_rows))

    return matchingIds

def parseValue(self, s):
    try:
        return int(s)
    except ValueError:
        return s

def compare(self, a, b):
    if isinstance(a, int) and isinstance(b, int):
        return (a > b) - (a < b)
    elif isinstance(a, str) and isinstance(b, str):
        return (a > b) - (a < b)
    return 0

# Check if a row matches a condition
def match(self, table, row, condition):
    colName = condition[0]
    operator = condition[1]
    valueStr = condition[2]

    colIdx = table.colIndex[colName]
    rowValue = row[colIdx]
    condValue = self.parseValue(valueStr)

    cmp = self.compare(rowValue, condValue)

    if operator == "=":
        return cmp == 0
    elif operator == ">":
        return cmp > 0
    elif operator == "<":
        return cmp < 0
    else:
        return False

`

LeetCode similar: LeetCode 2408