Hash Tables

First Non-Repeating Value

Find the first input value whose final frequency is one.

Algorithm

Canonical input [3, 5, 2, 5, 3, 8, 2] prints 8. The replay uses the same input in every language, so this SQL DSA implementation can be compared directly with the rest of the DSA track.

two-pass lookup The first pass builds a frequency table. The second pass keeps the original order and stops at the first value with frequency one.

Basic Implementation

basic.sql
Replay: real traced execution (multi-file project)
WITH input(pos, value) AS (
  VALUES (1, 3), (2, 5), (3, 2), (4, 5), (5, 3), (6, 8), (7, 2)
),
counts AS (
  SELECT value, COUNT(*) AS freq FROM input GROUP BY value
)
SELECT input.value
FROM input JOIN counts USING (value)
WHERE counts.freq = 1
ORDER BY input.pos
LIMIT 1;

  1. arr ← [3, 5, 2, 5, 3, 8, 2]

    1WITH input(pos, value) AS (2  VALUES (1, 3), (2, 5), (3, 2), (4, 5), (5, 3), (6, 8), (7, 2)
    values this step[3, 5, 2, 5, 3, 8, 2]arr

  2. count ← {}

    3),4counts AS (5  SELECT value, COUNT(*) AS freq FROM input GROUP BY value
    values this step{}count

  3. count ← {3: 1}

    3),4counts AS (5  SELECT value, COUNT(*) AS freq FROM input GROUP BY value
    values this step{} → {3: 1}count3value

  4. count ← {3: 1, 5: 1}

    3),4counts AS (5  SELECT value, COUNT(*) AS freq FROM input GROUP BY value
    values this step{3: 1} → {3: 1, 5: 1}count5value

  5. count ← {3: 1, 5: 1, 2: 1}

    3),4counts AS (5  SELECT value, COUNT(*) AS freq FROM input GROUP BY value
    values this step{3: 1, 5: 1} → {3: 1, 5: 1, 2: 1}count2value

  6. count ← {3: 1, 5: 2, 2: 1}

    3),4counts AS (5  SELECT value, COUNT(*) AS freq FROM input GROUP BY value
    values this step{3: 1, 5: 1, 2: 1} → {3: 1, 5: 2, 2: 1}count5value

  7. count ← {3: 2, 5: 2, 2: 1}

    3),4counts AS (5  SELECT value, COUNT(*) AS freq FROM input GROUP BY value
    values this step{3: 1, 5: 2, 2: 1} → {3: 2, 5: 2, 2: 1}count3value

  8. count ← {3: 2, 5: 2, 2: 1, 8: 1}

    3),4counts AS (5  SELECT value, COUNT(*) AS freq FROM input GROUP BY value
    values this step{3: 2, 5: 2, 2: 1} → {3: 2, 5: 2, 2: 1, 8: 1}count8value

  9. count ← {3: 2, 5: 2, 2: 2, 8: 1}

    3),4counts AS (5  SELECT value, COUNT(*) AS freq FROM input GROUP BY value
    values this step{3: 2, 5: 2, 2: 1, 8: 1} → {3: 2, 5: 2, 2: 2, 8: 1}count2value

  10. i ← 0, value ← 3, count[value] ← 2, found ← no

    4counts AS (5  SELECT value, COUNT(*) AS freq FROM input GROUP BY value6)
    values this step0i3value2count[value]nofound{3: 2, 5: 2, 2: 2, 8: 1}count

  11. i ← 1, value ← 5, count[value] ← 2, found ← no

    4counts AS (5  SELECT value, COUNT(*) AS freq FROM input GROUP BY value6)
    values this step1i5value2count[value]nofound{3: 2, 5: 2, 2: 2, 8: 1}count

  12. i ← 2, value ← 2, count[value] ← 2, found ← no

    4counts AS (5  SELECT value, COUNT(*) AS freq FROM input GROUP BY value6)
    values this step2i2value2count[value]nofound{3: 2, 5: 2, 2: 2, 8: 1}count

  13. i ← 3, value ← 5, count[value] ← 2, found ← no

    4counts AS (5  SELECT value, COUNT(*) AS freq FROM input GROUP BY value6)
    values this step3i5value2count[value]nofound{3: 2, 5: 2, 2: 2, 8: 1}count

  14. i ← 4, value ← 3, count[value] ← 2, found ← no

    4counts AS (5  SELECT value, COUNT(*) AS freq FROM input GROUP BY value6)
    values this step4i3value2count[value]nofound{3: 2, 5: 2, 2: 2, 8: 1}count

  15. i ← 5, value ← 8, count[value] ← 1, found ← yes, result ← 8

    4counts AS (5  SELECT value, COUNT(*) AS freq FROM input GROUP BY value6)
    values this step5i8value1count[value]yesfound8result{3: 2, 5: 2, 2: 2, 8: 1}count

  16. stdout ← 8

    4counts AS (5  SELECT value, COUNT(*) AS freq FROM input GROUP BY value6)
    values this step8stdout8result

Complexity

  • Time: O(n) average
  • Space: O(k) for k distinct values

Implementation notes

  • Keep output formatting deterministic. Do not rely on unordered hash-map printing when the lesson needs cross-language comparison.
  • The trace highlights the hash table state after each write.