SELECT
  `layers`.`id` AS `id`,
  `hc`.`name` AS `hc_name`,
  `b`.`start_sales` AS `start_sales`,
  `b`.`name` AS `b_name`,
  `b`.`id` AS `building_id`,
  `loc`.`address` AS `address`,
  `loc`.`coordinates` AS `coords`,
  `loc`.`lat` AS `lat`,
  `loc`.`lng` AS `lng`,
  `loc`.`area` AS `area`,
  `loc`.`district` AS `district`,
  `loc`.`region` AS `region`,
  `loc`.`city` AS `city`,
  `m`.`station` AS `metro`,
  `b`.`apartments` AS `apartments`,
  `lt`.`name` AS `lot_type_name`,
  `dev`.`developer` AS `developer`,
  `hc`.`total_square` AS `total_square`,
  `hc`.`living_square` AS `living_square`,
  `b`.`rooms_st` AS `rooms_st`,
  `b`.`rooms_1` AS `rooms_1`,
  `b`.`rooms_2` AS `rooms_2`,
  `b`.`rooms_3` AS `rooms_3`,
  `b`.`rooms_4` AS `rooms_4`,
  `b`.`rooms_0` AS `rooms_0`,
  `b`.`floors_from` AS `floors_from`,
  `b`.`floors_to` AS `floors_to`,
  `oc`.`class` AS `class`,
  `cn`.`construction` AS `construction`,
  `layers`.`expo_num` AS `expo_num`,
  `layers`.`sumRmin` AS `sumRmin`,
  `layers`.`sumRavg` AS `sumRavg`,
  `layers`.`sumRmax` AS `sumRmax`,
  `b`.`update_time` AS `project_from`,
  `layers`.`createTimeMax` AS `createTimeMax`,
  `b`.`parking` AS `parking`,
  `b`.`car_spaces_num` AS `car_spaces_num`,
  `b`.`floor_type` AS `floor_type`,
  `layers`.`carSpacesPriceMin` AS `carSpacesPriceMin`,
  `layers`.`carSpacesPriceMax` AS `carSpacesPriceMax`,
  `b`.`car_square_min` AS `carSpacesSquareMin`,
  `b`.`car_square_max` AS `carSpacesSquareMax`,
  `layers`.`seller_id` AS `sellers`,
  `layers`.`desc` AS `discount_desc`,
  `layers`.`metrPriceRAvg` AS `metrPriceRAvg`,
  `layers`.`expo_st` AS `expo_st`,
  `layers`.`squareAll_st` AS `squareAll_st`,
  `layers`.`squareMin_st` AS `squareMin_st`,
  `layers`.`squareAvg_st` AS `squareAvg_st`,
  `layers`.`squareMax_st` AS `squareMax_st`,
  `layers`.`sumRmin_st` AS `sumRmin_st`,
  `layers`.`sumRavg_st` AS `sumRavg_st`,
  `layers`.`sumRmax_st` AS `sumRmax_st`,
  `layers`.`metrPriceRMin_st` AS `metrPriceRMin_st`,
  `layers`.`metrPriceRAvg_st` AS `metrPriceRAvg_st`,
  `layers`.`metrPriceRMax_st` AS `metrPriceRMax_st`,
  `layers`.`expo_1` AS `expo_1`,
  `layers`.`squareAll_1` AS `squareAll_1`,
  `layers`.`squareMin_1` AS `squareMin_1`,
  `layers`.`squareAvg_1` AS `squareAvg_1`,
  `layers`.`squareMax_1` AS `squareMax_1`,
  `layers`.`sumRmin_1` AS `sumRmin_1`,
  `layers`.`sumRavg_1` AS `sumRavg_1`,
  `layers`.`sumRmax_1` AS `sumRmax_1`,
  `layers`.`metrPriceRMin_1` AS `metrPriceRMin_1`,
  `layers`.`metrPriceRAvg_1` AS `metrPriceRAvg_1`,
  `layers`.`metrPriceRMax_1` AS `metrPriceRMax_1`,
  `layers`.`expo_2` AS `expo_2`,
  `layers`.`squareAll_2` AS `squareAll_2`,
  `layers`.`squareMin_2` AS `squareMin_2`,
  `layers`.`squareAvg_2` AS `squareAvg_2`,
  `layers`.`squareMax_2` AS `squareMax_2`,
  `layers`.`sumRmin_2` AS `sumRmin_2`,
  `layers`.`sumRavg_2` AS `sumRavg_2`,
  `layers`.`sumRmax_2` AS `sumRmax_2`,
  `layers`.`metrPriceRMin_2` AS `metrPriceRMin_2`,
  `layers`.`metrPriceRAvg_2` AS `metrPriceRAvg_2`,
  `layers`.`metrPriceRMax_2` AS `metrPriceRMax_2`,
  `layers`.`expo_3` AS `expo_3`,
  `layers`.`squareAll_3` AS `squareAll_3`,
  `layers`.`squareMin_3` AS `squareMin_3`,
  `layers`.`squareAvg_3` AS `squareAvg_3`,
  `layers`.`squareMax_3` AS `squareMax_3`,
  `layers`.`sumRmin_3` AS `sumRmin_3`,
  `layers`.`sumRavg_3` AS `sumRavg_3`,
  `layers`.`sumRmax_3` AS `sumRmax_3`,
  `layers`.`metrPriceRMin_3` AS `metrPriceRMin_3`,
  `layers`.`metrPriceRAvg_3` AS `metrPriceRAvg_3`,
  `layers`.`metrPriceRMax_3` AS `metrPriceRMax_3`,
  `layers`.`expo_4` AS `expo_4`,
  `layers`.`squareAll_4` AS `squareAll_4`,
  `layers`.`squareMin_4` AS `squareMin_4`,
  `layers`.`squareAvg_4` AS `squareAvg_4`,
  `layers`.`squareMax_4` AS `squareMax_4`,
  `layers`.`sumRmin_4` AS `sumRmin_4`,
  `layers`.`sumRavg_4` AS `sumRavg_4`,
  `layers`.`sumRmax_4` AS `sumRmax_4`,
  `layers`.`metrPriceRMin_4` AS `metrPriceRMin_4`,
  `layers`.`metrPriceRAvg_4` AS `metrPriceRAvg_4`,
  `layers`.`metrPriceRMax_4` AS `metrPriceRMax_4`,
  `layers`.`expo_0` AS `expo_0`,
  `layers`.`squareAll_0` AS `squareAll_0`,
  `layers`.`squareMin_0` AS `squareMin_0`,
  `layers`.`squareAvg_0` AS `squareAvg_0`,
  `layers`.`squareMax_0` AS `squareMax_0`,
  `layers`.`sumRmin_0` AS `sumRmin_0`,
  `layers`.`sumRavg_0` AS `sumRavg_0`,
  `layers`.`sumRmax_0` AS `sumRmax_0`,
  `layers`.`metrPriceRMin_0` AS `metrPriceRMin_0`,
  `layers`.`metrPriceRAvg_0` AS `metrPriceRAvg_0`,
  `layers`.`metrPriceRMax_0` AS `metrPriceRMax_0`,
  `layers`.*,
  `ds`.`amount` AS `amount`,
  `ds`.`flats` AS `flats`,
  `ds`.`apart` AS `apart`,
  `ds`.`car_spaces` AS `car_spaces`,
  `ds`.`non_residential` AS `non_residential`,
  `ds`.`pantry` AS `pantry`,
  `ds`.`flats_fl` AS `flats_fl`,
  `ds`.`flats_ul` AS `flats_ul`,
  `ds`.`apart_fl` AS `apart_fl`,
  `ds`.`apart_ul` AS `apart_ul`,
  `ds`.`flats_fl_mortgage` AS `flats_fl_mortgage`,
  `ds`.`flats_fl_non_mortgage` AS `flats_fl_non_mortgage`,
  `ds`.`apart_fl_mortgage` AS `apart_fl_mortgage`,
  `ds`.`apart_fl_non_mortgage` AS `apart_fl_non_mortgage`,
  `ds`.`pace_flats` AS `pace_flats`,
  `ds`.`pace_flats_fl` AS `pace_flats_fl`,
  `ds`.`pace_flats_ul` AS `pace_flats_ul`,
  `ds`.`pace_apart` AS `pace_apart`,
  `ds`.`pace_apart_fl` AS `pace_apart_fl`,
  `ds`.`pace_apart_ul` AS `pace_apart_ul`,
  `ds`.`pace_flats_pre_1` AS `pace_flats_pre_1`,
  `ds`.`pace_flats_fl_pre_1` AS `pace_flats_fl_pre_1`,
  `ds`.`pace_flats_ul_pre_1` AS `pace_flats_ul_pre_1`,
  `ds`.`pace_apart_pre_1` AS `pace_apart_pre_1`,
  `ds`.`pace_apart_fl_pre_1` AS `pace_apart_fl_pre_1`,
  `ds`.`pace_apart_ul_pre_1` AS `pace_apart_ul_pre_1`,
  `ds`.`pace_flats_pre_3` AS `pace_flats_pre_3`,
  `ds`.`pace_flats_fl_pre_3` AS `pace_flats_fl_pre_3`,
  `ds`.`pace_flats_ul_pre_3` AS `pace_flats_ul_pre_3`,
  `ds`.`pace_apart_pre_3` AS `pace_apart_pre_3`,
  `ds`.`pace_apart_fl_pre_3` AS `pace_apart_fl_pre_3`,
  `ds`.`pace_apart_ul_pre_3` AS `pace_apart_ul_pre_3`,
  `ds`.`pace_flats_pre_6` AS `pace_flats_pre_6`,
  `ds`.`pace_flats_fl_pre_6` AS `pace_flats_fl_pre_6`,
  `ds`.`pace_flats_ul_pre_6` AS `pace_flats_ul_pre_6`,
  `ds`.`pace_apart_pre_6` AS `pace_apart_pre_6`,
  `ds`.`pace_apart_fl_pre_6` AS `pace_apart_fl_pre_6`,
  `ds`.`pace_apart_ul_pre_6` AS `pace_apart_ul_pre_6`,
  `ds`.`pace_flats_pre_12` AS `pace_flats_pre_12`,
  `ds`.`pace_flats_fl_pre_12` AS `pace_flats_fl_pre_12`,
  `ds`.`pace_flats_ul_pre_12` AS `pace_flats_ul_pre_12`,
  `ds`.`pace_apart_pre_12` AS `pace_apart_pre_12`,
  `ds`.`pace_apart_fl_pre_12` AS `pace_apart_fl_pre_12`,
  `ds`.`pace_apart_ul_pre_12` AS `pace_apart_ul_pre_12`,
  `ds`.`flats_fl_square` AS `flats_fl_square`,
  `ds`.`apart_fl_square` AS `apart_fl_square`,
  `ds`.`flats_fl_square_avg` AS `flats_fl_square_avg`,
  `ds`.`apart_fl_square_avg` AS `apart_fl_square_avg`,
  `ds`.`flats_fl_metrprice_avg` AS `flats_fl_metrprice_avg`,
  `ds`.`apart_fl_metrprice_avg` AS `apart_fl_metrprice_avg`,
  `ds`.`flats_fl_sum_avg` AS `flats_fl_sum_avg`,
  `ds`.`apart_fl_sum_avg` AS `apart_fl_sum_avg`,
  `ub`.`unrealized_amount` AS `unrealized_amount`,
  `ub`.`unrealized_square` AS `unrealized_square`,
  `ub`.`avg_annual_amount` AS `avg_annual_amount`,
  `ub`.`avg_annual_square` AS `avg_annual_square`,
  `ub`.`absorption_by_lot_amount_1` AS `absorption_by_lot_amount_1`,
  `ub`.`absorption_by_square_1` AS `absorption_by_square_1`,
  `ub`.`absorption_by_lot_amount_2` AS `absorption_by_lot_amount_2`,
  `ub`.`absorption_by_square_2` AS `absorption_by_square_2`,
  `ub`.`unrealized_amount_1` AS `unrealized_amount_1`,
  `ub`.`unrealized_square_1` AS `unrealized_square_1`,
  `ub`.`avg_annual_amount_1` AS `avg_annual_amount_1`,
  `ub`.`avg_annual_square_1` AS `avg_annual_square_1`,
  `ub`.`unrealized_amount_2` AS `unrealized_amount_2`,
  `ub`.`unrealized_square_2` AS `unrealized_square_2`,
  `ub`.`avg_annual_amount_2` AS `avg_annual_amount_2`,
  `ub`.`avg_annual_square_2` AS `avg_annual_square_2`,
  `ub`.`unrealized_amount_3` AS `unrealized_amount_3`,
  `ub`.`unrealized_square_3` AS `unrealized_square_3`,
  `ub`.`avg_annual_amount_3` AS `avg_annual_amount_3`,
  `ub`.`avg_annual_square_3` AS `avg_annual_square_3`,
  `ub`.`unrealized_amount_4` AS `unrealized_amount_4`,
  `ub`.`unrealized_square_4` AS `unrealized_square_4`,
  `ub`.`avg_annual_amount_4` AS `avg_annual_amount_4`,
  `ub`.`avg_annual_square_4` AS `avg_annual_square_4`,
  `ub`.`unrealized_amount_0` AS `unrealized_amount_0`,
  `ub`.`unrealized_square_0` AS `unrealized_square_0`,
  `ub`.`avg_annual_amount_0` AS `avg_annual_amount_0`,
  `ub`.`avg_annual_square_0` AS `avg_annual_square_0`,
  `ub`.`unrealized_amount_st` AS `unrealized_amount_st`,
  `ub`.`unrealized_square_st` AS `unrealized_square_st`,
  `ub`.`avg_annual_amount_st` AS `avg_annual_amount_st`,
  `ub`.`avg_annual_square_st` AS `avg_annual_square_st`,
  `ub`.`conversion_factor` AS `conversion_factor`,
  `ub`.`conversion_factor_1` AS `conversion_factor_1`,
  `ub`.`conversion_factor_2` AS `conversion_factor_2`,
  `ub`.`conversion_factor_3` AS `conversion_factor_3`,
  `ub`.`conversion_factor_4` AS `conversion_factor_4`,
  `ub`.`conversion_factor_0` AS `conversion_factor_0`,
  `ub`.`conversion_factor_st` AS `conversion_factor_st`,
  `ps`.`metr_price` AS `metr_price`,
  `ps`.`metr_price_st` AS `metr_price_st`,
  `ps`.`metr_price_1` AS `metr_price_1`,
  `ps`.`metr_price_2` AS `metr_price_2`,
  `ps`.`metr_price_3` AS `metr_price_3`,
  `ps`.`metr_price_4` AS `metr_price_4`,
  `ps`.`metr_price_0` AS `metr_price_0`,
  `ps`.`sum` AS `sum`,
  `ps`.`sum_st` AS `sum_st`,
  `ps`.`sum_1` AS `sum_1`,
  `ps`.`sum_2` AS `sum_2`,
  `ps`.`sum_3` AS `sum_3`,
  `ps`.`sum_4` AS `sum_4`,
  `ps`.`sum_0` AS `sum_0`,
  IFNULL (ip.inc_price, '0.00') AS `inc_price`,
  IFNULL (ip.inc_price_st, '0.00') AS `inc_price_st`,
  IFNULL (ip.inc_price_1, '0.00') AS `inc_price_1`,
  IFNULL (ip.inc_price_2, '0.00') AS `inc_price_2`,
  IFNULL (ip.inc_price_3, '0.00') AS `inc_price_3`,
  IFNULL (ip.inc_price_4, '0.00') AS `inc_price_4`,
  IFNULL (ip.inc_price_0, '0.00') AS `inc_price_0`,
  IFNULL (ip.inc_sum, '0.00') AS `inc_sum`,
  IFNULL (ip.inc_sum_st, '0.00') AS `inc_sum_st`,
  IFNULL (ip.inc_sum_1, '0.00') AS `inc_sum_1`,
  IFNULL (ip.inc_sum_2, '0.00') AS `inc_sum_2`,
  IFNULL (ip.inc_sum_3, '0.00') AS `inc_sum_3`,
  IFNULL (ip.inc_sum_4, '0.00') AS `inc_sum_4`,
  IFNULL (ip.inc_sum_0, '0.00') AS `inc_sum_0`
FROM
  `bnmap_building_collection` `layers`
  INNER JOIN `parser_building` `b`
    ON b.id = layers.object_id
  INNER JOIN `core_location` `loc`
    ON b.location_id = loc.id
  INNER JOIN `parser_housing_complex` `hc`
    ON hc.id = b.housing_complex_id
  LEFT JOIN `parser_objectclass` `oc`
    ON oc.id = b.objectclass_id
  LEFT JOIN `parser_construction` `cn`
    ON cn.id = b.construction_id
  LEFT JOIN `parser_salestage` `pss`
    ON pss.id = layers.salestage_id
  LEFT JOIN `parser_area` `ar`
    ON ar.id = b.area_id
  LEFT JOIN `parser_metro` `m`
    ON m.id = b.metro_id
  LEFT JOIN `parser_developer` `dev`
    ON dev.id = b.developer_id
  LEFT JOIN `parser_lot_types` `lt`
    ON lt.id = b.apartments
  LEFT JOIN `bnmap_building_deals_collection` `ds`
    ON ds.collection_id = layers.id
  LEFT JOIN `bnmap_building_unrealized_balances` `ub`
    ON ub.collection_id = layers.id
  LEFT JOIN `bnmap_building_price_dynamics` `ps`
    ON ps.collection_id = layers.id
  LEFT JOIN `bnmap_building_increase_prices` `ip`
    ON ip.collection_id = layers.id
  INNER JOIN
    (SELECT
      MAX(sub_t2.createTimeMax) AS `max_date`,
      `sub_t2`.`object_id` AS `object_id`
    FROM
      `bnmap_building_collection` `sub_t2`
    GROUP BY `sub_t2`.`object_id`) `t2`
    ON layers.createTimeMax = t2.max_date
    AND layers.object_id = t2.object_id
WHERE (`b`.`complete` = 1)
  AND (
    `b`.`region_id` IN ('3', '1', '2', '4')
  )
  AND (
    (loc.lat IS NOT NULL)
    AND (loc.lng IS NOT NULL)
  )
  AND (`pss`.`alias` = 'frozen')