Data Cleaning Exercises

Exercise · beginner · 15 min

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.

The rules

These rules decide what a correct result is. Your answer is checked on this dataset and on hidden datasets that follow the same rules.

Input
  records(id, account, updated, priority) — all text. id is unique. account keeps its leading zeros.
  updated is an ISO 8601 timestamp with an explicit offset; priority is a whole number. Both are always present and valid.

Rule
  Keep one row per account. The survivor is the row with:
    1. the latest updated instant (compare instants, not text: 01:00+01:00 equals 00:00Z),
    2. then the higher priority,
    3. then the lowest id (code-point order).
  Every other row goes to exceptions with reason "duplicate".

Outputs (row order does not matter)
  clean(id text, account text, updated timestamptz [UTC], priority int64)
  exceptions(id text, reason text)

The data

records · 13 rows, first 12 shown

idaccountupdatedpriority
rllcz0000642026-03-10T23:15:00Z9
rsilp0004582026-03-11T00:15:00+01:0010
r35yg0000642026-02-01T00:00:00Z3
rwy4c0008752026-03-10T23:15:00Z1
reqt90000642026-01-15T08:00:00Z9
r3mue0007482026-02-01T12:30:00Z2
rsiq70004582026-02-01T00:00:00Z12
rfk2c0004582026-03-11T00:15:00+01:0012
rxvfh0004582026-02-01T12:30:00Z9
r06sr0009412026-02-01T00:00:00Z1
r4gch0007482026-01-15T08:00:00Z2
r8klo0000642026-03-10T23:15:00Z1

What you practise

  • deduplication
  • tie-breaks
  • timestamps

Your answer is also checked on hidden datasets generated from the same rules.

Download this dataset (CSV) with its data dictionary

Sign in to bookmark this and keep private notes.