--- a/schema/default_sysconfig.sql	Mon Sep 16 16:56:11 2019 +0200
+++ b/schema/default_sysconfig.sql	Fri Sep 20 15:35:16 2019 +0200
@@ -43,7 +43,7 @@
         r.rwls AS reference_water_levels,
         wl.measure_date AS gm_measuredate,
         wl.water_level AS gm_waterlevel,
-        wl_14d.n AS gm_n_14d,
+        wl.n AS gm_n_14d,
         fca.forecast_accuracy_3d,
         fca.forecast_accuracy_1d
     FROM waterway.gauges g
@@ -56,29 +56,32 @@
         LEFT JOIN (SELECT DISTINCT ON (location)
                     location,
                     measure_date,
-                    water_level
-                FROM waterway.gauge_measurements
-                ORDER BY location, measure_date DESC) AS wl
-            USING (location)
-        LEFT JOIN (SELECT location, count(water_level) AS n
+                    water_level,
+                    count(*) OVER (PARTITION BY location) AS n
                 FROM waterway.gauge_measurements
                 -- consider all measurements within 14 days plus a tolerance
                 WHERE measure_date
                     >= current_timestamp - '14 days 00:15'::interval
-                GROUP BY location) AS wl_14d
+                ORDER BY location, measure_date DESC) AS wl
             USING (location)
-        LEFT JOIN (SELECT location,
-                    max(acc) FILTER (WHERE
-                        measure_date <= current_timestamp + '1 day'::interval)
-                        AS forecast_accuracy_1d,
-                    max(acc) AS forecast_accuracy_3d
-                FROM waterway.gauge_predictions,
-                    GREATEST(water_level - lower(conf_interval),
-                        upper(conf_interval) - water_level) AS acc (acc)
-                WHERE measure_date
-                    BETWEEN current_timestamp
-                        AND current_timestamp + '3 days'::interval
-                GROUP BY location) AS fca
+        LEFT JOIN (SELECT DISTINCT ON (location)
+                    location,
+                    max(acc) FILTER (WHERE measure_date
+                            <= current_timestamp + '1 day'::interval)
+                        OVER loc_date_issue AS forecast_accuracy_1d,
+                    max(acc) OVER loc_date_issue AS forecast_accuracy_3d
+                FROM (SELECT location, date_issue, measure_date,
+                        GREATEST(water_level - lower(conf_interval),
+                            upper(conf_interval) - water_level) AS acc
+                    FROM waterway.gauge_predictions
+                    -- consider predictions made within last 14 days ...
+                    WHERE date_issue
+                        >= current_timestamp - '14 days 00:15'::interval
+                        -- ... for the next three days from now
+                        AND measure_date BETWEEN current_timestamp
+                            AND current_timestamp + '3 days'::interval) AS acc
+                WINDOW loc_date_issue AS (PARTITION BY location, date_issue)
+                ORDER BY location, date_issue DESC) AS fca
             USING (location)
     $$);
 
@@ -145,15 +148,15 @@
             s.nobjnam,
             s.date_info,
             s.source_organization,
-            (SELECT string_agg(country_code, ', ')
-                FROM waterway.stretch_countries
-                WHERE stretches_id = s.id) AS countries,
+            (SELECT string_agg(country, ', ')
+                FROM users.stretch_countries
+                WHERE stretch_id = s.id) AS countries,
             s.staging_done,
             min(g.gm_measuredate) AS gm_measuredate,
             min(g.gm_n_14d) AS gm_n_14d,
             max(g.forecast_accuracy_3d) AS forecast_accuracy_3d,
             max(g.forecast_accuracy_1d) AS forecast_accuracy_1d
-        FROM waterway.stretches s
+        FROM users.stretches s
             LEFT JOIN (
     $$ || (SELECT def FROM base_views WHERE name = 'gauges_base_view') || $$
             WHERE NOT erased) AS g