Practice
Intermediate35 minFree
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.
Beginner15 minFree
Several systems sent the same account. Keep exactly one row per account using a stated tie-break order, and list every row you removed.
Beginner10 minFree
The same accented name arrives in two Unicode forms and padded with unusual spaces. Make equal names equal without stripping accents.
Beginner15 minFree
Look up each order's customer segment. A missing key must never match the customer row whose key is also missing.
Intermediate15 minPro
Turn long readings into one row per entity with Jan, Feb and Mar columns. A reported zero is not the same as a month nobody reported.
Beginner15 minFree
Payments must point at a known account. Quarantine missing and unknown references with distinct reasons so every row is accounted for.
Intermediate15 minPro
An order has several lines and each line has versions. Keep the latest version of every line, not one row per order.
Beginner10 minFree
Mark sales above a review threshold instead of deleting them, and keep negative refunds as valid rows.
Intermediate20 minFree
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.
Intermediate20 minPro
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.
Intermediate15 minFree
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.
Advanced15 minPro
Fill missing test values with the median of the TRAINING data, flag every filled cell, and never let test values influence the fit.
Intermediate15 minFree
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.
Beginner8 minFree
This system treats email addresses as case-insensitive. Trim and lowercase them — and do nothing else: dots and plus tags stay.
Beginner15 minFree
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.
Intermediate25 minFree
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.
Intermediate20 minPro
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.
Beginner15 minFree
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.
Intermediate15 minFree
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.
Beginner10 minFree
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.
Beginner10 minPro
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.
Beginner10 minPro
Supplier records carry country codes typed by hand. Normalize them and accept only the five approved codes, reporting missing and unknown codes separately.
Beginner10 minPro
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.
Beginner10 minPro
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.
Intermediate12 minPro
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.
Intermediate12 minPro
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.
Beginner10 minPro
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.
Beginner10 minPro
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.
Intermediate12 minPro
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.
Beginner8 minPro
Validate order quantities and flag bulk orders (100 or more) for the warehouse, keeping every valid order in the output.
Beginner8 minPro
Attach department titles to employees. A missing or unknown department is not an error here: keep the employee and leave the title missing.
Intermediate12 minPro
Total expense amounts per category with exact decimals. Uncategorized expenses get their own group; malformed amounts are reported and left out of the totals.
Beginner10 minPro
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.
Beginner8 minPro
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.
Intermediate10 minPro
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.
Beginner8 minPro
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.
Beginner10 minPro
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.
Beginner8 minPro
A contact needs a name and an email. Whitespace-only values count as missing. Report the first missing field in the declared order.
Intermediate10 minPro
Delivery times have a long tail. Report the median days per carrier (robust to extremes) with the number of deliveries, after rejecting malformed durations.
Beginner8 minPro
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.
Beginner10 minPro
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.
Beginner10 minPro
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.
Intermediate14 minPro
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.
Intermediate10 minPro
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.
Intermediate12 minPro
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.
Intermediate12 minPro
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.
Beginner10 minPro
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.
Beginner10 minPro
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.
Beginner10 minPro
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.
Intermediate12 minPro
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.
Intermediate10 minPro
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.
Intermediate10 minPro
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.
Intermediate12 minPro
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.
Intermediate10 minPro
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.
Intermediate12 minPro
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.
Intermediate10 minPro
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.
Beginner10 minPro
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.
Intermediate12 minPro
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.
Intermediate14 minPro
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.
Intermediate12 minPro
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.
Beginner8 minPro
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.
Beginner8 minPro
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.
Intermediate12 minPro
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.
Intermediate12 minPro
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.
Intermediate10 minPro
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.
Beginner8 minPro
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.
Intermediate10 minPro
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.
Beginner10 minPro
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.
Intermediate10 minPro
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.
Intermediate12 minPro
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.
Beginner8 minPro
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.
Intermediate10 minPro
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.
Advanced15 minPro
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.
Intermediate12 minPro
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.
Advanced14 minPro
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.
Intermediate12 minPro
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.
Intermediate10 minPro
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.
Intermediate10 minPro
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.
Intermediate12 minPro
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.
Intermediate12 minPro
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.
Beginner8 minPro
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.
Beginner8 minPro
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.
Intermediate10 minPro
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.
Intermediate10 minPro
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.
Intermediate12 minPro
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'.
Beginner10 minPro
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.
Intermediate10 minPro
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.
Intermediate12 minPro
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.
Beginner8 minPro
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.
Intermediate10 minPro
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.
Beginner8 minPro
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.
Beginner10 minPro
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.
Intermediate12 minPro
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.
Advanced14 minPro
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.
Intermediate10 minPro
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.
Intermediate10 minPro
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.
Intermediate10 minPro
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.
Intermediate12 minPro
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.
Intermediate10 minPro
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.
Advanced15 minPro
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.
Intermediate10 minPro
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.
Intermediate10 minPro
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.
Intermediate10 minPro
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.
Intermediate10 minPro
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.
Intermediate12 minPro
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.
Intermediate12 minPro
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.
Beginner8 minPro
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.
Intermediate10 minPro
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.
Beginner8 minPro
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.
Intermediate10 minPro
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.
Intermediate10 minPro
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.
Intermediate12 minPro
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.
Intermediate10 minPro
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.
Beginner8 minPro
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.
Intermediate10 minPro
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.
Intermediate10 minPro
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.
Beginner8 minPro
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.
Beginner8 minPro
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.
Beginner10 minPro
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.
Advanced12 minPro
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.
Beginner8 minPro
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.
Intermediate10 minPro
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.
Intermediate12 minPro
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.
Intermediate12 minPro
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.
Intermediate10 minPro
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.
Beginner10 minPro
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.
Intermediate10 minPro
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.
Intermediate12 minPro
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.
Intermediate10 minPro
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.
Beginner8 minPro
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.
Intermediate10 minPro
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.
Intermediate10 minPro
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.
Beginner10 minPro
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.
Beginner8 minPro
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.
Intermediate10 minPro
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.
Advanced14 minPro
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.
Intermediate12 minPro
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.
Intermediate10 minPro
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.
Beginner8 minPro
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.
Intermediate12 minPro
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.
Intermediate10 minPro
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.
Intermediate10 minPro
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.
Intermediate12 minPro
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.
Intermediate12 minPro
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.
Beginner8 minPro
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.
Beginner8 minPro
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.
Beginner8 minPro
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.
Intermediate12 minPro
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.
Intermediate12 minPro
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.
Beginner8 minPro
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.
Beginner8 minPro
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.
Intermediate10 minPro
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.
Advanced14 minPro
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.
Beginner8 minPro
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.
Intermediate10 minPro
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.
Intermediate10 minPro
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.
Intermediate10 minPro
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.
Intermediate10 minPro
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.
Intermediate12 minPro
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.
Beginner8 minPro
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.
Intermediate10 minPro
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.
Intermediate12 minPro
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.
Beginner8 minPro
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.
Intermediate12 minPro
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.
Advanced14 minPro
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.
Intermediate10 minPro
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.
Intermediate10 minPro
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.
Beginner8 minPro
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.
Intermediate10 minPro
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.
Intermediate12 minPro
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.
Intermediate10 minPro
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.
Advanced14 minPro
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.
Advanced18 minPro
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.
Intermediate10 minPro
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.
Intermediate12 minPro
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.
Intermediate12 minPro
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.
Beginner8 minPro
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.
Beginner8 minPro
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.
Beginner8 minPro
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.
Intermediate10 minPro
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.