Real Estate Investment Analytics: Investor Performance Report with Profit Ranking

investor_performance_analytics.sql

SELECT
    i.id AS investor_id,
    i.name AS investor_name,

    -- Aggregated contributions and distributions
    SUM(
        CASE 
            WHEN c.cashflow_type = 'CONTRIBUTION' THEN c.amount
            ELSE 0
        END
    ) AS total_contributed,

    SUM(
        CASE 
            WHEN c.cashflow_type = 'DISTRIBUTION' THEN c.amount
            ELSE 0
        END
    ) AS total_distributed,

    SUM(
        CASE 
            WHEN c.cashflow_type = 'DISTRIBUTION' THEN c.amount
            WHEN c.cashflow_type = 'CONTRIBUTION' THEN -c.amount
            ELSE 0
        END
    ) AS net_profit,

    COUNT(DISTINCT p.id) AS properties_count,

    AVG(d.target_irr) AS avg_target_irr,

    -- Rank investors by net profit (window function)
    RANK() OVER (
        ORDER BY 
            SUM(
                CASE 
                    WHEN c.cashflow_type = 'DISTRIBUTION' THEN c.amount
                    WHEN c.cashflow_type = 'CONTRIBUTION' THEN -c.amount
                    ELSE 0
                END
            ) DESC
    ) AS profit_rank

FROM investors i
JOIN investor_investments ii 
    ON ii.investor_id = i.id
JOIN investment_deals d 
    ON d.id = ii.deal_id
JOIN properties p 
    ON p.id = d.property_id
LEFT JOIN cashflows c 
    ON c.deal_id = d.id

-- Consider only active/closed deals with actual cashflows
WHERE d.status IN ('ACTIVE', 'CLOSED')

GROUP BY
    i.id,
    i.name

HAVING
    -- Only show investors who have actually contributed something
    SUM(
        CASE 
            WHEN c.cashflow_type = 'CONTRIBUTION' THEN c.amount
            ELSE 0
        END
    ) > 0

ORDER BY
    net_profit DESC;