AVG Time To Sell

avg_time_to_sell.sql

SELECT
	p.id AS 'PROPERTY ID',
	CONCAT_WS(
		', ',
		CONCAT_WS(
			' ',
			NULLIF(TRIM(a.building_number), ''),
			NULLIF(TRIM(a.building_name), ''),
			a.street
		),
		a.town,
		a.county,
		a.post_code
	) AS 'PROPERTY ID',
	DATE_FORMAT(
		MIN(status_sold.status_date),
		'%d/%m/%Y'
	) AS 'ACTIVE PROPERTY DATE',
	DATE_FORMAT(
		MIN(status_active.status_date),
		'%d/%m/%Y'
	) AS 'SOLD PROPERTY DATE',
	DATEDIFF(
		MIN(status_sold.status_date),
		MIN(status_active.status_date)
	) AS 'DAYS TO SELL'
FROM
	properties p
JOIN property_revisions prev ON p.id = prev.property_id
AND prev.flag = 0
JOIN addresses a ON prev.address_id = a.id
JOIN property_status_change_log AS status_sold ON p.id = status_sold.property_id
AND status_sold.`status` IN ("sold_stc")
JOIN property_status_change_log AS status_active ON p.id = status_active.property_id
AND status_active.`status` = "active"
AND (
	SELECT
		(
			DATEDIFF(
				MIN(status_sold.status_date),
				MIN(status_active.status_date)
			)
		)
) > 0
GROUP BY
	p.id;