Untitled

SELECT 
  current_and_prior.c_id AS current_and_prior_c_id, 
  current_and_prior.c_row_num AS current_and_prior_c_row_num, 
  current_and_prior.p_id AS current_and_prior_p_id, 
  current_and_prior.p_row_num AS current_and_prior_p_row_num, 
  current_and_prior.street_name AS current_and_prior_street_name, 
  current_and_prior.country AS current_and_prior_country, 
  current_and_prior.c_floor_area AS current_and_prior_c_floor_area, 
  current_and_prior.p_floor_area AS current_and_prior_p_floor_area, 
  current_and_prior.floor_area_diff AS current_and_prior_floor_area_diff, 
  current_and_prior.c_tiv_building AS current_and_prior_c_tiv_building, 
  current_and_prior.p_tiv_building AS current_and_prior_p_tiv_building, 
  current_and_prior.tiv_diff AS current_and_prior_tiv_diff, 
  CASE WHEN (
    current_and_prior.c_tiv_building = current_and_prior.p_tiv_building
  ) THEN ? WHEN (
    current_and_prior.c_tiv_building > current_and_prior.p_tiv_building
  ) THEN ? WHEN (
    current_and_prior.c_tiv_building < current_and_prior.p_tiv_building
  ) THEN ? END AS tiv_change 
FROM 
  (
    SELECT 
      current.id AS c_id, 
      row_number() OVER (
        PARTITION BY current.id 
        ORDER BY 
          current.address_id
      ) AS c_row_num, 
      prior.id AS p_id, 
      row_number() OVER (
        PARTITION BY prior.id 
        ORDER BY 
          current.address_id
      ) AS p_row_num, 
      current.street_name AS street_name, 
      current.code AS country, 
      current.floor_area AS c_floor_area, 
      prior.floor_area AS p_floor_area, 
      abs(
        current.floor_area - prior.floor_area
      ) AS floor_area_diff, 
      current.tiv_building AS c_tiv_building, 
      prior.tiv_building AS p_tiv_building, 
      current.tiv_building - prior.tiv_building AS tiv_diff 
    FROM 
      (
        SELECT 
          property_sov_input.id AS id, 
          property_sov_input.address_id AS address_id, 
          address.street_name AS street_name, 
          country.code AS code, 
          property_sov_input.floor_area AS floor_area, 
          property_sov_input.tiv_building AS tiv_building 
        FROM 
          property_sov_input 
          JOIN address ON address.id = property_sov_input.address_id 
          LEFT OUTER JOIN country ON country.id = address.country_id 
        WHERE 
          property_sov_input.quote_id = ?
      ) AS current 
      LEFT OUTER JOIN (
        SELECT 
          property_sov_input.id AS id, 
          property_sov_input.address_id AS address_id, 
          address.street_name AS street_name, 
          country.code AS code, 
          property_sov_input.floor_area AS floor_area, 
          property_sov_input.tiv_building AS tiv_building 
        FROM 
          property_sov_input 
          JOIN address ON address.id = property_sov_input.address_id 
          LEFT OUTER JOIN country ON country.id = address.country_id 
        WHERE 
          property_sov_input.quote_id = (
            SELECT 
              quote.prior_quote_id 
            FROM 
              quote 
            WHERE 
              quote.id = ?
          )
      ) AS prior ON current.address_id = prior.address_id 
    ORDER BY 
      current.address_id, 
      floor_area_diff
  ) AS current_and_prior 
WHERE 
  current_and_prior.c_row_num = ? 
  AND current_and_prior.p_row_num = ?