Data Cleaning Exercises

Exercises

Data cleaning exercises

Each exercise gives you a messy dataset and the exact rules that decide a correct result. Solve it visually, in SQL, or in pandas; your answer is checked on datasets you haven't seen.

beginner

Beginner15 minFree

Pick one surviving record per account

Several systems sent the same account. Keep exactly one row per account using a stated tie-break order, and list every row you removed.

  • deduplication
  • tie-breaks
  • timestamps

Beginner10 minFree

Normalize names without changing letters

The same accented name arrives in two Unicode forms and padded with unusual spaces. Make equal names equal without stripping accents.

  • Unicode
  • trimming
  • normalization

Beginner15 minFree

Referential integrity with explicit exceptions

Payments must point at a known account. Quarantine missing and unknown references with distinct reasons so every row is accounted for.

  • lookups
  • quarantine
  • reconciliation

Beginner8 minFree

Normalize emails under a declared policy

This system treats email addresses as case-insensitive. Trim and lowercase them — and do nothing else: dots and plus tags stay.

  • text
  • policies
  • idempotence

Beginner15 minFree

Map channels with an approved dictionary

Support tickets name the same channel a dozen ways. Map them with the approved dictionary only, and quarantine anything the dictionary doesn't cover instead of guessing.

  • mappings
  • controlled vocabulary
  • quarantine
  • trimming

Beginner15 minFree

Keep identifiers as text and parse yes/no flags

Member IDs and postcodes have leading zeros that must survive, and the opt-in flag arrives as Y, yes, TRUE or 1. Parse the flag to a real boolean without guessing.

  • identifiers
  • booleans
  • mappings
  • type conversion

Beginner10 minFree

Label unknown categories, keep missing numbers missing

A report needs every visit in a region bucket, so a missing region becomes "unknown". A missing duration is different: filling it with 0 would drag the average down.

  • missing values
  • filling
  • blank text
  • type conversion

Beginner10 minPro

Turn sentinel codes into missing values

A sensor export writes -999 when no reading was taken. Treat that code and blanks as missing, keep real readings (including negatives), and quarantine anything that isn't a whole number.

  • missing values
  • sentinels
  • type conversion

Beginner10 minPro

Validate supplier country codes

Supplier records carry country codes typed by hand. Normalize them and accept only the five approved codes, reporting missing and unknown codes separately.

  • validation
  • allowed values
  • text

Beginner10 minPro

Map ticket priorities with a documented default

Support tickets use several spellings for priority, and some have none. Map the approved spellings, apply the documented default for missing priorities, and quarantine anything unmapped.

  • mappings
  • defaults
  • text

Beginner10 minPro

Out of scope is not an error

The report covers EU orders only. Exclude other regions without reporting them as errors, then validate EU amounts and quarantine the ones that aren't exact two-decimal numbers.

  • scope
  • validation
  • exact decimals

Beginner10 minPro

Validate sign-up dates

Sign-up dates should be real ISO dates on or after the product launch. Report missing dates, impossible or wrongly formatted dates, and dates before launch separately.

  • dates
  • validation

Beginner10 minPro

Validate invoice totals

Invoice totals must be exact two-decimal amounts and never negative (credit notes come from another feed). Report missing, malformed and negative totals with separate reasons.

  • exact decimals
  • validation
  • money

Beginner8 minPro

Flag bulk orders without removing them

Validate order quantities and flag bulk orders (100 or more) for the warehouse, keeping every valid order in the output.

  • flags
  • validation
  • thresholds

Beginner8 minPro

Enrich without dropping rows

Attach department titles to employees. A missing or unknown department is not an error here: keep the employee and leave the title missing.

  • joins
  • left joins
  • lookups

Beginner10 minPro

Unpivot quarterly sales, keeping zeros

Quarterly sales arrive with one column per quarter. Turn them into one row per store and quarter. A reported 0 is a real value; a missing quarter produces no row.

  • reshaping
  • missing values

Beginner8 minPro

Exclude test accounts from scope

A user export mixes real and test accounts, with the test flag written several ways. Keep real users only (test accounts are out of scope, not errors) and normalize their emails.

  • scope
  • booleans
  • text

Beginner8 minPro

Map known variants, keep the rest

US states arrive as codes or full names. Map the listed full names to their codes and keep every other value unchanged; this mapping only standardizes known variants and rejects nothing.

  • mappings
  • text

Beginner10 minPro

Validate currency, then amount

Each payment needs an approved currency code and an exact amount. Check them in the declared order so every rejected payment has exactly one first reason.

  • validation
  • rule order
  • exact decimals

Beginner8 minPro

Required name and email

A contact needs a name and an email. Whitespace-only values count as missing. Report the first missing field in the declared order.

  • required fields
  • missing values
  • rule order

Beginner8 minPro

Placeholder text means missing

Phone numbers were filled with placeholders like N/A, none or a dash. Turn the listed placeholders into missing values and keep real numbers exactly as written.

  • missing values
  • placeholders
  • text

Beginner10 minPro

Count distinct users per day

An event log has many events per person. Report daily active users: distinct people per day, not events, after normalizing user IDs and leaving out rows that can't be attributed.

  • aggregation
  • distinct counts
  • dates

Beginner10 minPro

First and last order per customer

Build a customer summary with the first and last order date and the number of orders. Only real dates may count, and missing and invalid dates are reported separately.

  • dates
  • aggregation
  • validation

Beginner10 minPro

Errors versus warnings

Not every problem should block a row. A bad weight is an error and is quarantined; a missing tracking number is a warning: keep the shipment and flag it.

  • validation
  • severity
  • flags

Beginner10 minPro

Rename vendor columns and type them

A vendor file arrives with display-style headers. Map them to the warehouse schema, keep customer IDs as text (leading zeros matter), and give dates and amounts explicit types.

  • import
  • schemas
  • types

Beginner10 minPro

Unknown is not no

A consent field mixes yes, no and 'unknown'. Map the answers to true and false, keep unknown and unasked as missing (never false), and quarantine values that aren't answers at all.

  • booleans
  • missing values
  • mappings

Beginner10 minPro

Required fields block, optional fields don't

Price is required for a product listing; weight is optional enrichment. A bad price quarantines the row, while an unreadable weight just becomes missing and the product stays.

  • missing values
  • field policies
  • types

Beginner8 minPro

Profile completeness before cleaning

Before changing anything, measure how complete the file is: total rows and how many rows actually have an email, a phone and a birth date. Blank and whitespace-only cells don't count as present.

  • profiling
  • missing values
  • aggregation

Beginner8 minPro

Frequency table, including missing

Before mapping a status field, list every distinct normalized value with how often it occurs. Missing values are a value too: they get their own row instead of disappearing from the table.

  • profiling
  • group by
  • missing values

Beginner8 minPro

Share only the columns needed

An analytics partner needs city-level spend, not personal details. Deliver only the agreed columns — no names, emails or phones, not even in the exception report — with spend validated and city normalized.

  • data minimization
  • handoff
  • validation

Beginner10 minPro

Rates are fractions, not percents

Discount rates must be stored as fractions between 0 and 1. A value like 15 is almost certainly a percent typed in the wrong unit: report it instead of dividing by 100 on a guess.

  • units
  • ranges
  • decimal precision

Beginner8 minPro

Highest number, not highest text

Find the highest invoice number per store. As text, 9 sorts after 10 and 0042 before 7, so the numbers must be compared as numbers after validation.

  • types
  • aggregation
  • sorting

Beginner8 minPro

Stray header and total rows

This file was made by pasting several exports together, so header rows repeat in the middle and a TOTAL row sits at the end. Those rows aren't records: drop them before validating the real ones.

  • import
  • file structure
  • validation

Beginner8 minPro

Report unmapped values as other

For a channel report, map ticket sources with the approved table and put anything unmapped into an explicit other bucket — while keeping the normalized source next to it so the other bucket can be audited and the mapping extended.

  • mappings
  • reporting buckets
  • traceability

Beginner10 minPro

Joins with clashing column names

Orders carry the customer's name and the product table also has a name column. Bring in the product name under a new column name instead of overwriting the customer name or ending up with name_x and name_y.

  • joins
  • column naming
  • pandas merge

Beginner8 minPro

Derive a flag from a category list

Customers on the gold and platinum tiers get priority support. Normalize tiers through the approved mapping (plat is platinum), quarantine unknown tiers, and derive a priority_support flag from the list.

  • mappings
  • flags
  • validation

Beginner8 minPro

An invisible character in the header

A CSV saved as 'UTF-8 with BOM' starts with an invisible U+FEFF, so the first header is U+FEFF followed by customer_id and code that names the column fails. Rename it and tidy the other fields.

  • import
  • encoding
  • headers

Beginner10 minPro

Placeholder dates mean unknown

The membership system writes 1900-01-01 (and, in old records, 0001-01-01) when a birth date is unknown. Turn those placeholders into missing values before anyone computes ages, and quarantine real errors.

  • missing values
  • sentinels
  • dates

Beginner8 minPro

Headline numbers in one row

Produce the three headline numbers for a dashboard tile: valid orders, total revenue and the exact median order value. One row, computed only from amounts that pass validation.

  • aggregation
  • medians
  • exact decimals

Beginner8 minPro

The sign carries meaning

Stock movements use positive quantities for sales and negative ones for returns. The sales report includes only sales; returns belong to a different report and aren't errors. A quantity of zero is meaningless and is reported.

  • scope
  • validation
  • signs

Beginner8 minPro

Split train and test by time

For a forecasting model, test rows must come after training rows. Mark rows dated 2026-01-01 or later as test and earlier ones as train, instead of a random split that would leak the future into training.

  • train/test split
  • dates
  • leakage

Beginner8 minPro

Age rules as a cutoff date

Only adults may sign up. Instead of computing ages (and getting birthdays and leap years wrong), turn the rule into a cutoff: on 2026-06-30, adults were born on or before 2008-06-30.

  • validation
  • dates
  • boundaries

Beginner8 minPro

Totals that reconcile with missing keys

Total expenses per department, including expenses with no department. pandas groupby silently drops missing keys by default, so the report would no longer add up to the file; keep the missing group.

  • group by
  • missing values
  • reconciliation

Beginner10 minPro

Map month spellings to one code

A month column was typed as Mar, March, 03 or 3. Map every approved spelling to a two-digit month code, quarantine what isn't a month, and total units per month.

  • mappings
  • controlled vocabulary
  • aggregation

Beginner8 minPro

Windows line endings in the last column

An order file was saved on Windows and read as if it came from Linux, so every value in the last column ends with an invisible carriage return. 'open\r' isn't 'open': strip the line-ending characters before validating statuses.

  • import
  • whitespace
  • validation

Beginner10 minPro

Rename a column before stacking files

Three regional files should stack into one table, but the north export still calls the amount column amt. Rename it first — it is the same field — instead of letting the schema check reject the whole north file.

  • combining files
  • schemas
  • lineage

Beginner8 minPro

An unticked checkbox is blank

A web form exports a checkbox as 'on' when ticked and nothing when unticked. For the optional newsletter box, blank therefore means false — a documented exception to 'missing is unknown'. For the required terms box, blank means not accepted.

  • missing values
  • booleans
  • documented defaults

Beginner10 minPro

Group people into households

Count members per household, where a household is a normalized postcode plus address. '12 High St' and ' 12 high st' at 'ab1 2cd' are the same household; people without a complete address can't be placed and are reported.

  • entity resolution
  • normalization
  • aggregation

Beginner8 minPro

Soft-deleted rows are not live data

The accounts table keeps deleted accounts and marks them with a deleted_at value. Every downstream query must leave them out — as out of scope, not as errors — and validate only live accounts.

  • scope
  • soft deletes
  • validation

Beginner8 minPro

Round numbers are often estimates

Self-reported task times cluster on 30, 60, 90 and 120 minutes — the numbers people type when they estimate. Flag those as likely estimates so analysts can weigh them, while keeping every valid entry.

  • outliers
  • heaping
  • flags

Beginner8 minPro

Stack by column name, not position

Two exports have the same columns in a different order. SQL's UNION ALL matches columns by position, so SELECT * from both puts regions into the id column without any error. Stack by name, then validate.

  • union
  • schemas
  • combining files

Beginner8 minPro

The text 'false' is true in Python

Keep only active users. The active column is text — 'true', 'False', ' TRUE ' — and in Python any non-empty string is truthy, so astype(bool) keeps everyone. Parse the text into real booleans first.

  • booleans
  • types
  • pandas

Beginner8 minPro

The sign lives in another column

Ledger entries carry a direction (debit or credit) and a positive amount. A negative or zero amount is invalid here, because the direction already says which way money moved — a negative credit would flip it twice.

  • validation
  • signs
  • finance

Beginner8 minPro

Measure how many dates parse, per source

Before choosing a date rule, measure: for each source system, how many rows there are and how many event dates are valid ISO dates. A source that sends 03/04/2026 will stand out — and nothing is changed or quarantined yet.

  • profiling
  • dates
  • inspection

Beginner8 minPro

Zero degrees is a reading

A freezer log records temperatures from -50 to 60 °C. 0 and 0.0 are real readings, not missing values — but code that tests 'if value' or uses NULLIF(x, 0) quietly turns them into gaps.

  • validation
  • zero vs missing
  • ranges

Beginner8 minPro

Check the period each extract covers

Three weekly extracts should each cover one week. Before combining them, profile each extract: row count, how many rows have a usable date, and the first and last date. An extract that stops early or overlaps the next one shows up immediately.

  • profiling
  • dates
  • inspection

Beginner8 minPro

One-hot encode a category

A model needs the acquisition channel as three true/false columns: is_email, is_search and is_social. An unknown channel must be reported, not silently encoded as all-false, which the model would read as a fourth, made-up category.

  • machine learning prep
  • encoding
  • categories

Beginner8 minPro

Deliver missing, not empty text

A contact handoff has optional nickname and phone fields. Downstream systems test for missing values, so an empty or whitespace-only field must be delivered as missing — not as an empty string, which looks the same in a spreadsheet and breaks the receiving system's checks.

  • delivery
  • missing values
  • text

Beginner8 minPro

Snapshots are not deltas

The warehouse sends a full stock snapshot every day. Yesterday's and today's files both list every SKU, so stacking them doubles the stock. Stock as of 30 June comes from the 30 June snapshot alone.

  • combining files
  • snapshots
  • grain

Beginner8 minPro

Prices that might be in cents

Shelf prices are normally between 1 and 100. A price of 1299 is most likely 12.99 typed in cents, but it could be a real premium item, so flag it for review instead of dividing by 100 or deleting it.

  • outliers
  • units
  • flags

Beginner8 minPro

What the dictionary doesn't cover

Before applying a mapping dictionary, find out what it misses: the normalized values that have no entry, with how often each occurs. Missing values get their own row. The output is the to-do list for whoever maintains the dictionary.

  • inspection
  • mappings
  • profiling

Beginner8 minPro

Don't fill down unordered rows

An inventory export has blank aisles on some rows. In a printed report, a blank often means 'same as above', but this export's row order is arbitrary, so the row above says nothing about the aisle. Report the blanks instead of filling them down.

  • missing values
  • row order
  • validation

intermediate

Intermediate35 minFree

Clean an order-line export

Trim padded customer codes without losing leading zeros, parse amounts exactly, reject timestamps without an offset, quarantine unknown customers, and keep the latest copy of each duplicated line.

  • trimming
  • type conversion
  • quarantine
  • lookups
  • deduplication

Intermediate15 minPro

Deduplicate order lines at the right grain

An order has several lines and each line has versions. Keep the latest version of every line, not one row per order.

  • deduplication
  • row grain
  • type conversion

Intermediate20 minFree

Parse dates by declared locale

03/04/2026 is March 4th in the US and 3 April in the UK. Parse slash dates only when the row says which convention it uses.

  • dates
  • locales
  • quarantine

Intermediate20 minPro

Sum amounts by currency, without converting

Amounts arrive as 1,234.50 or 1.234,50 or (12.50). Parse each by its declared locale, keep exact cents, and total each currency separately.

  • decimals
  • locales
  • aggregation
  • quarantine

Intermediate15 minFree

Append monthly files under a declared schema

Three files share most columns. Stack the ones that match the schema, record which file each row came from, and reject a file that's missing a required column.

  • combining files
  • schemas
  • lineage

Intermediate15 minFree

Timestamps need an offset

Normalize timestamps to UTC, keep two different offsets on the same wall-clock time apart, and quarantine timestamps that never said which zone they're in.

  • timestamps
  • time zones
  • quarantine

Intermediate25 minFree

Validate quantities and prices against ranges

An inventory feed has impossible quantities, zero prices and unknown countries. Apply the validation rules in order and report the first rule each row breaks.

  • validation rules
  • ranges
  • type conversion
  • allowed values
  • quarantine

Intermediate20 minPro

Resolve customer records by normalized email

The CRM and the web shop both hold customer records. Merge records that share an email under a stated survivor rule, and send records you can't match safely to human review.

  • entity resolution
  • survivor rules
  • normalization
  • human review

Intermediate15 minFree

Unpivot survey answers without inventing scores

A survey export has one column per question. Reshape it to one row per answer, and leave unanswered questions out rather than scoring them zero.

  • unpivot
  • missing values
  • type conversion
  • reshaping

Intermediate12 minPro

Look up product names by SKU

Order lines carry SKUs in mixed case with padding. Normalize them, attach the product name from the catalog, and report unknown SKUs and bad quantities instead of dropping them.

  • joins
  • lookups
  • validation

Intermediate12 minPro

Keep the current price per product

A price history has several rows per product. Validate prices, then keep the row that is current (latest valid_from, then smallest row_id) and report the rest as superseded.

  • deduplication
  • survivor rules
  • dates

Intermediate12 minPro

Totals with coverage by region

Summarize visit minutes by region, and report how many visits had a recorded duration so readers can judge the totals. Missing durations stay missing; malformed ones are reported.

  • aggregation
  • missing values
  • coverage

Intermediate12 minPro

Expense totals by category

Total expense amounts per category with exact decimals. Uncategorized expenses get their own group; malformed amounts are reported and left out of the totals.

  • aggregation
  • exact decimals
  • categories

Intermediate10 minPro

Keep the first delivery of each event

A device resends events when it loses its connection. Keep the first delivery of each event_id (earliest received instant, then smallest row_id) and report the resends.

  • deduplication
  • timestamps
  • survivor rules

Intermediate10 minPro

Median delivery days by carrier

Delivery times have a long tail. Report the median days per carrier (robust to extremes) with the number of deliveries, after rejecting malformed durations.

  • aggregation
  • robust statistics
  • validation

Intermediate14 minPro

Total before you pivot

Build a Q1 store-by-month table. Several rows can share a store and month, so total them first; months outside Q1 are out of scope, and a store with no sales in a month stays missing, not zero.

  • pivot
  • aggregation
  • scope

Intermediate10 minPro

Map legacy codes with a crosswalk

Old category codes are retired. Translate them with the approved crosswalk table, keep the original code next to the new category so the mapping can be audited, and report missing and unmapped codes separately.

  • mappings
  • lookups
  • traceability

Intermediate12 minPro

Validate against the referenced record

Bookings must point to a room that exists and is open. Look each room up, use its state to validate the booking, and check the number of nights, reporting the first failing rule.

  • joins
  • validation
  • ordered rules

Intermediate12 minPro

Exact copies versus repeat payments

An export glitch repeated some payment rows. Remove the copies (same payer, amount and time) but keep genuine repeat payments, comparing typed values so 12.5 and 12.50 count as the same amount.

  • deduplication
  • typed comparison
  • timestamps

Intermediate12 minPro

Scope rows to a date window

The Q1 report covers 1 January to 31 March 2026. Rows outside the window are out of scope, rows whose date can't be read are errors, and amounts are validated only for rows in scope.

  • scope
  • dates
  • boundaries

Intermediate10 minPro

Invisible spaces break validation

Colour codes pasted from a web page carry no-break and ideographic spaces that an ASCII trim can't see. Remove Unicode whitespace, then validate the codes against the approved list.

  • unicode
  • whitespace
  • validation

Intermediate10 minPro

Four-decimal prices, never rounded

Fuel prices are quoted per litre to four decimals. Keep that precision exactly, reject prices with more decimals instead of rounding them, and report the lowest and highest price per station.

  • decimal precision
  • aggregation
  • validation

Intermediate12 minPro

Build a duplicate review queue

Don't merge customers automatically. Group records that share a normalized email into candidate groups for a person to review, listing the lowest record ID as the proposed master and how many records each group holds.

  • entity resolution
  • blocking
  • aggregation

Intermediate10 minPro

Impossible versus unusual readings

A temperature sensor can only measure -40 to 85 °C. Quarantine readings outside that physical range, but keep hot readings that are possible and flag them for attention instead of deleting them.

  • outliers
  • validation
  • flags

Intermediate12 minPro

Load a new batch only once

A nightly batch can contain orders that an earlier run already loaded. Skip those so re-running the load never inserts an order twice, then validate the amounts of the genuinely new orders.

  • idempotence
  • incremental loads
  • joins

Intermediate10 minPro

Long IDs must stay text

Account IDs are up to 19 digits. As floating-point numbers some of them become identical, so joining on numbers attaches the wrong plan. Match the IDs as text.

  • identifiers
  • joins
  • types

Intermediate12 minPro

Prefer the most complete record

Contacts are duplicated by email. Keep the record that has a phone number; among those, the most recently updated one; then the smallest id. Report the others as merged duplicates.

  • deduplication
  • survivor rules
  • completeness

Intermediate14 minPro

Load summary per source file

Three store files arrive for one load. Stack the ones that match the schema, validate amounts, and produce a per-file load summary (rows loaded and total) so a rejected or short file stands out.

  • combining files
  • reconciliation
  • schemas

Intermediate12 minPro

Scope by a reference attribute

The revenue report covers active customers only. An order for an unknown customer is an error, an order for a closed or trial customer is simply out of scope, and in-scope amounts must be positive.

  • scope
  • joins
  • validation

Intermediate12 minPro

Two-level referential integrity

Sales point to products, and products point to categories. Follow both links to attach the department, and report a sale whose product is unknown separately from one whose product points to a category that doesn't exist.

  • joins
  • referential integrity
  • lookups

Intermediate12 minPro

Combine overlapping exports

Two customer exports overlap: the latest export and an older archive. Stack them with lineage, keep one row per customer preferring the latest export, and keep customers that only exist in the archive.

  • combining files
  • lineage
  • deduplication

Intermediate10 minPro

Impute with an indicator column

A model needs a discount on every order, so missing discounts are filled with 0.00 — but only together with an indicator column, so analysts can tell a real zero from a filled one.

  • imputation
  • indicator columns
  • analysis readiness

Intermediate10 minPro

Future dates are errors, old dates are stale

The device list was extracted on 30 June 2026. A last-seen date after the extract is impossible and quarantined; a date before 2026 is real but stale, so the device is kept and flagged.

  • dates
  • validation
  • flags

Intermediate10 minPro

Not applicable is not unknown

In a contract list, an end date of N/A means the contract is open-ended, while a blank means nobody has entered it yet. Keep both as missing dates, but record the difference in an open_ended column.

  • missing values
  • flags
  • dates

Intermediate12 minPro

Top seller per category

Pick the best-selling product in each category with an explicit tie-break: highest units sold, then the smallest product ID. Products with unreadable sales can't compete.

  • window functions
  • ranking
  • ties

Intermediate10 minPro

Some codes are case-sensitive

Coupon codes are case-sensitive: SAVE10 and Save10 are different coupons with different discounts. Trim the padding but don't change case, or redemptions get the wrong discount.

  • text
  • lookups
  • over-normalization

Intermediate12 minPro

Exclude a suppression list safely

Build a mailing list that excludes everyone on the suppression list. The list contains blank rows, which silently empty a NOT IN query in SQL; exclude suppressed people without losing everyone else.

  • anti-joins
  • NULL semantics
  • scope

Intermediate12 minPro

Same name is not the same person

Two members called Lee Park may be different people. Merge records only when the normalized name AND the birth date agree, and keep same-name people with different birth dates apart.

  • entity resolution
  • composite keys
  • false merges

Intermediate10 minPro

Checkbox columns to rows

A form stored a multi-select question as one checkbox column per channel. Turn it into one row per respondent and chosen channel; unticked boxes produce no rows.

  • unpivot
  • multi-select
  • normalization

Intermediate10 minPro

Composed and decomposed accents

City names arrive from two systems: one writes ü as a single character, the other as u plus a combining mark. Normalize to NFC before looking cities up, without stripping the accents.

  • unicode
  • normalization
  • lookups

Intermediate12 minPro

Bin ages with a band table

Assign each person an age band using the approved band table (0–17, 18–34, 35–54, 55+). Edges are where binning code usually goes wrong, so 17, 18, 34, 35, 54 and 55 must land in the right band.

  • binning
  • lookups
  • boundaries

Intermediate12 minPro

Dates hidden in column headers

A sensor sheet has one column per day, so the dates live in the headers. Unpivot to one row per sensor and day, turn the header into a real date, and drop blank cells (no reading) while keeping zeros.

  • unpivot
  • headers as data
  • dates

Intermediate10 minPro

Ambiguous symbols need review

Currencies were captured as symbols or codes. € and £ are unambiguous, but $ could be US, Canadian or Australian dollars. Map what is certain and send $ to review instead of guessing.

  • mappings
  • ambiguity
  • validation

Intermediate10 minPro

A leaderboard with a defined order

Total each player's points and deliver the leaderboard in a defined order: most points first, ties broken alphabetically by player. Here row order is part of the answer.

  • aggregation
  • sorting
  • ties

Intermediate12 minPro

Counts of nothing are zero

Build a team-by-status table of ticket counts. Unlike sums of unknown values, a count over no tickets really is 0, so empty cells are filled with 0 — a documented exception to 'missing is not zero'.

  • pivot
  • counting
  • zero versus missing

Intermediate10 minPro

Suppress small counts before publishing

A published table of residents per district must not show groups smaller than 5, because tiny counts can identify people. Count per district and publish only districts with at least 5 residents.

  • privacy
  • aggregation
  • thresholds

Intermediate12 minPro

Unknown times never win

Keep the latest status per account. Some rows have no usable timestamp; they may only survive when an account has no timed row at all. Be explicit about where missing values sort.

  • deduplication
  • NULL ordering
  • timestamps

Intermediate10 minPro

Average order value per channel

Report the average order value per channel, rounded to cents. Only valid amounts count: a missing or broken amount is excluded and reported, never treated as zero.

  • aggregation
  • averages
  • rounding

Intermediate12 minPro

Session start and end as instants

Work out when each user session started and ended. Events were logged by servers in different time zones, so the earliest and latest event must be found by instant, not by the text of the timestamp.

  • timestamps
  • aggregation
  • time zones

Intermediate10 minPro

Stack files without losing identical rows

Two branches send sales files with no row IDs. A sale of the same SKU and quantity on the same day in both branches appears as identical rows, and both are real. Stack the files and total units per SKU without collapsing them.

  • combining files
  • UNION ALL
  • aggregation

Intermediate10 minPro

Answer counts per question

Summarize a wide survey: for each question, how many respondents gave each answer. Unpivot first so the answers can be cleaned once, leave unanswered questions out, and count.

  • unpivot
  • aggregation
  • normalization

Intermediate10 minPro

Exclude cancelled, keep unknown status

Leave cancelled orders out of the report. In SQL, status <> 'cancelled' is not true for a missing status, so a careless filter also drops orders whose status is unknown. Label them unknown and keep them.

  • NULL semantics
  • scope
  • filters

Intermediate12 minPro

Pick the current row, then filter

List the orders that are open right now. Each order has a status history, so first take each order's latest status and only then keep the open ones; filtering first would resurrect orders that have since closed.

  • order of operations
  • deduplication
  • timestamps

Intermediate10 minPro

Remove resends before totals

A payment feed sometimes re-sends a payment. Revenue per merchant must count each payment once, so remove the resends (same payment_id) before summing — otherwise totals grow with every retry.

  • deduplication
  • aggregation
  • reconciliation

Intermediate10 minPro

Survey codes that mean missing

A satisfaction question is coded 1–5, with 8 for don't know and 9 for refused. Decode every answer with the codebook, but only 1–5 become a score: averaging an 8 or a 9 would invent satisfaction.

  • codebooks
  • missing values
  • lookups

Intermediate10 minPro

A work queue with unassigned first

Deliver the support queue in its agreed order: unassigned tickets first, then by owner a to z, then oldest first. Missing owners must sort first, which is not every tool's default.

  • sorting
  • NULL ordering
  • timestamps

Intermediate10 minPro

Flag small samples, don't hide them

Summarize product ratings with the median and the number of reviews. A median from three reviews isn't reliable, so flag products with fewer than 5 reviews instead of dropping them or pretending they're as solid as the rest.

  • aggregation
  • sample size
  • flags

Intermediate10 minPro

Roll line flags up to orders

Summarize order lines per order: how many lines, whether ANY line is backordered and whether ALL lines have shipped. 'Any' and 'all' are different roll-ups of the same line-level facts.

  • aggregation
  • booleans
  • grain

Intermediate12 minPro

Repeated phone columns to distinct rows

A contact form stored up to three phone numbers in phone_1 to phone_3, and people often typed the same number twice. Produce one row per person and distinct phone, remembering the first slot it appeared in.

  • unpivot
  • deduplication
  • multi-valued fields

Intermediate12 minPro

Who hasn't reported March

Stores report revenue monthly. Pivot the Q1 reports and list the stores with no valid March report, with their January and February totals, so someone can chase them. A missing cell is the finding here.

  • pivot
  • completeness
  • follow-up lists

Intermediate10 minPro

The referenced record is incomplete

Invoices need the customer's billing email from the CRM. A customer that doesn't exist and a customer that exists but has no billing email are different problems with different owners, so report them separately.

  • joins
  • required fields
  • referential integrity

Intermediate10 minPro

Map months to fiscal quarters

The company's fiscal year starts in April, so March 2026 belongs to FY25 Q4, not to calendar Q1. Map each month with the finance calendar table instead of computing quarters, then total revenue per fiscal quarter.

  • calendars
  • lookups
  • aggregation

Intermediate10 minPro

Two code systems in one column

A country column mixes two-letter codes (DE) and three-letter codes (DEU). Translate both to the two-letter standard with the reference table, which lists every accepted code including the two-letter ones themselves.

  • mappings
  • standards
  • lookups

Intermediate12 minPro

First-touch attribution

Credit each customer's first marketing touch. Touches were logged in several time zones, so 'first' means the earliest instant; ties go to the smallest touch ID. Normalize channels and report touches without a usable time.

  • attribution
  • timestamps
  • survivor rules

Intermediate10 minPro

Training rows need a label

Prepare churn training data. A row without a known outcome can't teach the model anything, so it is quarantined; a missing feature is fine and stays missing for the model's own handling.

  • machine learning prep
  • booleans
  • missing values

Intermediate10 minPro

Sellable stock excludes expired batches

Report sellable units per SKU as of 2026-06-30. Batches that expired before that date aren't sellable (out of scope, not errors); batches whose expiry date can't be read are reported, because nobody knows if they're safe.

  • dates
  • scope
  • aggregation

Intermediate10 minPro

Aggregate through a dimension table

Revenue must be reported by region, but sales only carry a store code. Attach each store's region from the store table, report sales for unknown stores, and total revenue per region.

  • joins
  • aggregation
  • dimensions

Intermediate10 minPro

Dashes that only look the same

Part codes pasted from documents contain en dashes and minus signs that look exactly like a hyphen. They are different characters, so those codes don't match the catalog. Report them instead of silently rewriting them.

  • unicode
  • lookups
  • validation

Intermediate12 minPro

Resolve customers through a crosswalk

The shop and the CRM give the same customer different keys. A crosswalk maps every source key to one master customer ID. Resolve orders through it before totalling, or one customer's revenue is split across two keys.

  • entity resolution
  • lookups
  • aggregation

Intermediate12 minPro

Load only rows after the high-water mark

An incremental load picks up rows changed since the last run. The previous run's high-water mark is 2026-03-01 12:00 UTC: load rows updated strictly after that instant, compared as instants, so a row stamped 13:00+01:00 (the mark itself) isn't loaded twice.

  • incremental loads
  • timestamps
  • boundaries

Intermediate10 minPro

IDs that went through a spreadsheet

Member numbers passed through a spreadsheet, which wrote some as 1042.0 or 1.042E+03. Those are float artefacts: digits may already be lost, so they are reported for re-export instead of being 'fixed' by converting through a float.

  • identifiers
  • types
  • spreadsheets

Intermediate10 minPro

First available contact number

Pick each customer's contact number from three columns in a stated order of preference: mobile, then landline, then work phone. Whitespace-only cells count as empty. This is COALESCE — and the order of its arguments is the business rule.

  • coalesce
  • missing values
  • priority rules

Intermediate12 minPro

Keep the first row, not the first values

Keep each person's earliest sign-up row, whole. pandas groupby().first() takes the first non-missing value of each column separately, stitching together a record that never existed. If the earliest row has no phone, the result has no phone.

  • pandas
  • duplicates
  • survivor rules

Intermediate10 minPro

Spikes with calendar context

Daily order counts spike on some days. Flag days with 500 or more orders and attach the event from the marketing calendar, so a Black Friday peak is explained and an unexplained spike stands out. Nothing is removed.

  • outliers
  • context
  • lookups

Intermediate10 minPro

Repair garbled text with an approved list

Somewhere upstream, UTF-8 text was decoded as Windows-1252, so München became München. Apply the approved repair list, then validate cities against the reference table. Garbled values that aren't on the list are reported, not re-decoded on a guess.

  • encoding
  • mappings
  • validation

Intermediate10 minPro

A sort order written down exactly

Deliver a staff directory sorted by name in Unicode code point order, ties by id. That order puts Zoe before adam and émile after zack. Normalize accents to one form first, or the same name sorts in two places.

  • sorting
  • unicode
  • delivery

Intermediate10 minPro

Below detection is not zero

A lab result column holds numbers and the code <LOD (below the limit of detection). That's two meanings in one text column: split it into a typed number and a true/false flag, instead of turning <LOD into 0 or throwing it away.

  • types
  • missing values
  • flags

Intermediate12 minPro

Filter rows, then filter groups

List repeat customers: people with at least two paid orders, and what they spent. Unpaid orders must be removed before counting (WHERE), and customers with fewer than two paid orders after counting (HAVING). Doing either at the wrong stage changes who qualifies.

  • aggregation
  • where vs having
  • scope

Intermediate10 minPro

Whole numbers that can be missing

Children per household is optional: blank means not answered, not 0. In pandas a whole-number column with a gap becomes float (2.0, NaN) unless you use the nullable Int64 type, and the contract asks for whole numbers.

  • pandas
  • types
  • missing values

Intermediate12 minPro

Allocations must add up to 100%

Each invoice is split across departments by percentage. Every line can be valid on its own while the invoice is not: 60% + 50% is 110%. Validate the lines, then report the invoices whose allocations don't total exactly 100.

  • validation
  • aggregation
  • group rules

Intermediate10 minPro

Count tags that look alike

Blog tags were typed on phones and pasted from the web: Café, café with a combining accent, café padded with a no-break space. Count uses per tag after Unicode trimming, NFC and lower case, so one tag isn't split into three rows.

  • unicode
  • normalization
  • aggregation

Intermediate10 minPro

Match rate per source system

Before trusting a customer match, measure it: for each source system, how many records found a CRM customer by email. Missing emails can't match anything — not even the CRM row whose email is also missing.

  • entity resolution
  • quality metrics
  • joins

Intermediate12 minPro

Current rows from a history table

A customer tier table keeps history: closed rows have a valid_to date and the current row has none. Take the current rows only, and when a customer has two open rows (a load bug), keep the latest one and report the other.

  • history tables
  • scope
  • duplicates

Intermediate12 minPro

The file tells you the currency

Three country files have an amount column but no currency column: the UK file is in GBP, the US file in USD, the EU file in EUR. Stack them, turn the file name into a currency, and total per currency — never across currencies.

  • combining files
  • lineage
  • currency

Intermediate12 minPro

Still active when there is no end date

List the contracts active on 2026-06-30. A contract with no end date is still running, so 'end on or after the as-of date' must count a missing end as the far future, not as a failed comparison that drops the contract.

  • dates
  • intervals
  • missing values

Intermediate12 minPro

Sizes have an order, letters don't

Deliver stock sorted by SKU and then by size from small to large. Sorted as text, sizes come out L, M, S, XL. Map the spellings to standard sizes, look up each size's rank, and sort by the rank.

  • ordinal categories
  • mappings
  • sorting

Intermediate10 minPro

An audit log in true time order

Deliver an audit log sorted by when things happened. Entries were stamped in different time zones, so sorting the text puts 09:30-05:00 before 12:00Z although it happened later. Sort by instant, ties by id.

  • timestamps
  • sorting
  • delivery

Intermediate10 minPro

Look up a table in itself

Employees point at their manager by employee id, in the same table. Attach each manager's name by looking the table up in itself — joining in the right direction — and report managers that don't exist. The CEO is their own manager.

  • self joins
  • lookups
  • referential integrity

Intermediate10 minPro

One lookup table, two roles

Each flight has an origin and a destination airport, and both are looked up in the same airport table. Join it twice under different names, so the origin city and the destination city end up in separate columns, and report each kind of unknown airport separately.

  • joins
  • dimensions
  • renaming

Intermediate10 minPro

A total of nothing is zero

Report March refunds as one row: how many and how much. Some months have no refunds at all. SQL's SUM over no rows is NULL, not 0, so the dashboard would show a blank instead of 'no refunds'. The total of nothing is 0.00.

  • aggregation
  • nulls
  • KPIs

Intermediate10 minPro

Amounts that don't fit the column

The warehouse column is decimal(10,2): at most 8 digits before the point. An amount of 123456789.00 doesn't fit and is reported — not truncated, not stored as a float, and not silently turned into NULL without a reason.

  • numbers
  • precision
  • types

Intermediate12 minPro

A rate is the mean of a 0/1 column

Compute the conversion rate per channel. Encode converted as 1 and not converted as 0, and the rate is simply the mean. Visits with an unknown outcome are left out of both the numerator and the denominator, not counted as failures.

  • rates
  • encoding
  • aggregation

Intermediate10 minPro

Categories the model never saw

A model was trained on a fixed list of cities. New rows to score include cities it never saw, and some have no city. Map anything outside the training list — and missing values — to 'other', exactly as training did, so the model never meets an unknown value.

  • machine learning prep
  • categories
  • lookups

Intermediate12 minPro

Features only from before the prediction date

Build order-history features for a model that predicts from 2026-01-01. Only orders dated strictly before that day may count; an order on or after it is the future the model is trying to predict, and using it would leak the answer.

  • feature engineering
  • leakage
  • aggregation

Intermediate12 minPro

Totals per month from a wide sheet

A sheet has one row per store and one column per month. To total each month across stores, reshape to one row per store and month first. Blank cells are months a store didn't report: they are skipped, counted as missing coverage, never as 0.

  • unpivot
  • aggregation
  • coverage

Intermediate10 minPro

Digits that aren't ASCII

Quantities typed on a Japanese keyboard arrive as full-width digits like １２. The order system stores ASCII digits only, so they are reported for correction. Watch out: Python's int() and \d accept them silently.

  • unicode
  • validation
  • types

Intermediate10 minPro

Missing values group together

Remove duplicate contacts that share an email and a phone. Many contacts have no phone, and two rows with the same email and no phone are duplicates too: GROUP BY and PARTITION BY treat missing values as one group, but a self-join on phone = phone never matches them.

  • duplicates
  • nulls
  • sql

Intermediate10 minPro

Which feeds have gone stale

A load log records every successful load per source. For the 30 June check, report each source's number of loads and latest load instant, and flag sources whose latest load is before 2026-06-29 00:00 UTC as stale. Compare instants, not timestamp text.

  • monitoring
  • timestamps
  • aggregation

Intermediate12 minPro

Late events belong to their own month

Some March events only reached the warehouse in April, and some February events were loaded in early March. The March report is about when things happened: filter on event_at (as instants, in UTC), not on loaded_at.

  • timestamps
  • periods
  • late data

Intermediate10 minPro

Old orders for discontinued products

Enrich order lines with the product's price and status. A product that has since been discontinued still existed when it was ordered, so those orders stay — flagged, not rejected. Only SKUs the catalog has never heard of are errors.

  • lookups
  • flags
  • history

Intermediate10 minPro

Unanswered questions count in the denominator

Report, per survey question, how many respondents were asked and how many answered. Reshaping to long form must keep the unanswered cells: DuckDB's UNPIVOT drops NULLs by default, which would make every question look 100% answered.

  • missing values
  • unpivot
  • rates

Intermediate12 minPro

Units spelled many ways

Quantities come with units typed freely: kg, KGS, kilogram, g, grams, litre, pcs. Map the approved spellings to four unit codes, report units nobody approved, and total quantities per unit — never adding kilograms to grams.

  • mappings
  • units
  • aggregation

Intermediate12 minPro

The latest answer counts

People can resubmit a form, and only their last submission counts. Identify a person by normalized email within each form, compare submission times as instants across offsets, and report earlier submissions as replaced.

  • duplicates
  • timestamps
  • composite keys

Intermediate10 minPro

Versions are numbers, not text

Find the latest release of each app. A version is major.minor.patch, compared part by part as whole numbers: 1.10.0 is newer than 1.9.4, and 2.0.10 is newer than 2.0.9, although text comparison says the opposite.

  • types
  • sorting
  • survivor rules

advanced

Advanced15 minPro

Key-value attributes to columns

Product attributes are stored one per row (key-value). Keep the latest value of each attribute, then spread color, size and material into columns. Unknown attribute names are out of scope.

  • pivot
  • survivor rules
  • reshaping

Advanced14 minPro

Period close is an instant

The March books closed at 2026-04-01 00:00 UTC. Postings stamped with other offsets must be compared as instants: 01:30 in Berlin on 1 April is still March, and 20:00 in New York on 31 March is already April.

  • timestamps
  • time zones
  • scope

Advanced14 minPro

Pick the winning bid

Each auction lot has several bids. The winner is the highest valid bid; equal bids go to the one placed first (by instant), then the smallest bid ID. Every other bid on the lot is reported as outbid.

  • survivor rules
  • timestamps
  • ties

Advanced15 minPro

Order milestones as columns

Turn an order event log into a milestone table: for each order, the first time it was placed, shipped and delivered. Repeated events (a second shipping scan) must not move a milestone, and an order that hasn't reached a step stays missing there.

  • pivot
  • aggregation
  • timestamps

Advanced12 minPro

Pick the version in force on a date

The product catalog keeps every price version with a valid_from date, including versions scheduled for the future. Report the price in force on 2026-06-30: ignore future versions, then keep the latest remaining one per SKU.

  • as-of logic
  • deduplication
  • dates

Advanced14 minPro

Number format depends on the file

The German office sends 1.234,50 and the US office sends 1,234.50. After stacking the files, parse each amount with its file's separators: in the German file 12.500 is twelve thousand five hundred, in the US file 1.234 has too many decimals.

  • combining files
  • number formats
  • lineage

Advanced14 minPro

Date format depends on the country

Orders arrive from shops in several countries, and slash dates follow each country's convention: 04/03/2026 is 4 March in the UK and April 3rd in the US. Look up each country's date order, then parse with it.

  • dates
  • lookups
  • locales

Advanced14 minPro

Headline metrics as rows

A reporting tool wants metrics as rows (metric, value), not as one wide row. Compute the total, mean and median order value from valid amounts, then unpivot the three numbers into rows — all with the same type and 4 decimals, so they can share one value column.

  • aggregation
  • unpivot
  • types

Advanced14 minPro

Apply a change feed

A change feed lists inserts (I), updates (U) and deletes (D) with a sequence number. The current state of each key is its highest-numbered change — compared as a number, so 10 comes after 9 — and a key whose last change is a delete no longer exists.

  • change data capture
  • duplicates
  • ordering

Advanced18 minPro

Payroll hours per team

Turn raw timesheet entries into payroll hours per team: resolve each employee's team, validate the day and the hours, keep only the latest resubmission for each employee and day, and only then total. Every rejected row carries one reason.

  • pipelines
  • lookups
  • duplicates
  • aggregation