Required Week 2 practice · fictional drill

Keel and Bin: data reconciliation

One messy nightly extract, one live table, SQL that shows the gap, and a field map that is wrong on purpose. The nightly file is still not the live count.

Required practice for week 2 and the Keel and Bin case, at every depth. It does not replace either. Fictional practice. Not a placement. No savings figure. The SQL is not part of the 31 checks in python3 test_all.py.

Samir Qureshi, the fictional floor lead, still wants the bin from the live count. Purchasing still has a file from last night. This drill is how you show the gap without averaging the two sides.

Nightly file beside the live count

The arrow is a refusal. A gap means you say the live count did not answer. You do not quote the file.

The messy extract

Load this as text. Do not cast the quantity column on the way in. The blank row, the repeated label, and the word "seven" are the point. Part labels are fictional. They are not a catalog.

sku,onhand,bin,asof
KB-2042,14,A-12,2026-10-03
kb2042,14,A12,2026-10-03
KB-2042,14,A-12,
KB-1990,0,B-2,2026-10-03
,3,C-1,2026-10-03
KB-2201,seven,D-4,2026-10-03

The live table

This is the counter the floor updates. live_qty is an integer. bin_code is the bin you may name. There is no price column. There is no vendor column.

part_label | live_qty | bin_code | counted_at
KB-2042    | 9        | A-12     | 2026-10-04 09:10
KB-1990    | 2        | B-02     | 2026-10-04 09:10
KB-2201    | 7        | D-04     | 2026-10-04 09:12

SQL that shows the gap

Join on the label as written. Do not strip hyphens to force a match. A row that fails the join, or whose text quantity is not the live integer, is a gap. SQLite is enough. This script is practice. It is not one of the 31 checks.

import sqlite3
con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE nightly (sku TEXT, onhand TEXT, bin TEXT, asof TEXT)")
con.executemany(
    "INSERT INTO nightly VALUES (?,?,?,?)",
    [
        ("KB-2042", "14", "A-12", "2026-10-03"),
        ("kb2042", "14", "A12", "2026-10-03"),
        ("KB-2042", "14", "A-12", None),
        ("KB-1990", "0", "B-2", "2026-10-03"),
        (None, "3", "C-1", "2026-10-03"),
        ("KB-2201", "seven", "D-4", "2026-10-03"),
    ],
)
con.execute(
    "CREATE TABLE live (part_label TEXT, live_qty INTEGER, bin_code TEXT, counted_at TEXT)"
)
con.executemany(
    "INSERT INTO live VALUES (?,?,?,?)",
    [
        ("KB-2042", 9, "A-12", "2026-10-04 09:10"),
        ("KB-1990", 2, "B-02", "2026-10-04 09:10"),
        ("KB-2201", 7, "D-04", "2026-10-04 09:12"),
    ],
)
gap = con.execute(
    """
    SELECT n.sku, n.onhand, l.part_label, l.live_qty, l.bin_code
    FROM nightly n
    LEFT JOIN live l ON n.sku = l.part_label
    WHERE l.part_label IS NULL
       OR n.onhand != CAST(l.live_qty AS TEXT)
    """
).fetchall()
assert ("KB-2042", "14", "KB-2042", 9, "A-12") in gap
assert ("kb2042", "14", None, None, None) in gap
assert (None, "3", None, None, None) in gap
assert ("KB-2201", "seven", "KB-2201", 7, "D-04") in gap
assert ("KB-1990", "0", "KB-1990", 2, "B-02") in gap
print("gap rows", len(gap))

KB-2042 is on both sides and still a gap: the file says 14 and the live count says 9. The bin happens to match. That does not make 14 the answer. kb2042 does not equal KB-2042, so it does not join. The blank sku does not join. "seven" is not the text "7". KB-1990 says 0 in the file and 2 on the counter. None of these rows is a quantity you tell a picker.

A field map that is wrong on purpose

Read this map, then refuse it. It is here so you can see the mistakes. Do not ship it.

their name     mapped name     why this map is wrong
onhand          qty_on_hand     the live column is live_qty, an integer
bin             location        the live column is bin_code
asof            (dropped)       the clock is why the file is last night
sku             part_label      only after an exact match, not after stripping hyphens
(blank sku)     join on bin     a bin is not a part label
seven           7               a word is a gap, not a number you coerce

The map week 2 asks for is the other one: their name, your name, the live counter as the source of truth, and the sentence you return when the sides disagree. That sentence is "the live count did not answer." You do not average 14 and 9.

Do not trust the nightly file

The nightly file is a snapshot with a clock on it, and this extract does not even keep that clock on every row. It repeats a label, drops a label, and stores a quantity as a word. Pointing a tool at it because the file already exists is the wrong call the case already refused. If the file and the live counter disagree, or the file cannot be joined, you tell the picker the live count did not answer. You do not quote the file as if it were now. You do not invent a quieter radio, a percent, or a savings number from this drill.

Practice questions are on the interview page. The case is Keel and Bin Parts.