Conservar una fila por clave

Parte de la sección Más allá de lo básico del Journey de SQL de Coddy. Lección 26 de 27.

A menudo tendrás una tabla en la que una clave (un cliente, un sensor o un producto) aparece varias veces y solo quieres conservar la más reciente o la que tenga la puntuación más alta.

Usa ROW_NUMBER() de la misma manera que lo hiciste para top-N, después conserva 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

El PARTITION BY indica «una clasificación por cada clave esta»; el ORDER BY indica «los más recientes primero». La fila 1 de cada partición es la fila que quieres conservar.

Desafío

Intermedio

Tablas y columnas disponibles:
readings: sensor_id, recorded_at, value

Para cada sensor que tenga al menos 3 lecturas, devuelve su única lectura de mayor valor. Si dos lecturas empatan en value, conserva la más reciente.

Devuelve sensor_id, recorded_at, value, ordenados por sensor_id.

Pruébalo tú mismo

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 con >= 3 lecturas
)
SELECT sensor_id, recorded_at, value
FROM ranked
WHERE rn = 1 AND total_readings >= 3
ORDER BY sensor_id