SQL fun

-- Everything about Rounds!
-- Name, customer count, due customer count, and total customer debt
SELECT rounds._id, rounds.name,
    COUNT(customers._id) AS customer_count,
    IFNULL(due_customers.customers_due,0) AS customers_due,
    IFNULL(round_debts.debt,0) AS total_debt
FROM rounds
LEFT JOIN customers ON customers.round_id = rounds._id
LEFT JOIN (
    -- Create an imaginary table of round_id -> customers_due
    SELECT customers.round_id, COUNT(customers._id) AS customers_due
    FROM customers
    WHERE (
        -- Returns 1 if there are no visits within *frequency* days, otherwise 0
        SELECT 1 - COUNT(visits._id)
        FROM visits
        WHERE visits.customer_id = customers._id
            AND (visits.DATE + customers.frequency*86400) > UNIX_TIMESTAMP()
        ORDER BY visits.DATE DESC
        LIMIT 1
    ) = 1
    GROUP BY customers.round_id
) AS due_customers ON due_customers.round_id = rounds._id
LEFT JOIN (
    -- Sum debt for each round
    SELECT customers.round_id, SUM(visits.charge) AS debt
    FROM visits
    LEFT JOIN customers ON customers._id = visits.customer_id
    WHERE visits.paid = 0
    GROUP BY customers.round_id
) AS round_debts ON round_debts.round_id = rounds._id
GROUP BY rounds._id