# Help: `deduplication` (concept)

Keep one row per key inside an array of rows. The pattern is always: assign `ROW_NUMBER()` in a `TRANSFORM`, then filter to `row_num = 1` in a second `TRANSFORM`.

## Keep the first row per key (earliest by sort order)

```featureql
-- ROW_NUMBER() restarts at 1 for each key group; ORDER BY controls which row is "first"
DEDUPED := arr.TRANSFORM(
    RETURN *, ROW_NUMBER() OVER (PARTITION BY key_field ORDER BY ts ASC) AS row_num
).TRANSFORM(
    RETURN * WHERE row_num = 1
),
```

Runnable snippet: [`help_dedup_first_per_key`](/llms/help_dedup_first_per_key.md)

## Keep the last row per key (most recent)

Same pattern, reverse the `ORDER BY`:

```featureql
-- ORDER BY ts DESC makes the most recent row number 1
LATEST_PER_KEY := arr.TRANSFORM(
    RETURN *, ROW_NUMBER() OVER (PARTITION BY key_field ORDER BY ts DESC) AS row_num
).TRANSFORM(
    RETURN * WHERE row_num = 1
),
```

Runnable snippet: [`help_dedup_last_per_key`](/llms/help_dedup_last_per_key.md)

## Keep top N per key

Replace `= 1` with `<= N`:

```featureql
-- Top 2 by amount per category
TOP2_BY_CAT := arr.TRANSFORM(
    RETURN *, ROW_NUMBER() OVER (PARTITION BY category ORDER BY amount DESC) AS row_num
).TRANSFORM(
    RETURN * WHERE row_num <= 2
),
```

## ⚠ Shadow alias gotcha

The output alias cannot reuse an input field name. If your array already has a field called `row_num`, use a different name (e.g. `dedup_rn`).

```featureql
-- WRONG if 'row_num' already exists as a row field
RETURN *, ROW_NUMBER() OVER (...) AS row_num   -- TRANSFORM-OUTPUT-SHADOWS-INPUT error

-- RIGHT: use a distinct name
RETURN *, ROW_NUMBER() OVER (...) AS dedup_rn
```

# Help: `deduplication` (snippet)

## `help_dedup_first_per_key`

```slt
# tags: deduplication, first_last_per_group, row_number
# schema: ARRAY<ROW<id BIGINT, key_field VARCHAR, ts DATE, value BIGINT>>
query OUT:Q
DEFINE
    -- Three keys (A, B, C); A and B each have two rows — keep the earliest per key
    events := ZIP(
        ARRAY(1::BIGINT, 2::BIGINT, 3::BIGINT, 4::BIGINT, 5::BIGINT) AS id,
        ARRAY('A', 'B', 'A', 'C', 'B') AS key_field,
        ARRAY(
            DATE '2024-01-10',
            DATE '2024-01-05',
            DATE '2024-01-20',
            DATE '2024-01-15',
            DATE '2024-02-01'
        ) AS ts,
        ARRAY(100::BIGINT, 200::BIGINT, 150::BIGINT, 300::BIGINT, 50::BIGINT) AS value
    ),
    deduped := events.TRANSFORM(
        RETURN
            *,
            dedup_rn := ROW_NUMBER() OVER (PARTITION BY key_field ORDER BY ts ASC)
    ).TRANSFORM(
        RETURN
            id,
            key_field,
            ts,
            value
        WHERE dedup_rn = 1
        ORDER BY key_field
    )
RETURN
    out := deduped
;
----
[{id: 1, key_field: A, ts: 2024-01-10, value: 100}, {id: 2, key_field: B, ts: 2024-01-05, value: 200}, {id: 4, key_field: C, ts: 2024-01-15, value: 300}]
```

## `help_dedup_last_per_key`

```slt
# tags: deduplication, first_last_per_group, keep_last, row_number
# schema: ARRAY<ROW<id BIGINT, key_field VARCHAR, ts DATE, value BIGINT>>
query OUT:Q
DEFINE
    events := ZIP(
        ARRAY(1::BIGINT, 2::BIGINT, 3::BIGINT, 4::BIGINT, 5::BIGINT) AS id,
        ARRAY('A', 'B', 'A', 'C', 'B') AS key_field,
        ARRAY(
            DATE '2024-01-10',
            DATE '2024-01-05',
            DATE '2024-01-20',
            DATE '2024-01-15',
            DATE '2024-02-01'
        ) AS ts,
        ARRAY(100::BIGINT, 200::BIGINT, 150::BIGINT, 300::BIGINT, 50::BIGINT) AS value
    ),
    latest := events.TRANSFORM(
        RETURN
            *,
            dedup_rn := ROW_NUMBER() OVER (PARTITION BY key_field ORDER BY ts DESC)
    ).TRANSFORM(
        RETURN
            id,
            key_field,
            ts,
            value
        WHERE dedup_rn = 1
        ORDER BY key_field
    )
RETURN
    out := latest
;
----
[{id: 3, key_field: A, ts: 2024-01-20, value: 150}, {id: 5, key_field: B, ts: 2024-02-01, value: 50}, {id: 4, key_field: C, ts: 2024-01-15, value: 300}]
```

**Related tags**: [`transform_window`](/llms/transform_window.md) [`transform_filter`](/llms/where_vs_filter.md) [`transform_limit`](/llms/transform_window.md) [`episode_merging`](/llms/episode_merging.md) [`locf`](/llms/locf.md)