Katkat
Back

Build a Chainable Python SQL Query Builder with Where, Alias and Join Support

Pylon LendingSoftware Engineer · Technical Screen
MediumSoftware EngineerTechnical ScreenSoftware Engineering Fundamentals

Last updated

Implement a small Python query builder with a fluent API covering table, select, where with condition objects, alias and a join on a shared column, where render() must return the exact SQL string an existing test suite expects. It tests clause ordering, literal quoting, alias qualification and builder design.

Implement a small ORM-style query builder in Python. An existing test suite drives it through a fluent API and compares the string returned by render() with the SQL it expects. The tests use calls like these:

o.table("users").select("id", "email")

o.table("products").select("id", "upc", "volume").where(
 o.cond("volume", ">", 300),
 o.cond("upc", "=", "1337"),
)

o.table("products").select("product_id", "price").alias("p")

Queries can also be joined:

users = o.table("users").select("user_id", "email").alias("u")
apps = o.table("applications").select("loan_id").alias("app")
users.join(apps, using="user_id")

Here o is the entry point the tests use (a module or an object). Implement o.table(name), o.cond(column, operator, value), and a query object with select, where, alias, join and render, so that each of these queries renders to the SQL SELECT statement the tests expect.

Constraints and Clarifications

  • The expected SQL strings live in the existing tests and are not reproduced here. render() must match them exactly, so formatting details are confirmed with the interviewer or read from the tests, not guessed.
  • Conditions are created with o.cond(column, operator, value). The examples use the operators > and =, with an integer value (300) and a string value ("1337").
  • where accepts several conditions in one call.
  • join takes another query and a using column that both tables share. In the example, the join column user_id is selected on the left side only.
  • Every table, column and alias name in the examples is a plain identifier.

Clarifying Questions

  • What exact format do the tests expect: keyword case, spacing and commas, whether AS appears before an alias, and whether the statement ends with a semicolon?
  • When a query has an alias, are its selected columns qualified with it (p.price) even without a join?
  • Are several conditions combined with AND? Can where be called more than once, and do the conditions then accumulate?
  • Which operators besides > and = must cond accept, and how should a None value render?
  • Values are inlined into the returned string. How should a string that contains a single quote be rendered?
  • Does each method return a new query, or change the query it is called on? The join example does not assign the result of users.join(...).
  • In a joined query, which columns appear in the SELECT list, and in what order?

Hints

Hint 1

Record first, render last: The builder methods can be called in different orders, but the clauses of a SQL statement cannot. Think about what each method should store so that `render()` alone decides the final layout.

Hint 2

Compare the two condition values: The products example passes `300` for one condition and `"1337"` for the other. Decide how each Python type becomes a SQL literal.

Follow-up questions

  • How would you change `render()` to return a parameterized statement plus a list of bound values, and why is that safer than inlining?
  • How would you add a join on an arbitrary condition (`on=`) and a left outer join?
  • How would you support `OR` and nested groups of conditions while keeping the call sites readable?
  • Where would `order_by` and `limit` live in your representation, and how would they render in a joined query?