SQL · Project Planning: ventanas y CTE

Esta serie mantiene la mente fresca resolviendo, uno a uno, los ejercicios de HackerRank. Cada entrada toma un tema concreto y lo agota; todas las consultas están en MySQL salvo donde se indique.

Te invito a intentar cada ejercicio antes de leer la solución.

Projects

SQL Project Planning

WITH
  Projects_S AS (
    SELECT
      p.Start_Date,
      p.End_Date,
      SUM(
        CASE WHEN p.Previous_End = p.Start_Date THEN 0 ELSE 1 END
      ) OVER (
        ORDER BY
          p.Start_Date
      ) AS Project
    FROM
      (
        SELECT
          *,
          LAG(End_Date) OVER (
            ORDER BY
              Start_Date
          ) AS Previous_End
        FROM
          Projects
      ) p
  )
SELECT
  MIN(p.Start_Date),
  MAX(p.End_Date)
FROM
  Projects_S p
GROUP BY
  p.Project
ORDER BY
  DATEDIFF(
    MAX(p.End_Date),
    MIN(p.Start_Date)
  )

Antes realmente para poder desmenuzar cada parte de la query debemos hacernos diversas preguntas, el enunciado nos pide de antemano organizar los proyectos por su fecha de entrada hasta su fecha final, por lo que debemos saber cual es el punto quiebre de cuando empieza un nuevo proyecto que es cuando un nuevo proyecto no tiene la fecha anterior del día por el cual se finalizo ese día para continuar con el próximo, para identificarlo realicemos algo básico como

SELECT * FROM Projects ORDER BY Start_Date

Como vemos empieza el primer proyecto por 2015-10-01 y termina por 2015-10-05 siendo el primer proyecto, ya que luego en la fila 5, 2015-10-11 el cual su fecha de End_Date no es 2015-10-05 sino 2015-10-13 y por eso ahí empieza otro proyecto.

Ahora esta parte puede sonar un poco confusa, pero la manera correcta para identificar este proceso de manera lineal es comparar Start_Date que por ejemplo en este caso tenemos, 2015-10-04 y el End_Date anterior era 2015-10-04 por lo que podemos saber sigue siendo parte del primer proyecto, cuando al pasar a 2015-10-11 no coincide con el anterior End_Date que es 2015-10-05es porque ya es un proyecto nuevo, siendo el segundo en empezar.

Por lo que para realizar este calculo por cada fila, creamos una nueva columna.

SELECT
  *,
  LAG(End_Date) OVER (
    ORDER BY
      Start_Date
  ) AS Previous_End
FROM
  Projects

Ahora teniendo esta columna llamada Previous_End tendríamos como resultado;

Como nos damos cuenta ahora tanto Start_Date como Previous_End tienen mismo valor por lo explicado anteriormente, y inicialmente tenemos un NULL, y los que empiezan por un proyecto nuevo su Previous_End es diferente.

Ahora este resultado, lo volvemos como subconsulta ya que necesitamos, realizar otro calculo a cada fila respecto a esta nueva columna que tenemos.

SubConsulta

FROM
  (
    SELECT
      *,
      LAG(End_Date) OVER (
        ORDER BY
          Start_Date
      ) AS Previous_End
    FROM
      Projects
  ) p

Ahora la magia es saber si Previous_End coincide con Start_Date y eso sea una suma acumulativa en caso de no coincidir ya que seria un proyecto nuevo, por ejemplo, si coinciden ambas columnas daría como resultado 0, y asi sucesivamente, pero que pasa cuando no coinciden? sumamos todos esos 0 con 1 que daría como resultado 1, y luego nuevamente, y ya teniendo el 1 anterior tendríamos 2, y así sucesivamente.

Un ejemplo de esto.

La suma acumulativa del calculo la hacemos por la función SUM de esta forma podemos hacerlo por cada fila usamos una ventana, y ordenamos claramente para este calculo por Start_Date.

Las sumas se vería representada de esta forma

1
1 + 0 = 1
1 + 0 + 0 = 1
1 + 0 + 0 + 1 = 2
1 + 0 + 0 + 1 + 0 = 2

Ahora teniendo

SELECT
  p.Start_Date,
  p.End_Date,
  SUM(
    CASE WHEN p.Previous_End = p.Start_Date THEN 0 ELSE 1 END
  ) OVER (
    ORDER BY
      p.Start_Date
  ) AS Project

Ahora haciendo una  CTE (Common Table Expression) de todo esta query, ahora tenemos que agrupar por esta suma acumulativa solo sacar 1, solo un 2, etc. Para esto aunque hagamos un GROUP BY de la columna Project debemos igual tener un SELECT para que nos de el resultado por el patrón que esperamos que sea agrupe que es sacando la fecha mas pequeña del proyecto por donde empezó con la fecha final, ordenados con DATEDIFF por la cantidad menor de días que tiene un proyecto.

SELECT
  MIN(p.Start_Date),
  MAX(p.End_Date)
FROM
  Projects_S p
GROUP BY
  p.Project
ORDER BY
  DATEDIFF(
    MAX(p.End_Date),
    MIN(p.Start_Date)
  )