SQL Quest

Lessons, hints, explained solutions, the in-browser PostgreSQL editor and practice answer checks in your browser are available. Graded assessments, recorded progress from answer checks and new certificates aren't available yet.

Data Cleaning and Transformation

Real data is typed by people and exported by systems that disagree: duplicate customer registrations, coupon codes in every case, cities with stray spaces, missing timestamps, impossible stock levels and salaries outside their band. In this track you clean that data with SQL on the Wadi Market shop, Shifa Clinics, Sahra Logistics and Masar Holding HR datasets. You relabel values with CASE, handle NULLs honestly with COALESCE and NULLIF, cast types safely, take text apart with string functions and regular expressions, work with dates and durations, standardize categories with mapping tables, find and remove duplicates with a stated survivor rule, profile missing data, write validation queries, and finally combine it all into an analysis-ready dataset whose grain and totals you can prove.

Start the track

Lessons

  1. CASE expressionsTurn raw values into business categories with simple and searched CASE, and understand why the order of WHEN branches decides the answer.18 min · 4 exercisesFree
  2. COALESCE and NULLIFReplace missing values with sensible defaults, stop NULL from wiping out calculations, and divide safely with NULLIF.16 min · 4 exercises Members
  3. CAST and data typesConvert values between types with CAST and ::, avoid integer division and rounding errors, and validate text before casting it so one bad value cannot break a query.18 min · 4 exercises Members
  4. String functionsClean, split, extract and assemble text with lower/upper/initcap, trim, split_part, substring, position, replace, length and concatenation.20 min · 4 exercises Members
  5. Pattern matching with regular expressionsValidate formats, find messy values and extract or rewrite parts of text with ~, ~*, regexp_replace, regexp_match and substring.22 min · 4 exercises Members
  6. Date and time functionsBucket, take apart, format and build dates with date_trunc, extract, to_char, age, make_date and interval literals.20 min · 4 exercises Members
  7. Date arithmetic and durationsMeasure durations in days, hours and minutes, compare timestamps with dates correctly, and understand why business days need extra care.20 min · 4 exercises Members
  8. Standardizing inconsistent valuesCollapse spelling variants into one canonical value, map codes to business categories with CASE or a mapping table, and keep unmapped values visible.20 min · 4 exercises Members
  9. Finding duplicatesDetect exact and near-duplicate records with GROUP BY … HAVING count(*) > 1 on a well-chosen, normalised key, and list every row involved with a window count.20 min · 4 exercises Members
  10. Removing duplicates and keeping one rowPick one survivor per duplicate group with ROW_NUMBER, build a duplicate-to-survivor map, and delete duplicates safely from a working copy.22 min · 4 exercises Members
  11. Profiling and handling missing dataMeasure how much data is missing, tell structural gaps from defects, check whether gaps are concentrated somewhere, and decide whether to exclude, flag or fill.20 min · 4 exercises Members
  12. Validation queriesWrite queries that test data against rules: row counts, uniqueness, referential integrity, allowed values and ranges, cross-table reconciliation, and a scorecard of checks.22 min · 4 exercises Members
  13. Preparing an analysis-ready datasetCombine cleaning, deduplication, type fixes and validation into one readable CTE pipeline that produces a table with a clear grain, clean columns and documented decisions.25 min · 4 exercises Members