ЯсноПонятно24

WITH investments AS (
    SELECT
        country_code,
        COUNT(DISTINCT company_id) AS num_companies
    FROM
        investments_table
    WHERE
        year >= 2010 AND year <= 2012
    GROUP BY
        country_code, fund_id
),
country_stats AS (
    SELECT
        country_code,
        MIN(num_companies) AS min_num_companies,
        MAX(num_companies) AS max_num_companies,
        AVG(num_companies) AS avg_num_companies
    FROM
        investments
    WHERE
        min_num_companies > 0
    GROUP BY
        country_code
)
SELECT
    c.country_name,
    cs.min_num_companies,
    cs.max_num_companies,
    cs.avg_num_companies
FROM
    country_stats cs
JOIN countries c ON cs.country_code = c.country_code
ORDER BY
    avg_num_companies DESC, country_code
LIMIT 10;