Notes

WITH AddressData AS (
SELECT
r.Address,
r.shipment,
r.event_date,
r.latitude AS routingLatitude,
r.longitude AS routingLongitude,
d.latitude AS deliveryLatitude,
d.longitude AS deliveryLongitude,
distance
FROM (
SELECT
r.Address,
r.shipment,
r.event_date,
r.latitude AS routingLatitude,
r.longitude AS routingLongitude,
d.latitude AS deliveryLatitude,
d.longitude AS deliveryLongitude,
ABS(DEGREES(ACOS(
SIN(RADIANS(r.latitude)) * SIN(RADIANS(d.latitude)) +
COS(RADIANS(r.latitude)) * COS(RADIANS(d.latitude)) * COS(RADIANS(r.longitude - d.longitude))
)) * 6371000) AS distance
FROM
YourTableName r
JOIN
YourTableName d ON r.Address = d.Address
WHERE
r.routing = true
AND d.delivery = true
AND EXTRACT(MONTH FROM r.event_date) = 7
) sub
),
ShipmentCounts AS (
SELECT
Address,
SUM(CASE WHEN shipment = 'delivered' THEN 1 ELSE 0 END) AS delivered_count
FROM
YourTableName
WHERE
shipment = 'delivered'
AND event_date >= NOW() - INTERVAL '6 months'
GROUP BY
Address
HAVING
SUM(CASE WHEN shipment = 'delivered' THEN 1 ELSE 0 END) > 3
)
SELECT
ad.*,
sc.delivered_count
FROM
AddressData ad
JOIN
ShipmentCounts sc ON ad.Address = sc.Address;