-- Query: New host summary by activity & plan
-- Result variable: new_host_summary

-- Summary: new hosts by activity level and plan type
SELECT
    plan_type,
    CASE
        WHEN is_dau = 1 THEN 'DAU'
        WHEN is_7day_active = 1 THEN '7-day Active'
        WHEN is_30day_active = 1 THEN '30-day Active'
        ELSE 'Inactive'
    END AS activity_level,
    COUNT(DISTINCT host_id) AS host_count
FROM new_hosts_last_week
GROUP BY
    CASE
        WHEN is_dau = 1 THEN 'DAU'
        WHEN is_7day_active = 1 THEN '7-day Active'
        WHEN is_30day_active = 1 THEN '30-day Active'
        ELSE 'Inactive'
    END,
    plan_type
ORDER BY
    CASE
        WHEN activity_level = 'DAU' THEN 1
        WHEN activity_level = '7-day Active' THEN 2
        WHEN activity_level = '30-day Active' THEN 3
        ELSE 4
    END,
    host_count DESC