All challenge writeups

Give me your best AI Engineer

Canonicalization, deduplication and stable joins reveal a message buried in messy customer and order data.

Overview

You’re given three messy datasets (customers.csv, orders.csv, aliases.csv) along with cryptic notes about canonicalization and duplicates. Your task is to clean, deduplicate, and integrate them properly. If you follow the correct data warehousing rules, a hidden flag is revealed: one character is embedded every 137th row of the final dataset.

Phase 1 — Standardize & Canonicalize the Data

What to do (explanation only)

  1. Normalize Headers
    • Lowercase all headers, strip whitespace, collapse multiple spaces.
    • Apply the alias mapping from aliases.csv (e.g., "e-mail", "email_address" → "email").
  2. Canonicalize Email Keys
    • Convert email to lowercase.
    • Apply Unicode NFC normalization.
    • Remove only spaces (keep and _, as they are meaningful identifiers).
  3. Deduplicate Customers
    • Group by canonical email.
    • Keep the record with the earliest created_at timestamp.
  4. Deduplicate Orders
    • Group by order_id.
    • Keep the record with the highest version (lower versions contain decoy bytes).

Code snippet — header normalization + email canonicalization

import pandas as pd, unicodedata, re

def load_aliases():
    aliases = pd.read_csv("aliases.csv")
    return dict(zip(aliases["alias"], aliases["canonical"]))

def normalize_headers(df, aliases):
    norm_cols = []
    for col in df.columns:
        col = col.lower().strip()
        col = re.sub(r"\s+", " ", col)
        norm_cols.append(aliases.get(col, col))
    df.columns = norm_cols
    return df

def canonicalize_email(email):
    if pd.isna(email): return email
    email = email.lower()
    email = unicodedata.normalize("NFC", email)
    return email.replace(" ", "")

Phase 2 — Deduplicate & Join

What to do (explanation only)

  • Customers: after canonicalization, sort by created_at and keep the first row per canonical key.
  • Orders: sort by order_id + version (descending), then keep the first per order_id.
  • Join Strategy: Inner join orders ↔ customers on the canonical key.
  • Stable Sorting: Sort the joined dataset by:
    1. region (alphabetical: East, North, South, West)
    2. order_date (chronological)
    3. order_id (alphanumeric)

Code snippet — deduplication + join

def process_customers(df):
    df["canonical_key"] = df["email"].apply(canonicalize_email)
    df["created_at"] = pd.to_datetime(df["created_at"])
    df = df.sort_values(["canonical_key", "created_at"])
    return df.groupby("canonical_key").first().reset_index()

def process_orders(df):
    df["canonical_key"] = df["customer_key"].apply(canonicalize_email)
    df["order_date"] = pd.to_datetime(df["order_date"])
    df = df.sort_values(["order_id", "version"], ascending=[True, False])
    return df.groupby("order_id").first().reset_index()

# Join and sort
result = pd.merge(orders_processed, customers_processed,
                  on="canonical_key", how="inner")
result = result.sort_values(["region", "order_date", "order_id"]).reset_index(drop=True)

Phase 3 — Extract the Flag (every 137th row)

What to do (explanation only)

  • After sorting, read every 137th row (positions 137, 274, 411, …).
  • Extract the byte_code field.
  • Convert each byte_code into an ASCII character.
  • Concatenate characters to form the flag.

Code snippet — flag extraction

flag_bytes = []
for i in range(136, len(result), 137):  # 0-indexed → row 137
    flag_bytes.append(result.iloc[i]["byte_code"])

flag = "".join(chr(b) for b in flag_bytes)
print("Flag:", flag)
  • Final Flag: CONCTF{Data_2L3An_1N5iGht}

Original Notion writeup