SELECT 
 CURR.BOOKING_RATE_CALENDAR_KEY,
 CURR.ARV_CALENDAR_KEY,
 CURR.SITE_KEY AS SITE_KEY,
 CURR.DAYS_TO_ARRIVAL_DATE,
 CURR.LENGTH_OF_STAY,
 CURR.NUMBER_OF_GUEST,
 CURR.DATA_SOURCE_CODE,
 CURR.COMPETITOR_PRODUCT_NAME,
 MIN(CURR.RATE_AMT_USD) AS MIN_RATE_AMT_USD,
 (
  SELECT
   MIN(PREV.RATE_AMT_USD)
  FROM
   OPERATIONS.COMPETITIVE_RATE_SHOPPING_FACT PREV
  WHERE
   PREV.ARV_CALENDAR_KEY = CURR.ARV_CALENDAR_KEY AND
   PREV.SITE_KEY = CURR.SITE_KEY AND
   PREV.LENGTH_OF_STAY = CURR.LENGTH_OF_STAY AND
   PREV.NUMBER_OF_GUEST = CURR.NUMBER_OF_GUEST AND
   PREV.DATA_SOURCE_CODE = CURR.DATA_SOURCE_CODE AND
   PREV.COMPETITOR_PRODUCT_NAME = CURR.COMPETITOR_PRODUCT_NAME AND
   PREV.BOOKING_RATE_CALENDAR_KEY = CURR.BOOKING_RATE_CALENDAR_KEY - 7 AND
   PREV.REPORTING_COMPETITOR_ID = PREV.RUBICON_PROPERTY_ID
 ) AS MIN_PREV_RATE_AMT_USD
FROM
 OPERATIONS.COMPETITIVE_RATE_SHOPPING_FACT CURR
WHERE
 CURR.REPORTING_COMPETITOR_ID = CURR.RUBICON_PROPERTY_ID AND
 CURR.BOOKING_RATE_CALENDAR_KEY > 0 AND
 CURR.DM_INSERTED_DATE >= (SELECT MAX(C.DM_INSERTED_DATE) - 1 FROM OPERATIONS.COMPETITIVE_RATE_SHOPPING_FACT C)
GROUP BY
 CURR.BOOKING_RATE_CALENDAR_KEY,
 CURR.BOOKING_RATE_DAY_TIME_KEY,
 CURR.ARV_CALENDAR_KEY,
 CURR.DAYS_TO_ARRIVAL_DATE,
 CURR.LENGTH_OF_STAY,
 CURR.NUMBER_OF_GUEST,
 CURR.DATA_SOURCE_CODE,
 CURR.RUBICON_PROPERTY_ID,
 CURR.COMPETITOR_PRODUCT_NAME,
 CURR.SITE_KEY;