derelict.sql

                        SELECT
                                u.name,
                                u.first_login,
                                (u.last_login - u.first_login) AS age,
                                ($now - GREATEST(IF(u.lastvote > $voteWindow, u.lastvote, 0), u.last_activity)) as since_last,
                                r.address,
                                'derelict' as reason
                        FROM
                                residence AS r
                        LEFT JOIN
                                user AS u ON r.owner_user_id = u.user_id
                        WHERE
                                r.owner_user_id IS NOT NULL
                                AND
                                u.user_group_id < 2
                                AND
                                u.derelict_protection = 0
                                AND
                                        (
                                        (u.first_login < $oneYearAgo && GREATEST(IF(u.lastvote > $voteWindow, u.lastvote, 0), u.last_activity) < $oneMonthAgo)
                                        ||
                                        (u.first_login > $oneMonthAgo && GREATEST(IF(u.lastvote > $voteWindow, u.lastvote, 0), u.last_activity) < $tenDaysAgo)
                                        )
                                AND
                                u.last_supported < $oneMonthAgo
                                AND
                                server_id = $serverId
                        ORDER BY age ASC