Node Classification¶

-- int_nodes_geolocated.sql
-- IP enrichment, VPN flagging, and latency checks

SELECT
    n.peer_id,
    n.ip_address,
    -- Geographic enrichment from MaxMind GeoIP2
    geo.country_code,
    geo.city,
    geo.latitude,
    geo.longitude,
    -- ASN enrichment
    asn.autonomous_system_number AS asn_number,
    asn.autonomous_system_organization AS asn_org,
    -- VPN / proxy detection
    CASE
        WHEN vpn.ip_address IS NOT NULL THEN true
        WHEN asn.autonomous_system_organization ILIKE '%vpn%' THEN true
        WHEN asn.autonomous_system_organization ILIKE '%proxy%' THEN true
        ELSE false
    END AS is_vpn_flagged,
    -- Latency statistics from crawl probes
    avg(n.latency_ms) AS avg_latency_ms,
    stddevPop(n.latency_ms) AS stddev_latency_ms,
    count(DISTINCT toDate(n.crawl_timestamp)) AS days_active,
    count(DISTINCT n.ip_address) AS distinct_ips_observed
FROM {{ ref('stg_chao1_observers') }} n
LEFT JOIN {{ ref('stg_maxmind_geoip') }} geo
    ON n.ip_address = geo.ip_address
LEFT JOIN {{ ref('stg_maxmind_asn') }} asn
    ON n.ip_address = asn.ip_address
LEFT JOIN {{ ref('stg_vpn_ip_ranges') }} vpn
    ON n.ip_address = vpn.ip_address
GROUP BY
    n.peer_id, n.ip_address,
    geo.country_code, geo.city, geo.latitude, geo.longitude,
    asn.autonomous_system_number, asn.autonomous_system_organization,
    vpn.ip_address

-- int_esg_node_classification.sql
-- Classifies nodes into Home Staker, Professional, or Cloud Hosted

WITH cloud_asns AS (
    SELECT asn_number, provider_name
    FROM {{ ref('stg_cloud_provider_asns') }}
),

classified AS (
    SELECT
        g.peer_id,
        g.ip_address,
        g.country_code,
        g.city,
        g.asn_number,
        g.asn_org,
        g.is_vpn_flagged,
        g.avg_latency_ms,

        -- Classification CASE: strict priority order
        CASE
            -- Priority 1: Cloud provider ASN match
            WHEN c.asn_number IS NOT NULL
                THEN 'Cloud Hosted'

            -- Priority 2: Hosting / datacenter ASN patterns
            WHEN g.asn_org ILIKE '%hosting%'
                OR g.asn_org ILIKE '%datacenter%'
                OR g.asn_org ILIKE '%data center%'
                OR g.asn_org ILIKE '%colocation%'
                OR g.asn_org ILIKE '%server%'
                THEN 'Professional'

            -- Priority 3: Residential ISP indicators
            WHEN g.asn_org ILIKE '%telecom%'
                OR g.asn_org ILIKE '%broadband%'
                OR g.asn_org ILIKE '%cable%'
                OR g.asn_org ILIKE '%fiber%'
                OR g.asn_org ILIKE '%dsl%'
                OR g.asn_org ILIKE '%residential%'
                OR g.asn_org ILIKE '%mobile%'
                THEN 'Home Staker'

            -- Default: Professional (conservative mid-range estimate)
            ELSE 'Professional'
        END AS node_category,

        -- Confidence score
        CASE
            WHEN c.asn_number IS NOT NULL THEN 'high'
            WHEN g.asn_org ILIKE '%hosting%'
                OR g.asn_org ILIKE '%datacenter%' THEN 'medium'
            WHEN g.asn_org ILIKE '%telecom%'
                OR g.asn_org ILIKE '%broadband%' THEN 'medium'
            ELSE 'low'
        END AS classification_confidence,

        -- Cloud provider name (if applicable)
        c.provider_name AS cloud_provider

    FROM {{ ref('int_nodes_geolocated') }} g
    LEFT JOIN cloud_asns c
        ON g.asn_number = c.asn_number
)

SELECT * FROM classified