project_insights.pipe

DESCRIPTION >
    - `project_insights.pipe` serves project insights data for a specific project.
    - Returns comprehensive metrics including project metadata (name, logoUrl, isLF), health score with category breakdowns (contributors, popularity, development, security), contributor count, software value, contributor and organization dependency metrics, leaderboard achievements, and activity metrics for both current and previous 365-day periods.
    - Parameters:
    - `slug`: Optional string for a single project slug (e.g., 'kubernetes')
    - `slugs`: Optional array of project slugs for multi-project query (e.g., ['kubernetes', 'tensorflow'])
    - `ids`: Optional array of project ids for multi-project query
    - At least one of `slug`, `slugs`, or `ids` should be provided.
    - `isLfx`: Optional integer (1 = LFX, 0 = non-LFX) to filter by LFX project status
    - `orderByField`: Optional string specifying sort field, defaults to 'name'
    - `orderByDirection`: Optional string ('asc' or 'desc'), defaults to 'asc'
    - `pageSize`: Optional integer for result limit, defaults to 10
    - `page`: Optional integer for pagination offset calculation, defaults to 0
    - Response: Project records with all insights metrics including achievements as array of (leaderboardType, rank, totalCount) tuples

TAGS "Insights, Widget", "Project"

NODE project_insights_endpoint
SQL >
    %
    SELECT
        id,
        name,
        slug,
        logoUrl,
        isLF,
        status,
        contributorCount,
        organizationCount,
        softwareValue,
        contributorDependencyCount,
        contributorDependencyPercentage,
        organizationDependencyCount,
        organizationDependencyPercentage,
        achievements,
        healthScore,
        contributorHealthScore,
        popularityHealthScore,
        developmentHealthScore,
        securityHealthScore,
        firstCommit,
        starsLast365Days,
        forksLast365Days,
        activeContributorsLast365Days,
        activeOrganizationsLast365Days,
        starsPrevious365Days,
        forksPrevious365Days,
        activeContributorsPrevious365Days,
        activeOrganizationsPrevious365Days
    FROM project_insights_copy_ds
    WHERE
        type = 'project'
        {% if defined(slug) %}
            AND slug = {{ String(slug, description="Project slug", required=False) }}
        {% end %}
        {% if defined(slugs) %}
            AND slug
            IN {{ Array(slugs, 'String', description="Filter by project slug list", required=False) }}
        {% end %}
        {% if defined(ids) %}
            AND id
            IN {{ Array(ids, 'String', description="Filter by project id list", required=False) }}
        {% end %}
        {% if defined(isLfx) %}
            AND isLF
            = {{
                UInt8(
                    isLfx, description="Filter by LFX project (1 = LFX, 0 = non-LFX)", required=False
                )
            }}
        {% end %}
    ORDER BY
        {{ column(String(orderByField, "name", description="Order by field.", required=False)) }}
        {% if String(
            orderByDirection,
            'asc',
            description="Order by direction. ASC or DESC",
            required=False,
        ) == 'asc' or String(
            orderByDirection,
            'asc',
            description="Order by direction. ASC or DESC",
            required=False,
        ) == 'ASC' %} ASC
        {% else %} DESC
        {% end %},
        name ASC
    LIMIT {{ Int32(pageSize, 10) }}
    OFFSET {{ Int32(page, 0) * Int32(pageSize, 10) }}