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.

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