Price

select competitor,
        department,
        categoria,
        description,
        upc,
        cast(price as double precision)as price,
       --price,
       previousprice,
       seller,
       brand,
       quantityofphotos,
       quantityofphotospdp,
       mexico_time,
       universal_time,
       url,
       CASE WHEN ROW_NUMBER() OVER(PARTITION BY upc ORDER BY price asc)  =1 then 'Si' else '' END as lowestprice,
       case when COUNT(upc) OVER (PARTITION BY upc) = 1 THEN 'Si' else  '' end as isunique
--sabana
from (--sabana

 SELECT competitor,
        department,
        categoria,
        description,
        upc,
        price,
        --'' as previousprice,
        (SELECT price
         FROM mv_pricing_superama_nm
         WHERE ts_to_char(mexico_time) = '10-03-2019'
           AND upc = s.upc
         LIMIT 1)  AS previousprice,
        'Superama' AS seller,
        '' AS brand,
        '' AS quantityofphotos,
        '' AS quantityofphotospdp,
        mexico_time,
        universal_time,
        url
 FROM mv_pricing_superama_nm s
 WHERE ts_to_char(mexico_time) = '11-03-2019'
 GROUP BY upc, description, competitor, department, categoria, price, mexico_time, universal_time, url
 UNION ALL
 SELECT competitor,
        department,
        categoria,
        description,
        upc,
        price,
        --price as previousprice,
        (SELECT price
         FROM mv_pricing_wm_ghs
         WHERE ts_to_char(mexico_time) = '10-03-2019'
           AND upc = s.upc
         LIMIT 1) AS previousprice,
        'Walmart' AS seller,
        '' AS brand,
        '' AS quantityofphotos,
        '' AS quantityofphotospdp,
        mexico_time,
        universal_time,
        url
 FROM mv_pricing_wm_ghs s
 WHERE ts_to_char(mexico_time) = '11-03-2019'
 GROUP BY upc, description, competitor, department, categoria, price, mexico_time, universal_time, url
 UNION ALL
 SELECT competitor,
        department,
        categoria,
        description,
        upc,
        price,
        --'' as previousprice,
        (SELECT price
         FROM mv_pricing_wm_mg
         WHERE ts_to_char(mexico_time) = '10-03-2019'
           AND upc = s.upc AND seller = s.seller
         LIMIT 1) AS previousprice,
        seller,
        '' AS brand,
        '' AS quantityofphotos,
        '' AS quantityofphotospdp,
        mexico_time,
        universal_time,
        url
 FROM mv_pricing_wm_mg s
 WHERE ts_to_char(mexico_time) = '11-03-2019'
 GROUP BY upc, description, competitor, department, categoria, seller, price, mexico_time, universal_time, url
  UNION ALL
 SELECT competitor,
        department,
        categoria,
        description,
        upc,
        price,
        --'' as previousprice,
        (SELECT price
         FROM mv_pricing_sams
         WHERE ts_to_char(mexico_time) = '10-03-2019'
           AND upc = s.upc
         LIMIT 1) AS previousprice,
        competitor AS seller,
        '' AS brand,
        '' AS quantityofphotos,
        '' AS quantityofphotospdp,
        mexico_time,
        universal_time,
        url
 FROM mv_pricing_sams s
 WHERE ts_to_char(mexico_time) = '11-03-2019'
 GROUP BY upc, description, competitor, department, categoria, seller, price, mexico_time, universal_time, url
 UNION ALL
 SELECT competitor,
        department,
        categoria,
        description,
        upc,
        minimum_list_price,
        --'' as previousprice,
        (SELECT minimum_list_price
         FROM mv_pricing_liverpool
         WHERE ts_to_char(mexico_time) = '10-03-2019'
           AND upc = s.upc
         LIMIT 1)   AS previousprice,
        'Liverpool' AS seller,
        '' AS brand,
        '' AS quantityofphotos,
        '' AS quantityofphotospdp,
        mexico_time,
        universal_time,
        url
 FROM mv_pricing_liverpool s
 WHERE ts_to_char(mexico_time) = '11-03-2019'
 GROUP BY upc, description, competitor, department, categoria, minimum_list_price, mexico_time, universal_time, url
 UNION ALL
 SELECT competitor,
        department,
        categoria,
        description,
        upc,
        CASE
        WHEN (offer_price IS NULL) THEN price
          ELSE offer_price END AS price,
        --'' as previousprice,
        (SELECT
          CASE
        WHEN (offer_price IS NULL) THEN price
          ELSE offer_price END AS price
         FROM mv_pricing_amazon
         WHERE ts_to_char(mexico_time) = '10-03-2019'
           AND upc = s.upc
         LIMIT 1)   AS previousprice,
        'Liverpool' AS seller,
        '' AS brand,
        '' AS quantityofphotos,
        '' AS quantityofphotospdp,
        mexico_time,
        universal_time,
        url
 FROM mv_pricing_amazon s
 WHERE ts_to_char(mexico_time) = '11-03-2019' AND price IS NOT NULL AND offer_price IS NOT NULL
 GROUP BY upc, description, competitor, department, categoria, price, offer_price, mexico_time, universal_time, url
 UNION ALL
 SELECT competitor,
        department,
        categoria,
        description,
        upc,
       replace( replace(replace (replace(price,' M.N. kilo',''),' M.N. pieza',''), '$','') ,',','') as price,
        (SELECT replace( replace(replace (replace( trim(price),' M.N. kilo',''),' M.N. pieza',''), '$',''),',','')
         FROM mv_pricing_citymarket
         WHERE ts_to_char(mexico_time) = '10-03-2019'
           AND upc = s.upc
         LIMIT 1)   AS previousprice,
        'City Market' AS seller,
        '' AS brand,
        '' AS quantityofphotos,
        '' AS quantityofphotospdp,
        mexico_time,
        universal_time,
        url
 FROM mv_pricing_citymarket s
 WHERE ts_to_char(mexico_time) = '11-03-2019'
 GROUP BY upc, description, competitor, department, categoria, price, mexico_time, universal_time, url

 UNION ALL
 SELECT competitor,
        department,
        categoria,
        description,
        upc,
        price,
        --'' as previousprice,
        (SELECT minimum_list_price
         FROM mv_pricing_liverpool
         WHERE ts_to_char(mexico_time) = '10-03-2019'
           AND upc = s.upc
         LIMIT 1)   AS previousprice,
        'Farmacias San Pablo' AS seller,
        laboratory AS brand,
        '' AS quantityofphotos,
        '' AS quantityofphotospdp,
        mexico_time,
        universal_time,
        url
 FROM mv_pricing_sanpablo s
 WHERE ts_to_char(mexico_time) = '11-03-2019' AND price IS NOT NULL
 GROUP BY upc, description, competitor, department, categoria, price, mexico_time, universal_time, url, laboratory
  UNION ALL
 SELECT competitor,
        department,
        categoria,
        description,
        upc,
        price,
        --'' as previousprice,
        (SELECT minimum_list_price
         FROM mv_pricing_liverpool
         WHERE ts_to_char(mexico_time) = '10-03-2019'
           AND upc = s.upc
         LIMIT 1)   AS previousprice,
        'Farmacias del Ahorro' AS seller,
        '' AS brand,
        '' AS quantityofphotos,
        '' AS quantityofphotospdp,
        mexico_time,
        universal_time,
        url
 FROM mv_pricing_fahorro s
 WHERE ts_to_char(mexico_time) = '11-03-2019' AND price IS NOT NULL
 GROUP BY upc, description, competitor, department, categoria, price, mexico_time, universal_time, url
 UNION ALL
   SELECT competitor,
        department,
        categoria,
        description,
        upc,
        CASE
          WHEN price IS NULL THEN offer_price
          WHEN offer_price IS NULL THEN promo_price
          ELSE price END AS price,
        --'' as previousprice,
        (SELECT CASE
          WHEN price IS NULL THEN offer_price
          WHEN offer_price IS NULL THEN promo_price
          ELSE price END AS previousprice
         FROM mv_pricing_soriana
         WHERE ts_to_char(mexico_time) = '10-03-2019'
           AND upc = s.upc
         LIMIT 1) AS previousprice,
        competitor AS seller,
        '' AS brand,
        '' AS quantityofphotos,
        '' AS quantityofphotospdp,
        mexico_time,
        universal_time,
        url
 FROM mv_pricing_soriana s
 WHERE ts_to_char(mexico_time) = '11-03-2019'
 GROUP BY upc, description, competitor, department, categoria, price,offer_price,promo_price, mexico_time, universal_time, url
 UNION ALL
SELECT competitor,
        department,
        categoria,
        description,
        upc,
        price,
        --'' as previousprice,
        (SELECT price
         FROM mv_pricing_bestbuy
         WHERE ts_to_char(mexico_time) = '10-03-2019'
           AND upc = s.upc
         LIMIT 1)   AS previousprice,
        competitor AS seller,
        '' AS brand,
        '' AS quantityofphotos,
        '' AS quantityofphotospdp,
        mexico_time,
        universal_time,
        url
 FROM mv_pricing_bestbuy s
 WHERE ts_to_char(mexico_time) = '11-03-2019'
 GROUP BY upc, description, competitor, department, categoria, price, mexico_time, universal_time, url
 UNION ALL
SELECT competitor,
        department,
        categoria,
        description,
        upc,
        price,
        --'' as previousprice,
        (SELECT price
         FROM mv_pricing_linio
         WHERE ts_to_char(mexico_time) = '10-03-2019'
           AND upc = s.upc
         LIMIT 1)   AS previousprice,
        competitor AS seller,
        '' AS brand,
        '' AS quantityofphotos,
        '' AS quantityofphotospdp,
        mexico_time,
        universal_time,
        url
 FROM mv_pricing_linio s
 WHERE ts_to_char(mexico_time) = '11-03-2019' AND price IS NOT NULL
 GROUP BY upc, description, competitor, department, categoria, price, mexico_time, universal_time, url
 UNION ALL
SELECT competitor,
        department,
        categoria,
        description,
        upc,
        price,
        --'' as previousprice,
        (SELECT price
         FROM mv_pricing_laeuropea
         WHERE ts_to_char(mexico_time) = '10-03-2019'
           AND upc = s.upc
         LIMIT 1)   AS previousprice,
        competitor AS seller,
        brand AS brand,
        '' AS quantityofphotos,
        '' AS quantityofphotospdp,
        mexico_time,
        universal_time,
        url
 FROM mv_pricing_laeuropea s
 WHERE ts_to_char(mexico_time) = '11-03-2019' AND price IS NOT NULL
 GROUP BY upc, description, competitor, department, categoria, price, mexico_time, universal_time, url, brand

 ) AS allprices