Mantendo uma Linha por Chave

Parte da seção Além do Básico do Journey de SQL da Coddy — lição 26 de 27.

Você frequentemente terá uma tabela onde uma chave (um cliente, um sensor, um produto) aparece várias vezes e você quer manter apenas a mais recente, ou aquela com a maior pontuação.

Use ROW_NUMBER() da mesma forma que você fez para o top-N, então mantenha rn = 1.

WITH ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY customer_id ORDER BY purchased_at DESC
           ) AS rn
    FROM purchases
)
SELECT * FROM ranked WHERE rn = 1

O PARTITION BY diz "um ranking por esta chave"; o ORDER BY diz "o mais recente primeiro". A linha 1 em cada partição é a linha que você deseja manter.

Desafio

Médio

Tabelas e colunas disponíveis:
readings: sensor_id, recorded_at, value

Para cada sensor que possua pelo menos 3 leituras, retorne sua única leitura de maior valor. Se duas leituras empatarem no value, mantenha a mais recente.

Exiba sensor_id, recorded_at, value, ordenados por sensor_id.

Folha de consulta

Use ROW_NUMBER() com PARTITION BY para manter apenas a linha mais recente (ou a melhor) por chave e, em seguida, filtre por rn = 1:

WITH ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY customer_id ORDER BY purchased_at DESC
           ) AS rn
    FROM purchases
)
SELECT * FROM ranked WHERE rn = 1

PARTITION BY — redefine a classificação para cada chave única
ORDER BY ... DESC — coloca a linha desejada em primeiro lugar (mais recente, mais alta, etc.)
WHERE rn = 1 — mantém apenas a linha superior por partição

Experimente você mesmo

WITH ranked AS (
    -- ROW_NUMBER() OVER (PARTITION BY sensor_id ORDER BY value DESC, recorded_at DESC)
    -- COUNT(*) OVER (PARTITION BY sensor_id) para filtrar sensores com >= 3 leituras
)
SELECT sensor_id, recorded_at, value
FROM ranked
WHERE rn = 1 AND total_readings >= 3
ORDER BY sensor_id