The Table Thief

A medium Python interview practice problem on DataDriven. Write the Python and run it against test cases, with instant feedback.

Domain
Python
Difficulty
medium
Seniority
L4
Asked in
DoorDash

Problem

You're building a data-lineage tool that needs to know which tables each query reads. Given a SQL query string sql, pull the table names that follow its FROM and JOIN keywords, where a name may be trailed by an alias that isn't part of it.

Somewhere in that query, tables are hiding.

Example 1

Input:

sql = "SELECT a.id, b.name\nFROM orders a\nJOIN customers b ON a.customer_id = b.id"

Output:

["customers","orders"]

Example 2

Input:

sql = "SELECT a.id\nFROM users a\nJOIN users b ON a.manager_id = b.id"

Output:

["users"]

Worked solution and explanation

What this problem is really about

Strip away the SQL costume and this is a keyword-anchored token grab: for every FROM or JOIN in the string, you want the very next word, then a clean, deduplicated, sorted set of them. Anyone can eyeball the table names. The interview is watching two things. First, FROM|JOIN has to sit in a NON-capturing group (?:...), because the moment you use a plain capturing group, findall stops returning your table name and starts handing back the keyword. Second, real queries come in mixed case, so without case-insensitive matching a lowercase from sails right past you and its table quietly vanishes from your lineage graph.

How to get there

Step 1: Anchor on the FROM and JOIN keywords

Table names follow FROM and any variant of JOIN (LEFT JOIN, INNER JOIN, and so on). The keyword match must be case-insensitive, since queries mix FROM, from, and From freely.

Step 2: Capture the identifier that follows

The table name is the next identifier after the keyword. An alias (e.g., users u) may follow it, but you only capture that first \w+ and let the alias fall away naturally.

Step 3: Deduplicate and sort

A self-join names the same table twice, so collapse the matches into a set, then return them sorted. Both the dedup and the ordering are visible in the examples.

The solution

Keyword-anchored extraction
import re

def extract_table_names(sql):
    pattern = re.compile(r'(?:FROM|JOIN)\s+(\w+)', re.IGNORECASE)
    names = pattern.findall(sql)
    return sorted(set(names))

One compiled pattern, one pass, then normalize into a sorted unique set.

Common follow-up questions