resolve rounding issues

WindowFunctions-QueryB-ResolvingRoundingIssues.sql

WITH prep AS (
   SELECT day                                                      AS day_of_week
        , COUNT(*)                                                 AS dow_accidents
        , SUM(dow_accidents) OVER (PARTITION BY 0)                 AS total_accidents
        
        /* Create a column with the percentage calc, but make it null on Saturday */
        , CASE
             WHEN day_of_week != '7. Saturday'
                THEN ROUND(dow_accidents / total_accidents, 4) END AS prep_accidents_percent
   FROM temp.CAMBRIDGESHIRE_ACCIDENTS
   WHERE accident_ts BETWEEN '2017-01-01'::date AND '2017-01-31'::date
   GROUP BY 1)
   
SELECT day_of_week                        AS day_of_week
     , dow_accidents                      AS dow_accidents
     , total_accidents                    AS total_accidents
     
     -- For Saturday (=where is null), calculate as 1-SUM(everything else)
     , CASE
          WHEN prep_accidents_percent IS NULL
             THEN 1.0000 - SUM(prep_accidents_percent) OVER (PARTITION BY 0)
          ELSE prep_accidents_percent END AS dow_accidents_percent
FROM prep