create materialized view public.kpi_active_keywords_mv as
SELECT sub.kpi_id       AS id,
       sub.kpi_id,
       sub.spider_id,
       sub.spidername,
       max(
               CASE
                   WHEN sub.module = 'fp_kpi_keywords'::text THEN sub.keywords
                   ELSE ARRAY []::integer[]
                   END) AS search_first_page,
       max(
               CASE
                   WHEN sub.module = 'top_kpi_keywords'::text THEN sub.keywords
                   ELSE ARRAY []::integer[]
                   END) AS search_top,
       max(
               CASE
                   WHEN sub.module = 'products_fp_kpi_keywords'::text THEN sub.keywords
                   ELSE ARRAY []::integer[]
                   END) AS search_products_on_first_page,
       max(
               CASE
                   WHEN sub.module = 'products_top_kpi_keywords'::text THEN sub.keywords
                   ELSE ARRAY []::integer[]
                   END) AS search_products_on_top
FROM (SELECT kpi.kpi_id,
             s.main_spider_id        AS spider_id,
             s.spidername,
             'fp_kpi_keywords'::text AS module,
             array_agg(kw.id)        AS keywords
      FROM kpi_searchfirstpagekpi_keywords kkw
               JOIN search_analytics_rpckeywordpreference ks ON kkw.rpckeyword_id = ks.keyword_id
               JOIN search_analytics_rpckeyword kw ON kw.id = ks.keyword_id
               JOIN kpi_searchfirstpagekpi kpi ON kkw.searchfirstpagekpi_id = kpi.id
               JOIN (SELECT si.id AS main_spider_id,
                            ss.id AS search_spider_id,
                            si.spidername
                     FROM items_spider si
                              LEFT JOIN items_spider ss ON (si.spidername::text || '_search'::text) = ss.spidername::text AND ss.type = 3
                     WHERE si.type = 1) s ON ks.spider_id = s.search_spider_id
      WHERE kw.removed = false
        AND kw.is_visible = true
      GROUP BY kpi.kpi_id, s.main_spider_id, s.spidername
      UNION
      SELECT kpi.kpi_id,
             s.main_spider_id         AS spider_id,
             s.spidername,
             'top_kpi_keywords'::text AS module,
             array_agg(kw.id)         AS keywords
      FROM kpi_searchtopkpi_keywords kkw
               JOIN search_analytics_rpckeywordpreference ks ON kkw.rpckeyword_id = ks.keyword_id
               JOIN search_analytics_rpckeyword kw ON kw.id = ks.keyword_id
               JOIN kpi_searchtopkpi kpi ON kkw.searchtopkpi_id = kpi.id
               JOIN (SELECT si.id AS main_spider_id,
                            ss.id AS search_spider_id,
                            si.spidername
                     FROM items_spider si
                              LEFT JOIN items_spider ss ON (si.spidername::text || '_search'::text) = ss.spidername::text AND ss.type = 3
                     WHERE si.type = 1) s ON ks.spider_id = s.search_spider_id
      WHERE kw.removed = false
        AND kw.is_visible = true
      GROUP BY kpi.kpi_id, s.main_spider_id, s.spidername
      UNION
      SELECT kpi.kpi_id,
             s.main_spider_id                 AS spider_id,
             s.spidername,
             'products_fp_kpi_keywords'::text AS module,
             array_agg(kw.id)                 AS keywords
      FROM kpi_searchproductsonfirstpagekpi_keywords kkw
               JOIN search_analytics_rpckeywordpreference ks ON kkw.rpckeyword_id = ks.keyword_id
               JOIN search_analytics_rpckeyword kw ON kw.id = ks.keyword_id
               JOIN kpi_searchproductsonfirstpagekpi kpi ON kkw.searchproductsonfirstpagekpi_id = kpi.id
               JOIN (SELECT si.id AS main_spider_id,
                            ss.id AS search_spider_id,
                            si.spidername
                     FROM items_spider si
                              LEFT JOIN items_spider ss ON (si.spidername::text || '_search'::text) = ss.spidername::text AND ss.type = 3
                     WHERE si.type = 1) s ON ks.spider_id = s.search_spider_id
      WHERE kw.removed = false
        AND kw.is_visible = true
      GROUP BY kpi.kpi_id, s.main_spider_id, s.spidername
      UNION
      SELECT kpi.kpi_id,
             s.main_spider_id                  AS spider_id,
             s.spidername,
             'products_top_kpi_keywords'::text AS module,
             array_agg(kw.id)                  AS keywords
      FROM kpi_searchproductsontopkpi_keywords kkw
               JOIN search_analytics_rpckeywordpreference ks ON kkw.rpckeyword_id = ks.keyword_id
               JOIN search_analytics_rpckeyword kw ON kw.id = ks.keyword_id
               JOIN kpi_searchproductsontopkpi kpi ON kkw.searchproductsontopkpi_id = kpi.id
               JOIN (SELECT si.id AS main_spider_id,
                            ss.id AS search_spider_id,
                            si.spidername
                     FROM items_spider si
                              LEFT JOIN items_spider ss ON (si.spidername::text || '_search'::text) = ss.spidername::text AND ss.type = 3
                     WHERE si.type = 1) s ON ks.spider_id = s.search_spider_id
      WHERE kw.removed = false
        AND kw.is_visible = true
      GROUP BY kpi.kpi_id, s.main_spider_id, s.spidername) sub
GROUP BY sub.kpi_id, sub.spider_id, sub.spidername;