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 em que 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 top-N e, em seguida, 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 significa “uma classificação por esta chave”; o ORDER BY significa “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 tenha pelo menos 3 leituras, retorne sua única leitura de maior valor. Se duas leituras empatarem em value, mantenha a mais recente.

Exiba sensor_id, recorded_at, value, ordenados por sensor_id.

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