Final assignment

What you will be able to do

Use every module of the course on a new question, and deliver a dataset that another person can check and build again. You use the same export as in the lessons, but the prediction time and the target are new, so you must make each decision again instead of copying it.

Before you startWhat to know and to have readyAbout 180 minutes

• You completed Modules 1 to 6. Each part below names the lessons that it uses.
• Your ticket-data project works: larkfield.db, the scripts from Modules 3 to 6 and the virtual environment with pandas 3.0.6. If something is broken, run python get_data.py --reset, then run your pipeline again.
• Make a new folder in your project for this assignment: reopen/. Put every new query, script and report for the assignment in it.
• You do not need the case studies of Module 7 for this assignment. (The certificate asks for at least one of them.) To see a similar procedure on real data first, open Prepare a leak-free table for city repair requests. It sets a target at the prediction time, splits by time and delivers a dataset with a contract.
• Tested with Python 3.14.6 and pandas 3.0.6 on macOS.

The question: which tickets come back?

Some tickets are resolved and then reopened: the customer writes again because the answer did not solve the problem. A reopened ticket costs Larkfield twice. Priya wants to train a model that predicts, at the moment an agent first resolves a ticket, if the customer will reopen it within 14 days. If the model flags a ticket, Grace’s team can do a short check before they close it.

Priya asks you for the dataset. She gives you these facts:

• Prediction time: the ticket’s first resolved event in outcomes.
• Target: reopened_14d is 1 if the ticket has a reopened event within 14 days after its first resolution, and 0 if it does not.
• The satisfaction survey (csat) is sent to the customer two days after a resolution. The export stores the answer on the resolved event, but nobody knows it at the moment of resolution.
• She wants a train set, a validation set and a test set, split by time.

All the data is the synthetic Larkfield export of 2026-07-01 06:00. No real people.

Part 1: extract and count

Uses Join tables, Readable queries, Time and ranking and Reproducible extraction.

1. Write a query, reopen/01-first-resolution.sql, that returns one row for each ticket that has at least one resolved event: the ticket ID, the time of its first resolution and its resolution_code. Use a window function, and break ties with outcome_id.
2. Before you run it, predict the number of rows. Then run it and write down the real number.
3. Check that no ticket appears twice in the result. Write the check as a query that must return 0 rows.
4. Write a second query that counts the tickets that have no resolved event. Explain what these tickets are.
5. Reconcile: the number of distinct tickets equals the tickets with a first resolution plus the tickets without one. Write the three numbers in your report.
6. Extract the data that you need for the assignment with your extract.py (or a copy of it), so that the manifest records the snapshot, the parameters and the row counts.

Two counts, two stages

Your SQL in Part 1 runs on the raw export, which still has repeated events and duplicate ticket rows. Part 2 cleans them. Your report must say at which stage each count was taken, so that a reader can follow the numbers from one table to the next.

Part 2: clean, and account for every row

Uses Profile before changing, Transform with Python and Handle messy values.

1. Run your cleaning step from Module 4 on the extract. You can reuse it as it is.
2. Write the input count, the duplicates removed, the quarantined rows (with each reason) and the clean count. The four numbers must add up.
3. Profile the columns that are new in this assignment: resolution_code on the first resolution, and the time from creation to first resolution. Look for missing values and impossible values (for example, a resolution before the ticket was created).
4. For each problem that you find, write a decision: exclude, correct or keep with a flag. Give the reason and say where the original value is kept.

Part 3: the target and the features, at the new prediction time

Uses Features and target timing.

This is the most important part. In Module 5, the prediction time was the creation of the ticket. Now it is the first resolution, so more is known, and some columns that were leakage before are now allowed. Others are still not allowed.

1. Calculate reopened_14d for every ticket with a first resolution.
2. Decide which tickets have a final target. A ticket whose 14 days are not over at the snapshot cannot have a final 0. Write the rule and the number of tickets that it removes.
3. Make a table of every column in tickets and customers, and of the information in outcomes. For each one, write “feature”, “not a feature” or “feature, with a change”, and give the reason in one sentence. Pay attention to closed_at, priority_now, first_reply_minutes and csat.
4. first_reply_minutes needs care. For some tickets, the first reply came after the first resolution. Find out how many. Decide how your feature handles these tickets, and explain why.
5. Add at least two features that use the new prediction time, for example the hours from creation to first resolution, or “was the ticket escalated before its first resolution”. Each one must use only events at or before the first resolution.
6. Build the modelling table: one row per ticket, the features, the target, and the ticket ID and resolution time for the split.

Part 4: a leakage-safe split

Uses Choose a split and Fit transformations correctly.

1. Split by the time of the first resolution: train before 2026-04-01, validation in April 2026, test from 2026-05-01.
2. For each part, write the rows, the reopened tickets and the reopen rate.
3. Explain in two or three sentences why a time split fits this question better than a random split.
4. Write a short preprocessing plan for Priya: which columns are categories, which are numbers, how each missing value is handled, and on which part each transformation is fitted. You do not train a model.

Part 5: validate and deliver

Uses Validation rules, Track lineage and Deliver a reusable dataset.

1. Write validation rules for the new tables: required columns and types, allowed values, a unique ticket ID, the target in {0, 1}, an expected range for the reopen rate, and no feature time later than the first resolution. Run them, and make one rule fail on purpose to show that the check works. Then fix the data or the rule.
2. Write reopen/DATASET.md: where the data comes from, its licence, the prediction time and the target, the steps in order, the tool versions, the row counts at each step and the known limitations.
3. Run the whole preparation twice from a clean snapshot (python get_data.py --reset). Compare the SHA-256 checksums of the train, validation and test files. They must be the same.

What to submit

A folder reopen/ with:

reopen/
  01-first-resolution.sql      and your other queries
  build_reopen.py              (or several scripts) that build the tables from the clean data
  validate_reopen.py           the validation rules
  data/train.csv  valid.csv  test.csv
  MANIFEST.json                row counts, reopen rates and SHA-256 of each file
  DATASET.md                   schema, lineage, decisions and quality report

DATASET.md must contain these sections:

# Reopen dataset (Larkfield, snapshot 2026-07-01)

## Question, prediction time and target
## Source and licence
## Query results and row counts        (Part 1, with the stage of each count)
## Cleaning decisions                   (Part 2: each problem, the decision, the reason)
## Columns: feature or not, and why     (Part 3)
## Split                                (Part 4: rule, counts, rates, preprocessing plan)
## Schema                               (each delivered column: name, type, meaning, allowed values)
## Quality report                       (validation results, known limitations)
## How to rebuild                       (commands, versions, checksums)

Check your work

There is no reviewer for this course yet. Review your work with this table, or ask a colleague to use it. Mark each item as demonstrated, partially demonstrated or not yet demonstrated, with a reason. The course is complete when every item is demonstrated. You can correct an item and review it again as often as you need.

Required evidence	Demonstrated when
Correct query results and row counts	The first-resolution query returns one row per resolved ticket, with ties broken by outcome_id. The counts reconcile (resolved plus never resolved equals all distinct tickets), and each count names its stage: raw export, extract, clean or modelling table.
Documented cleaning decisions	Every input row is accounted for: clean, quarantined with a reason, or removed as a duplicate, and the numbers add up. Each correction keeps the original value and states its assumption.
Leakage-safe split	No feature uses information from after the first resolution. closed_at, csat and priority_now are not features, each with a correct reason. first_reply_minutes is handled for tickets whose reply came after the resolution. Tickets whose 14 days are not over are removed. The split is by time, and the preprocessing plan fits on the train set only.
Dataset schema and quality report	DATASET.md describes every delivered column, the lineage and the limitations. The validation rules run, a failing rule gives a clear message, and two clean runs give the same checksums.

A dataset with correct counts and one leaky feature is not complete. For example, if closed_at is in the features, the third item is not yet demonstrated, even when every other number is correct.

Show the key numbers, to check your results

Open this only after you have your own numbers. Small differences can come from a different but documented cleaning decision; then your report must explain them.

• Part 1 (raw export): 1,403 distinct tickets. On the raw data, 1,392 tickets have a resolved event and 11 have none (they are still open). The extraction leaves out the ticket dated 2036 and cleaning quarantines 4 tickets, all of them resolved, so the clean data has 1,387 resolved tickets.
• Part 2: the cleaning step from Module 4 gives 1,416 → 14 duplicates, 4 quarantined, 1,398 clean tickets.
• Part 3: 1,387 clean tickets have a first resolution; 127 of them were resolved less than 14 days before the snapshot (after 2026-06-17 06:00), which leaves 1,260 tickets, 104 reopened (8.3%). For all 104 reopened tickets, closed_at is different from the first resolution: it is the time of the last resolution, so it shows the answer. For 42 tickets, the first reply came after the first resolution.
• Part 4: train 669 rows (52 reopened, 7.8%), validation 249 (28, 11.2%), test 342 (24, 7.0%).

AI assistants

You can ask an AI assistant to explain an error message or to suggest a query. Every decision in DATASET.md must be yours, and you must be able to explain it. Check every query that an assistant writes with a row count: assistants often write joins that look right and multiply rows. That is the lesson of this course in one sentence.

Common mistake

The table is clean, the counts are right, and one column contains the answer

Symptom: all checks pass. Priya trains a first model on your train set and gets an almost perfect score on the validation set.

Cause: a feature describes the ticket’s future. For example, closed_at is the time of the last resolution. For a reopened ticket, it is later than the first resolution, so the model reads the target from it.

Fix: for every feature, ask “at the moment of the first resolution, is this value already known, and does it already have this value?”. Add a validation rule for it: no feature time may be later than resolved_at. A score that is too good is a reason to look for leakage before you celebrate.

Recap

In this course, you learned to get data ready for AI work without quiet mistakes:

• Structure: what one row means, which keys connect the tables, and how to check that the keys hold.
• SQL: filters, aggregations and joins, each with a row count that proves it did what you meant.
• Analytical queries: readable steps, dates, window functions with ties handled, and extractions that you can reproduce.
• Cleaning: a profile first, then exclusion, correction or imputation, with originals kept and every row accounted for.
• Modelling safety: features known at prediction time, a split that matches the real use, and transformations fitted on training data only.
• Handoff: validation rules, lineage and a pipeline that another person can run again and trust.

Keep your project. Machine Learning: From Problem to Reliable Model trains and evaluates machine learning models on data like this, and Databases and Storage for AI Applications covers databases and storage for AI applications.