subsidio_sumario_q_consorcio.sql

# Sumário por quinzena e consórcio
WITH
  sumario AS (
  SELECT
    EXTRACT(YEAR
    FROM
      DATA) AS ano,
    EXTRACT(MONTH
    FROM
      DATA) AS mes,
    CASE
      WHEN EXTRACT(DAY FROM DATA) < 16 THEN '1Q'
    ELSE
    '2Q'
  END
    AS quinzena,
    consorcio,
    SUM(distancia_total_planejada) AS distancia_total_planejada,
    SUM(distancia_total_subsidio) AS distancia_total_subsidio
  FROM
    `rj-smtr.dashboard_subsidio_sppo.sumario_dia`
  GROUP BY
    1,
    2,
    3,
    4 )
SELECT
  *,
  IF (distancia_total_planejada = 0, NULL, ROUND(100*distancia_total_subsidio/distancia_total_planejada, 2)) AS perc_distancia_total_subsidio
FROM
  sumario
ORDER BY
  ano,
  mes,
  quinzena