1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40
| SELECT SQL_CALC_FOUND_ROWS
property_id, property_reference, property_property_type_id, property_property_type_sale_id,
property_property_housing_id, property_price_type_id, property_price, property_unified_price,
property_sqft, property_agent_id, property_bedroom_id, property_loc_community_id,
property_loc_subcommunity_id, property_loc_tower_id, property_nbimages, property_published, property_date_creation,
property_deleted, agent_name, property_type_name, property_type_sale_name, property_housing_name,
bedroom_name, loc_city_name, loc_community_name, loc_subcommunity_name,
loc_tower_name, price_type_name, property_property_showstyle_id, property_showstyle_name,
IFNULL(SUM(stats_property_history_visits),0) as visits, IFNULL(SUM(stats_property_history_phones),0) as phones,
IFNULL(SUM(stats_property_history_emails),0) as emails, IFNULL(SUM(stats_property_history_offers),0) as offers,
IFNULL(SUM(stats_property_history_leads),0) as leads, IFNULL(SUM(stats_property_history_impressions),0) as impressions,
IFNULL(SUM(stats_property_history_visits)/SUM(stats_property_history_impressions),0) as ctr1,
IFNULL(SUM(stats_property_history_leads)/SUM(stats_property_history_visits),0) as ctr2,
IFNULL(AVG(stats_property_history_property_ranking_total),0) as ranking_total,
IFNULL(MAX(stats_property_history_property_showstyle_level),1) as property_showstyle_level
FROM stats_property_history
LEFT JOIN property p on stats_property_history_property_id = property_id
LEFT JOIN property_type ON property_type_id = property_property_type_id
LEFT JOIN property_type_sale ON property_type_sale_id = property_property_type_sale_id
LEFT JOIN property_housing ON property_housing_id = property_property_housing_id
LEFT JOIN price_type ON price_type_id = property_price_type_id
LEFT JOIN agent ON agent_id = property_agent_id
LEFT JOIN bedroom ON bedroom_id = property_bedroom_id
LEFT JOIN loc_community ON loc_community_id = property_loc_community_id
LEFT JOIN loc_city ON loc_city_id = loc_community_loc_city_id
LEFT JOIN loc_subcommunity ON loc_subcommunity_id = property_loc_subcommunity_id
LEFT JOIN loc_tower ON loc_tower_id = property_loc_tower_id
LEFT JOIN property_showstyle ON property_showstyle_id = property_property_showstyle_id
WHERE stats_property_history_date BETWEEN '2011-03-22' AND '2011-04-22'
AND stats_property_history_property_type_id = '1'
AND stats_property_history_property_type_sale_id = '1'
AND (stats_property_history_property_housing_id = '1'
AND (stats_property_history_agent_id = '1'
AND stats_property_history_loc_city_id = '1'
AND (stats_property_history_loc_community_id = '1'
AND (stats_property_history_loc_subcommunity_id = '1'
AND (stats_property_history_loc_tower_id = '1'
GROUP BY stats_property_history_property_id
ORDER BY leads DESC
LIMIT 0, 100; |
Partager