イベント・潮位別成功率

event_type_wave_type_stats.sql

WITH all_waves AS (
  SELECT
    CONCAT(
      CAST(job_id AS VARCHAR),
      '-',
      CAST(details.quota_num AS VARCHAR)
    ) AS wave_id,
    job_id,
    details.quota_num,
    details.event_type.name AS event,
    details.water_level.name AS water_level
  FROM
    salmon_run_stats
    CROSS JOIN UNNEST(wave_details) AS t(details)
),
failure_waves AS (
  SELECT
    CONCAT(
      CAST(job_id AS VARCHAR),
      '-',
      CAST(
        element_at(wave_details, job_result.failure_wave).quota_num AS VARCHAR
      )
    ) AS wave_id,
    false AS is_clear
  FROM
    salmon_run_stats
  WHERE
    job_result.is_clear = false
)
SELECT
  all_waves.water_level,
  all_waves.event,
  COUNT(*) AS count,
  COUNT(
    is_clear = false
    OR NULL
  ) AS failure_count,
  100 - (CAST(
    COUNT(
      is_clear = false
      OR NULL
    ) AS DOUBLE
  ) / COUNT(*) * 100) AS clear_rate
FROM
  all_waves
  LEFT JOIN failure_waves ON all_waves.wave_id = failure_waves.wave_id
GROUP BY
  1,
  2