Build a Labeled Training Dataset from Messy Order and Store Tables with an AI Assistant

In a one-hour AI-assisted coding round for a machine learning role, you work in a HackerRank environment that gives you five small datasets about orders, identifiers and stores. Your task is to turn them into a training dataset for a model: select features from the data you have and attach a label, which in the reported version is whether an order was placed. The datasets have the small problems real data has, such as duplicate records and missing values, and you must resolve them. You work through an AI coding assistant built into the IDE: you discuss the prompts you give it with the interviewer, and you interpret the results once you have them. The report does not list the datasets' columns. Assume they can be joined through shared identifiers such as order, customer and store ids, and confirm the actual keys and the label definition with the interviewer. ### Clarifying Questions - What does one training example represent: a customer, a customer and store pair, a visit, or a customer on a given day? Where do the examples labeled "no order" come from? - At what moment would the model make its prediction, so that every feature uses only data available before then? - Over what time window after that moment does an order count toward the label? - What counts as a duplicate: identical rows, or the same id appearing with different values? - For each column with missing values, does missing mean unknown, zero, or not applicable? - Is a quick model expected after the dataset, or only the dataset and its interpretation? ### Part 1 — Audit and clean the five datasets Load each dataset, establish what one row represents and which columns form its key, and find and fix the data problems before joining anything. ```hint Check the grain first Before choosing how to drop duplicates, decide what one row of each table should represent and test whether its supposed key is really unique. ``` #### What This Part Should Cover - Profiling: row counts, key uniqueness, null rates, types and value ranges - A deliberate, explained policy for each kind of duplicate and missing value - Checks that joins between the tables neither multiply rows nor silently drop ids ### Part 2 — Define the label and assemble the features Choose the unit of a training example, build the label "an order was placed", select features from the cleaned tables, and produce one training table. ```hint Where the zeros come from An orders table only records orders that happened. Think about which population of rows can carry the label 0, and from what point in time each feature may be computed. ``` #### What This Part Should Cover - The example unit, the prediction time and the label window, and how negative examples are generated - Features aggregated from history before the prediction time, with no target leakage - Validation of the output: one row per example, a plausible label rate and no unexplained missing values ### Part 3 — Work with the AI assistant and interpret the results Explain how you prompt the assistant, how you check what it produces, and how you interpret the finished dataset, and any quick model, with the interviewer. ```hint Make the assistant's guesses visible Think about what the prompt must state so the assistant does not have to guess the schema or the label, and which quick checks would expose a join or a label that is silently wrong. ``` #### What This Part Should Cover - Prompts that state the schema, the grain, the label definition and the leakage rule, in small verifiable steps - Reading and testing generated code instead of accepting it because it runs - Interpreting the result: label rate, feature distributions, suspicious features, limitations and next steps ### What a Strong Answer Covers - Asks about the example unit, prediction time and label definition before writing code - Profiles each table before cleaning and justifies every fix instead of dropping rows silently - Joins on verified keys at the right grain and keeps information from after the prediction time out of the features - Uses the assistant for speed while staying in control: precise prompts, code review and assertions - Explains findings and limitations clearly within the hour ### Follow-up Questions - A feature turns out to predict the label almost perfectly. How do you decide whether it is leakage? - The same order id appears twice with different totals. How do you choose which row to keep? - How would you split this dataset into training and validation sets, and why might a random split mislead? - The assistant's code runs without errors, but the label rate looks far too high. How do you find the bug?