SELECT DISTINCT ON (property_id)
  arrival_date,
  property_id,
  abs('2018-01-02'::date - arrival_date) AS days_diff
FROM property_arrival_rules
INNER JOIN properties ON properties.id = property_arrival_rules.property_id
WHERE '2017-01-02'::date - 7 <= arrival_date AND arrival_date <= '2018-01-02'::date + 7
  AND (
    (properties.dest_type = 'Place' AND properties.dest_id IN (9)) 
    OR (properties.dest_type = 'District' AND properties.dest_id IN (16, 17))
  )
ORDER BY property_id, days_diff

WITH fit_arrival_rules AS (
  SELECT DISTINCT ON (property_id)
    arrival_date,
    property_id,
    abs('2018-01-02'::date - arrival_date) AS days_diff
  FROM property_arrival_rules
  INNER JOIN properties ON properties.id = property_arrival_rules.property_id
  WHERE '2017-01-02'::date - 7 <= arrival_date AND arrival_date <= '2018-01-02'::date + 7
    AND (
      (properties.dest_type = 'Place' AND properties.dest_id IN (9)) 
      OR (properties.dest_type = 'District' AND properties.dest_id IN (16, 17))
    )
  ORDER BY property_id, days_diff
)

SELECT 
  property_arrival_periods.arrival_date, 
  property_arrival_periods.departure_date, 
  property_arrival_periods.property_id
FROM property_arrival_periods
INNER JOIN fit_arrival_rules
  ON property_arrival_periods.property_id = fit_arrival_rules.property_id 
    AND property_arrival_periods.arrival_date = fit_arrival_rules.arrival_date
WHERE wanted_departure_range @> (property_arrival_periods.arrival_date + 6)

WITH fit_arrival_rules AS (
  SELECT DISTINCT ON (property_id)
    arrival_date,
    property_id,
    abs('2018-01-02'::date - arrival_date) AS days_diff
  FROM property_arrival_rules
  INNER JOIN properties ON properties.id = property_arrival_rules.property_id
  WHERE '2017-01-02'::date - 7 <= arrival_date AND arrival_date <= '2018-01-02'::date + 7
    AND (
      (properties.dest_type = 'Place' AND properties.dest_id IN (9)) 
      OR (properties.dest_type = 'District' AND properties.dest_id IN (16, 17))
    )
  ORDER BY property_id, days_diff
),
fit_arrival_dates AS (
  SELECT 
    property_arrival_periods.arrival_date, 
    property_arrival_periods.departure_date, 
    property_arrival_periods.property_id
  FROM property_arrival_periods
  INNER JOIN fit_arrival_rules
    ON property_arrival_periods.property_id = fit_arrival_rules.property_id 
      AND property_arrival_periods.arrival_date = fit_arrival_rules.arrival_date
  WHERE wanted_departure_range @> (property_arrival_periods.arrival_date + 6)
)

SELECT room_availabilities.room_id
FROM room_availabilities
INNER JOIN rooms ON rooms.id = room_availabilities.room_id
INNER JOIN fit_arrival_dates ON fit_arrival_dates.property_id = rooms.property_id
WHERE fit_arrival_dates.arrival_date <= date AND date < fit_arrival_dates.departure_date
  AND initial_count - sales_count > 0
GROUP BY fit_arrival_dates.arrival_date, fit_arrival_dates.departure_date, room_id
HAVING COUNT(room_availabilities.id) = (fit_arrival_dates.departure_date - fit_arrival_dates.arrival_date)

CREATE OR REPLACE FUNCTION is_room_fit_guests(guests INTEGER[], capacity INTEGER[])
RETURNS BOOLEAN
AS
$$ 
DECLARE
  guest int;
  seat int;
  seat_index int;
  max_array_index CONSTANT int := 2147483647;
BEGIN
  guest = guests[1];

  IF guest IS NULL
  THEN 
    RETURN TRUE;
  END IF;

  seat_index := 1;
  FOREACH seat IN ARRAY capacity
  LOOP
    IF guest <= seat
    THEN
      RETURN is_room_fit_guests(guests[2:max_array_index], capacity[1:seat_index-1] || capacity[seat_index+1:max_array_index]);
    END IF;
    seat_index := seat_index + 1;
  END LOOP;

  RETURN FALSE;
END;
$$ 
LANGUAGE plpgsql;

WITH fit_arrival_rules AS (
  SELECT DISTINCT ON (property_id)
    arrival_date,
    property_id,
    abs('2018-01-02'::date - arrival_date) AS days_diff
  FROM property_arrival_rules
  INNER JOIN properties ON properties.id = property_arrival_rules.property_id
  WHERE '2017-01-02'::date - 7 <= arrival_date AND arrival_date <= '2018-01-02'::date + 7
    AND (
      (properties.dest_type = 'Place' AND properties.dest_id IN (9)) 
      OR (properties.dest_type = 'District' AND properties.dest_id IN (16, 17))
    )
  ORDER BY property_id, days_diff
),
fit_arrival_dates AS (
  SELECT 
    property_arrival_periods.arrival_date, 
    property_arrival_periods.departure_date, 
    property_arrival_periods.property_id
  FROM property_arrival_periods
  INNER JOIN fit_arrival_rules
    ON property_arrival_periods.property_id = fit_arrival_rules.property_id 
      AND property_arrival_periods.arrival_date = fit_arrival_rules.arrival_date
  WHERE wanted_departure_range @> (property_arrival_periods.arrival_date + 6)
)

SELECT
  rooms.property_id,
  fit_arrival_dates.arrival_date,
  fit_arrival_dates.departure_date,
  room_price_policy_id,
  room_price_policies.meal_type,
  (
    CASE
      WHEN room_properties.currency = 'EUR' THEN SUM(room_prices.price)
      ELSE (SUM(room_prices.price) / COALESCE(currency_rates.price, 1))::DECIMAL(10,2)
    END
  ) AS total,
  (
    CASE
      WHEN room_properties.currency = 'EUR' THEN AVG(room_prices.price)::DECIMAL(10,2)
      ELSE (AVG(room_prices.price) / COALESCE(currency_rates.price, 1))::DECIMAL(10,2)
    END
  ) AS average_night_price,
  rooms.id AS room_id,
  is_room_fit_guests(ARRAY[7,9,18,18,18], room_price_policies.capacity) AS fit_people
FROM room_prices
INNER JOIN room_price_policies ON room_prices.room_price_policy_id = room_price_policies.id
INNER JOIN rooms ON room_price_policies.room_id = rooms.id
INNER JOIN properties room_properties ON room_properties.id = rooms.property_id
LEFT JOIN currency_rates 
  ON currency_rates.sale_currency = room_properties.currency 
  AND currency_rates.buy_currency = 'EUR'
INNER JOIN (
  SELECT room_availabilities.room_id
  FROM room_availabilities
  INNER JOIN rooms ON rooms.id = room_availabilities.room_id
  INNER JOIN fit_arrival_dates ON fit_arrival_dates.property_id = rooms.property_id
  WHERE fit_arrival_dates.arrival_date <= date AND date < fit_arrival_dates.departure_date
    AND initial_count - sales_count > 0
  GROUP BY fit_arrival_dates.arrival_date, fit_arrival_dates.departure_date, room_id
  HAVING COUNT(room_availabilities.id) = (fit_arrival_dates.departure_date - fit_arrival_dates.arrival_date)
) ra ON ra.room_id = rooms.id
INNER JOIN fit_arrival_dates ON fit_arrival_dates.property_id = rooms.property_id
WHERE fit_arrival_dates.arrival_date <= price_date AND price_date < fit_arrival_dates.departure_date
  AND (room_price_policies.has_special_requirements = FALSE
    OR (room_price_policies.has_special_requirements = TRUE AND room_price_policies.before_type = 0
      AND room_price_policies.before_date IS NOT NULL AND '2017-08-17'::date < room_price_policies.before_date)
    OR (room_price_policies.has_special_requirements = TRUE AND room_price_policies.before_type = 1
      AND room_price_policies.days_before_arrival IS NOT NULL 
      AND '2017-08-17'::date < fit_arrival_dates.arrival_date - room_price_policies.days_before_arrival)
  )
  AND room_price_policies.capacity IS NOT NULL
GROUP BY
  rooms.property_id,
  fit_arrival_dates.arrival_date,
  fit_arrival_dates.departure_date,
  room_price_policy_id,
  room_price_policies.meal_type,
  rooms.id,
  room_properties.currency,
  currency_rates.price,
  room_price_policies.capacity
HAVING COUNT(room_prices.id) = (fit_arrival_dates.departure_date - fit_arrival_dates.arrival_date)

WITH fit_arrival_rules AS (
  SELECT DISTINCT ON (property_id)
    arrival_date,
    property_id,
    abs('2018-01-02'::date - arrival_date) AS days_diff
  FROM property_arrival_rules
  INNER JOIN properties ON properties.id = property_arrival_rules.property_id
  WHERE '2017-01-02'::date - 7 <= arrival_date AND arrival_date <= '2018-01-02'::date + 7
    AND (
      (properties.dest_type = 'Place' AND properties.dest_id IN (9)) 
      OR (properties.dest_type = 'District' AND properties.dest_id IN (16, 17))
    )
  ORDER BY property_id, days_diff
),
fit_arrival_dates AS (
  SELECT 
    property_arrival_periods.arrival_date, 
    property_arrival_periods.departure_date, 
    property_arrival_periods.property_id
  FROM property_arrival_periods
  INNER JOIN fit_arrival_rules
    ON property_arrival_periods.property_id = fit_arrival_rules.property_id 
      AND property_arrival_periods.arrival_date = fit_arrival_rules.arrival_date
  WHERE wanted_departure_range @> (property_arrival_periods.arrival_date + 6)
),
properties_with_rooms AS (
  SELECT
    rooms.property_id,
    fit_arrival_dates.arrival_date,
    fit_arrival_dates.departure_date,
    room_price_policy_id,
    room_price_policies.meal_type,
    (
      CASE
        WHEN room_properties.currency = 'EUR' THEN SUM(room_prices.price)
        ELSE (SUM(room_prices.price) / COALESCE(currency_rates.price, 1))::DECIMAL(10,2)
      END
    ) AS total,
    (
      CASE
        WHEN room_properties.currency = 'EUR' THEN AVG(room_prices.price)::DECIMAL(10,2)
        ELSE (AVG(room_prices.price) / COALESCE(currency_rates.price, 1))::DECIMAL(10,2)
      END
    ) AS average_night_price,
    rooms.id AS room_id,
    is_room_fit_guests(ARRAY[7,9,18,18,18], room_price_policies.capacity) AS fit_people
  FROM room_prices
  INNER JOIN room_price_policies ON room_prices.room_price_policy_id = room_price_policies.id
  INNER JOIN rooms ON room_price_policies.room_id = rooms.id
  INNER JOIN properties room_properties ON room_properties.id = rooms.property_id
  LEFT JOIN currency_rates 
    ON currency_rates.sale_currency = room_properties.currency 
    AND currency_rates.buy_currency = 'EUR'
  INNER JOIN (
    SELECT room_availabilities.room_id
    FROM room_availabilities
    INNER JOIN rooms ON rooms.id = room_availabilities.room_id
    INNER JOIN fit_arrival_dates ON fit_arrival_dates.property_id = rooms.property_id
    WHERE fit_arrival_dates.arrival_date <= date AND date < fit_arrival_dates.departure_date
      AND initial_count - sales_count > 0
    GROUP BY fit_arrival_dates.arrival_date, fit_arrival_dates.departure_date, room_id
    HAVING COUNT(room_availabilities.id) = (fit_arrival_dates.departure_date - fit_arrival_dates.arrival_date)
  ) ra ON ra.room_id = rooms.id
  INNER JOIN fit_arrival_dates ON fit_arrival_dates.property_id = rooms.property_id
  WHERE fit_arrival_dates.arrival_date <= price_date AND price_date < fit_arrival_dates.departure_date
    AND (room_price_policies.has_special_requirements = FALSE
      OR (room_price_policies.has_special_requirements = TRUE AND room_price_policies.before_type = 0
        AND room_price_policies.before_date IS NOT NULL AND '2017-08-17'::date < room_price_policies.before_date)
      OR (room_price_policies.has_special_requirements = TRUE AND room_price_policies.before_type = 1
        AND room_price_policies.days_before_arrival IS NOT NULL 
        AND '2017-08-17'::date < fit_arrival_dates.arrival_date - room_price_policies.days_before_arrival)
    )
    AND room_price_policies.capacity IS NOT NULL
  GROUP BY
    rooms.property_id,
    fit_arrival_dates.arrival_date,
    fit_arrival_dates.departure_date,
    room_price_policy_id,
    room_price_policies.meal_type,
    rooms.id,
    room_properties.currency,
    currency_rates.price,
    room_price_policies.capacity
  HAVING COUNT(room_prices.id) = (fit_arrival_dates.departure_date - fit_arrival_dates.arrival_date)
)

SELECT DISTINCT ON(property_id) *, 
  1 as all_guests_placed
FROM properties_with_rooms
WHERE fit_people = TRUE
ORDER BY property_id, total

WITH fit_arrival_rules AS (
  SELECT DISTINCT ON (property_id)
    arrival_date,
    property_id,
    abs('2018-01-02'::date - arrival_date) AS days_diff
  FROM property_arrival_rules
  INNER JOIN properties ON properties.id = property_arrival_rules.property_id
  WHERE '2017-01-02'::date - 7 <= arrival_date AND arrival_date <= '2018-01-02'::date + 7
    AND (
      (properties.dest_type = 'Place' AND properties.dest_id IN (9)) 
      OR (properties.dest_type = 'District' AND properties.dest_id IN (16, 17))
    )
  ORDER BY property_id, days_diff
),
fit_arrival_dates AS (
  SELECT 
    property_arrival_periods.arrival_date, 
    property_arrival_periods.departure_date, 
    property_arrival_periods.property_id
  FROM property_arrival_periods
  INNER JOIN fit_arrival_rules
    ON property_arrival_periods.property_id = fit_arrival_rules.property_id 
      AND property_arrival_periods.arrival_date = fit_arrival_rules.arrival_date
  WHERE wanted_departure_range @> (property_arrival_periods.arrival_date + 6)
),
properties_with_rooms AS (
  SELECT
    rooms.property_id,
    fit_arrival_dates.arrival_date,
    fit_arrival_dates.departure_date,
    room_price_policy_id,
    room_price_policies.meal_type,
    (
      CASE
        WHEN room_properties.currency = 'EUR' THEN SUM(room_prices.price)
        ELSE (SUM(room_prices.price) / COALESCE(currency_rates.price, 1))::DECIMAL(10,2)
      END
    ) AS total,
    (
      CASE
        WHEN room_properties.currency = 'EUR' THEN AVG(room_prices.price)::DECIMAL(10,2)
        ELSE (AVG(room_prices.price) / COALESCE(currency_rates.price, 1))::DECIMAL(10,2)
      END
    ) AS average_night_price,
    rooms.id AS room_id,
    is_room_fit_guests(ARRAY[7,9,18,18,18], room_price_policies.capacity) AS fit_people
  FROM room_prices
  INNER JOIN room_price_policies ON room_prices.room_price_policy_id = room_price_policies.id
  INNER JOIN rooms ON room_price_policies.room_id = rooms.id
  INNER JOIN properties room_properties ON room_properties.id = rooms.property_id
  LEFT JOIN currency_rates 
    ON currency_rates.sale_currency = room_properties.currency 
    AND currency_rates.buy_currency = 'EUR'
  INNER JOIN (
    SELECT room_availabilities.room_id
    FROM room_availabilities
    INNER JOIN rooms ON rooms.id = room_availabilities.room_id
    INNER JOIN fit_arrival_dates ON fit_arrival_dates.property_id = rooms.property_id
    WHERE fit_arrival_dates.arrival_date <= date AND date < fit_arrival_dates.departure_date
      AND initial_count - sales_count > 0
    GROUP BY fit_arrival_dates.arrival_date, fit_arrival_dates.departure_date, room_id
    HAVING COUNT(room_availabilities.id) = (fit_arrival_dates.departure_date - fit_arrival_dates.arrival_date)
  ) ra ON ra.room_id = rooms.id
  INNER JOIN fit_arrival_dates ON fit_arrival_dates.property_id = rooms.property_id
  WHERE fit_arrival_dates.arrival_date <= price_date AND price_date < fit_arrival_dates.departure_date
    AND (room_price_policies.has_special_requirements = FALSE
      OR (room_price_policies.has_special_requirements = TRUE AND room_price_policies.before_type = 0
        AND room_price_policies.before_date IS NOT NULL AND '2017-08-17'::date < room_price_policies.before_date)
      OR (room_price_policies.has_special_requirements = TRUE AND room_price_policies.before_type = 1
        AND room_price_policies.days_before_arrival IS NOT NULL 
        AND '2017-08-17'::date < fit_arrival_dates.arrival_date - room_price_policies.days_before_arrival)
    )
    AND room_price_policies.capacity IS NOT NULL
  GROUP BY
    rooms.property_id,
    fit_arrival_dates.arrival_date,
    fit_arrival_dates.departure_date,
    room_price_policy_id,
    room_price_policies.meal_type,
    rooms.id,
    room_properties.currency,
    currency_rates.price,
    room_price_policies.capacity
  HAVING COUNT(room_prices.id) = (fit_arrival_dates.departure_date - fit_arrival_dates.arrival_date)
),
properties_with_recommended_room AS (
  SELECT DISTINCT ON(property_id) *, 
    1 as all_guests_placed
  FROM properties_with_rooms
  WHERE fit_people = TRUE
  ORDER BY property_id, total
)

SELECT DISTINCT ON(property_id) *, 
  0 as all_guests_placed   
FROM properties_with_rooms
WHERE property_id NOT IN (SELECT property_id FROM properties_with_recommended_room)
ORDER BY property_id, total

WITH fit_arrival_rules AS (
  SELECT DISTINCT ON (property_id)
    arrival_date,
    property_id,
    abs('2018-01-02'::date - arrival_date) AS days_diff
  FROM property_arrival_rules
  INNER JOIN properties ON properties.id = property_arrival_rules.property_id
  WHERE '2017-01-02'::date - 7 <= arrival_date AND arrival_date <= '2018-01-02'::date + 7
    AND (
      (properties.dest_type = 'Place' AND properties.dest_id IN (9)) 
      OR (properties.dest_type = 'District' AND properties.dest_id IN (16, 17))
    )
  ORDER BY property_id, days_diff
),
fit_arrival_dates AS (
  SELECT 
    property_arrival_periods.arrival_date, 
    property_arrival_periods.departure_date, 
    property_arrival_periods.property_id
  FROM property_arrival_periods
  INNER JOIN fit_arrival_rules
    ON property_arrival_periods.property_id = fit_arrival_rules.property_id 
      AND property_arrival_periods.arrival_date = fit_arrival_rules.arrival_date
  WHERE wanted_departure_range @> (property_arrival_periods.arrival_date + 6)
),
properties_with_rooms AS (
  SELECT
    rooms.property_id,
    fit_arrival_dates.arrival_date,
    fit_arrival_dates.departure_date,
    room_price_policy_id,
    room_price_policies.meal_type,
    (
      CASE
        WHEN room_properties.currency = 'EUR' THEN SUM(room_prices.price)
        ELSE (SUM(room_prices.price) / COALESCE(currency_rates.price, 1))::DECIMAL(10,2)
      END
    ) AS total,
    (
      CASE
        WHEN room_properties.currency = 'EUR' THEN AVG(room_prices.price)::DECIMAL(10,2)
        ELSE (AVG(room_prices.price) / COALESCE(currency_rates.price, 1))::DECIMAL(10,2)
      END
    ) AS average_night_price,
    rooms.id AS room_id,
    is_room_fit_guests(ARRAY[7,9,18,18,18], room_price_policies.capacity) AS fit_people
  FROM room_prices
  INNER JOIN room_price_policies ON room_prices.room_price_policy_id = room_price_policies.id
  INNER JOIN rooms ON room_price_policies.room_id = rooms.id
  INNER JOIN properties room_properties ON room_properties.id = rooms.property_id
  LEFT JOIN currency_rates 
    ON currency_rates.sale_currency = room_properties.currency 
    AND currency_rates.buy_currency = 'EUR'
  INNER JOIN (
    SELECT room_availabilities.room_id
    FROM room_availabilities
    INNER JOIN rooms ON rooms.id = room_availabilities.room_id
    INNER JOIN fit_arrival_dates ON fit_arrival_dates.property_id = rooms.property_id
    WHERE fit_arrival_dates.arrival_date <= date AND date < fit_arrival_dates.departure_date
      AND initial_count - sales_count > 0
    GROUP BY fit_arrival_dates.arrival_date, fit_arrival_dates.departure_date, room_id
    HAVING COUNT(room_availabilities.id) = (fit_arrival_dates.departure_date - fit_arrival_dates.arrival_date)
  ) ra ON ra.room_id = rooms.id
  INNER JOIN fit_arrival_dates ON fit_arrival_dates.property_id = rooms.property_id
  WHERE fit_arrival_dates.arrival_date <= price_date AND price_date < fit_arrival_dates.departure_date
    AND (room_price_policies.has_special_requirements = FALSE
      OR (room_price_policies.has_special_requirements = TRUE AND room_price_policies.before_type = 0
        AND room_price_policies.before_date IS NOT NULL AND '2017-08-17'::date < room_price_policies.before_date)
      OR (room_price_policies.has_special_requirements = TRUE AND room_price_policies.before_type = 1
        AND room_price_policies.days_before_arrival IS NOT NULL 
        AND '2017-08-17'::date < fit_arrival_dates.arrival_date - room_price_policies.days_before_arrival)
    )
    AND room_price_policies.capacity IS NOT NULL
  GROUP BY
    rooms.property_id,
    fit_arrival_dates.arrival_date,
    fit_arrival_dates.departure_date,
    room_price_policy_id,
    room_price_policies.meal_type,
    rooms.id,
    room_properties.currency,
    currency_rates.price,
    room_price_policies.capacity
  HAVING COUNT(room_prices.id) = (fit_arrival_dates.departure_date - fit_arrival_dates.arrival_date)
),
properties_with_recommended_room AS (
  SELECT DISTINCT ON(property_id) *, 
    1 as all_guests_placed
  FROM properties_with_rooms
  WHERE fit_people = TRUE
  ORDER BY property_id, total
),
properties_without_recommended_room AS (
  SELECT DISTINCT ON(property_id) *, 
    0 as all_guests_placed   
  FROM properties_with_rooms
  WHERE property_id NOT IN (SELECT property_id FROM properties_with_recommended_room)
  ORDER BY property_id, total
),
properties_with_cheapest_room AS (
  SELECT * FROM properties_with_recommended_room
  UNION ALL
  SELECT * FROM properties_without_recommended_room
)

SELECT properties.*,
  (
    CASE 
      WHEN room_id IS NOT NULL THEN 1
      ELSE 0
    END
  ) AS room_available,
  properties_with_cheapest_room.arrival_date,
  properties_with_cheapest_room.departure_date,
  properties_with_cheapest_room.room_id,
  properties_with_cheapest_room.room_price_policy_id,
  properties_with_cheapest_room.total,
  properties_with_cheapest_room.average_night_price,
  properties_with_cheapest_room.all_guests_placed
FROM properties
LEFT JOIN properties_with_cheapest_room ON properties_with_cheapest_room.property_id = properties.id
WHERE
    (
      (properties.dest_type = 'Place' AND properties.dest_id IN (9)) 
      OR (properties.dest_type = 'District' AND properties.dest_id IN (16, 17))
    )
ORDER BY all_guests_placed DESC NULLS LAST, room_available DESC, total ASC
LIMIT 20 OFFSET 0

WITH fit_arrival_rules AS (
  SELECT DISTINCT ON (property_id)
    arrival_date,
    property_id,
    abs('2018-01-02'::date - arrival_date) AS days_diff
  FROM property_arrival_rules
  INNER JOIN properties ON properties.id = property_arrival_rules.property_id
  WHERE '2017-01-02'::date - 7 <= arrival_date AND arrival_date <= '2018-01-02'::date + 7
    AND property_id IN (1, 4)
  ORDER BY property_id, days_diff
),
fit_arrival_dates AS (
  SELECT 
    property_arrival_periods.arrival_date, 
    property_arrival_periods.departure_date, 
    property_arrival_periods.property_id
  FROM property_arrival_periods
  INNER JOIN fit_arrival_rules
    ON property_arrival_periods.property_id = fit_arrival_rules.property_id 
      AND property_arrival_periods.arrival_date = fit_arrival_rules.arrival_date
  WHERE wanted_departure_range @> (property_arrival_periods.arrival_date + 6)
),
properties_with_available_rooms AS (
  SELECT DISTINCT ON (rooms.id)
    rooms.property_id,
    fit_arrival_dates.arrival_date,
    fit_arrival_dates.departure_date,
    room_price_policy_id,
    room_price_policies.meal_type,
    (
      CASE
        WHEN room_properties.currency = 'EUR' THEN SUM(room_prices.price)
        ELSE (SUM(room_prices.price) / COALESCE(currency_rates.price, 1))::DECIMAL(10,2)
      END
    ) AS total,
    (
      CASE
        WHEN room_properties.currency = 'EUR' THEN AVG(room_prices.price)::DECIMAL(10,2)
        ELSE (AVG(room_prices.price) / COALESCE(currency_rates.price, 1))::DECIMAL(10,2)
      END
    ) AS average_night_price,
    rooms.id AS room_id
  FROM room_prices
  INNER JOIN room_price_policies ON room_prices.room_price_policy_id = room_price_policies.id
  INNER JOIN rooms ON room_price_policies.room_id = rooms.id
  INNER JOIN properties room_properties ON room_properties.id = rooms.property_id
  LEFT JOIN currency_rates 
    ON currency_rates.sale_currency = room_properties.currency 
    AND currency_rates.buy_currency = 'EUR'
  INNER JOIN (
    SELECT room_availabilities.room_id
    FROM room_availabilities
    INNER JOIN rooms ON rooms.id = room_availabilities.room_id
    INNER JOIN fit_arrival_dates ON fit_arrival_dates.property_id = rooms.property_id
    WHERE fit_arrival_dates.arrival_date <= date AND date < fit_arrival_dates.departure_date
      AND initial_count - sales_count > 0
    GROUP BY fit_arrival_dates.arrival_date, fit_arrival_dates.departure_date, room_id
    HAVING COUNT(room_availabilities.id) = (fit_arrival_dates.departure_date - fit_arrival_dates.arrival_date)
  ) ra ON ra.room_id = rooms.id
  INNER JOIN fit_arrival_dates ON fit_arrival_dates.property_id = rooms.property_id
  WHERE fit_arrival_dates.arrival_date <= price_date AND price_date < fit_arrival_dates.departure_date
    AND (room_price_policies.has_special_requirements = FALSE
      OR (room_price_policies.has_special_requirements = TRUE AND room_price_policies.before_type = 0
        AND room_price_policies.before_date IS NOT NULL AND '2017-08-17'::date < room_price_policies.before_date)
      OR (room_price_policies.has_special_requirements = TRUE AND room_price_policies.before_type = 1
        AND room_price_policies.days_before_arrival IS NOT NULL 
        AND '2017-08-17'::date < fit_arrival_dates.arrival_date - room_price_policies.days_before_arrival)
    )
  GROUP BY
    rooms.property_id,
    fit_arrival_dates.arrival_date,
    fit_arrival_dates.departure_date,
    room_price_policy_id,
    room_price_policies.meal_type,
    rooms.id,
    room_properties.currency,
    currency_rates.price
  HAVING COUNT(room_prices.id) = (fit_arrival_dates.departure_date - fit_arrival_dates.arrival_date)
)

SELECT 
  distinct_available_rooms.property_id,
  distinct_available_rooms.room_id,
  distinct_available_rooms.room_price_policy_id,
  distinct_available_rooms.total
FROM properties
JOIN LATERAL (
  SELECT * FROM properties_with_available_rooms
  WHERE properties.id = properties_with_available_rooms.property_id
  ORDER BY total
  LIMIT 3
) distinct_available_rooms ON distinct_available_rooms.property_id = properties.id        
WHERE properties.id IN (1, 4)
ORDER BY distinct_available_rooms.total