CSV Join and Group By

Medium3 partsabout 45 minHash-map counting, grouping and joinsSorting with custom comparatorsParsing structured text

Parse CSV text into rows, sum a column per group, left join two tables on a key column with missing matches, then add the filter and sort queries candidates report most: WHERE conditions and multi-column ORDER BY.

Also known as: Left join in Python, Group by without pandas, In-memory SQL query

Why practise this

Hash-map counting, grouping and joins shows up in 10.5% of the 1,447 coding interview reports we studied, at 14 companies. Count, group or match records by a key, often as one step inside a bigger problem.

Reported at: Amazon, Roblox, Robinhood, OpenAI, Anthropic, Snowflake, Rippling, Citadel, Databricks, Waymo, Palantir, Zip, xAI, Harvey AI.

This is an original practice problem written by AceOffer to train the pattern. It is not a question any company asked.

What candidates report interviewers add

Follow-ups candidates reported on questions with this pattern, in our words. Parts marked “from reports” below are built on them.

You get CSV data as strings, here an orders.csv and a products.csv:

order_id,customer,product_id,quantity
1,amy,P1,2
2,bob,P2,1
3,amy,P3,4
4,cat,P1,1
5,bob,P9,3

product_id,name,category,price
P1,Pen,office,3
P2,Mug,kitchen,8
P3,Pad,office,2
P4,Bowl,kitchen,6

Format rules for every part: the first non-blank line is the header, blank lines are ignored, and fields are separated by , with surrounding spaces stripped. Values never contain commas or quotes, and every row has as many fields as the header. Look columns up by header name, since column order can differ between inputs. Numeric columns hold integers. Text with no rows (empty, or only a header) gives empty results.

Questions to ask before you code

  • Can columns come in a different order, so I should find them by header name?
  • In the join, what goes in the right-hand columns when there is no match?
  • In the query, should numbers compare as numbers or as text, and how do I know which columns are numeric?
  • When sorting by several columns, which one wins, and what happens to rows that tie on all of them?
  • Do several where conditions combine with AND or OR?

Part 1: Group by and sumfrom reports

Write group_by_sum(csv_text, key, value) -> list[tuple[str, int]]. For each distinct value of column key, add up column value as integers. Return (key value, total) tuples sorted by key value in ascending string order.

group_by_sum(orders, "customer", "quantity")
# [("amy", 6), ("bob", 4), ("cat", 1)]

Part 2: Left joinfrom reports

Write left_join(left_csv, right_csv, on) -> list[dict].

  • Produce output for every left row, in left-file order. Values stay strings.
  • Each output dict has the left row's columns (in left header order) followed by the right header's columns except on (in right header order).
  • If no right row has the same on value, the right columns are None.
  • If several right rows match, emit one output dict per match, in right-file order.
left_join(orders, products, "product_id")[4]
# {"order_id": "5", "customer": "bob", "product_id": "P9", "quantity": "3",
#  "name": None, "category": None, "price": None}

Part 3: Filter and sortfrom reports

Write select(csv_text, columns=None, where=None, order_by=None) -> list[dict], a small query over one table.

Column types. A column is numeric when the table has at least one row and every value in that column is an integer such as 42 or -3. Numeric columns compare and sort as int; all other columns compare and sort as strings. A where value is converted the same way as its column.

  • where: a list of (column, op, value) conditions, with op one of =, !=, <, <=, >, >=. A row is kept only if it passes every condition. None or [] keeps all rows.
  • order_by: a list of (column, direction), direction "asc" or "desc". The first pair is the main key and later pairs break ties. Rows still tied keep their file order. None keeps file order.
  • columns: which columns to return, in that order. None returns every column in header order.
  • Each result is a dict of the kept columns with their original string values.
  • An unknown column anywhere raises KeyError. An unknown operator or direction raises ValueError.
select(orders, columns=["order_id", "quantity"],
       where=[("quantity", ">", 1)], order_by=[("quantity", "desc")])
# [{"order_id": "3", "quantity": "4"}, {"order_id": "5", "quantity": "3"},
#  {"order_id": "1", "quantity": "2"}]

Open this page on a laptop or desktop to write and run your code against the tests in the browser.

Approach

Spoilers: try each part before reading its walkthrough.

Part 1: Group by and sum

Start with a tiny parser that every part reuses. read_csv drops blank lines, splits the first line into header names, and turns each later line into a dict from header name to stripped value. Looking values up by name rather than position is what lets the same code handle files whose columns come in a different order. Grouping is then one pass with a defaultdict(int): add int(row[value]) to totals[row[key]]. Finally sorted(totals.items()) orders the groups by key, since tuples sort on their first element first. The pass is O(n) for n rows and the sort is O(g log g) for g groups. Keeping numbers as integers avoids any floating point drift in the totals.

Part 2: Left join

A nested loop over both tables works but costs O(n x m). The standard fix is a hash join: index the right table once as a dict from join value to the list of right rows that carry it. Storing a list, not a single row, is what handles duplicate keys, because each match produces its own output row. Then walk the left rows in order. If the lookup finds nothing, emit the left row with every right column set to None, which is what separates a left join from an inner join. Otherwise emit one merged dict per match. Merging with {**row, **extra} keeps left columns first and right columns after, in header order. Total cost is O(n + m + output size).

Part 3: Filter and sort

Treat the parsed rows as a tiny table and apply the query in the same order a database would: filter, then sort, then project. Before any of that, decide each column's type once by checking that every value parses as an integer; this is what makes 10 sort after 9. Filtering maps each operator symbol to a function such as operator.lt and keeps rows where the converted cell and converted value pass, condition by condition, so several conditions act as AND. Sorting by several keys uses stability: sort by the least important key first and the main key last, each with its own reverse flag, and earlier ties stay in file order. Projection builds a fresh dict per row with only the requested columns. Cost is O(n) per condition and O(n log n) per sort key.

Hints

Part 1 hint

Turn each line after the header into a dict that maps header name to field. A defaultdict(int) then sums the value column per key in one pass.

Part 2 hint

Index the right table first: a dict from the join value to the list of right rows with that value. Then each left row is a single lookup.

Part 3 hint

Map each operator symbol to a function from the operator module, and filter once per condition. For sorting, Python's sort is stable, so sorting by the last key first and the main key last gives the multi-column order, with reverse=True per key where needed.

Common mistakes

  • Reading columns by position, which breaks as soon as the header order changes.
  • Writing the join as a nested loop over both tables, which is O(n x m).
  • Dropping unmatched left rows, which turns the left join into an inner join.
  • Comparing numbers as strings, so 10 sorts before 9 and '10' > '9' is False.
  • Sorting by each key in the order given instead of reversed, so the last key ends up as the main one.

Where an interviewer could take it next

  • Add right join and full outer join.
  • The orders file is too big for memory: which of these can still run in one streaming pass?
  • Add an index on one column so equality filters do not scan every row.

Complexity

Parsing is linear in the input. group_by_sum is O(n + g log g) for n rows and g groups. left_join is O(n + m + output) using the right-side index. select is O(n x c) for c conditions plus O(k x n log n) for k sort keys, since each key is one stable sort.

Frequently asked

How do you left join two CSV files in Python with dictionaries?
Read each file into a list of dicts keyed by header name. Build an index from the right file: a dict that maps each join value to the list of right rows with that value. Then loop over the left rows. For each one, look up its join value; if there is no match, output the left row with the right columns set to None, otherwise output one merged row per match.
How do you group by and sum without pandas?
Use collections.defaultdict(int). Loop over the rows once and add each row's value to the entry for its group key, converting the value to a number first. When the loop ends, the dict holds one total per group. Sort its items if you need a stable output order. This runs in a single pass and needs memory only for the groups, not the whole file.
How do you filter and sort CSV rows in Python like SQL WHERE and ORDER BY?
Parse the file into a list of dicts. For WHERE, keep rows whose value passes a comparison, converting numeric columns with int so numbers compare correctly. For ORDER BY on several columns, rely on the fact that Python's sort is stable: sort by the last column first and the first column last, passing reverse=True for any descending key. Then keep only the columns you need.

Practise next

Log File Parser
Parsing structured text
Lowest Common Manager (Org Chart)
Tree recursion and traversal
Time-Based Key-Value Store with TTL
Time-based state (expiry, TTL, snapshots)

See what candidates report at each company

The company guides collect the questions candidates report, with every part, the follow-ups interviewers added, and how often each one comes up.

Browse the interview guides
Is this helpful?