leaderboards_small_project_commit.pipe

DESCRIPTION >
    Leaderboard ranking small projects (50 or fewer contributors) by total commit count. Identifies
    small teams with massive output, showing which smaller projects are highly productive despite
    limited contributor counts.

NODE leaderboards_small_project_commit_projects
DESCRIPTION >
    Retrieves all projects with 50 or fewer contributors

SQL >
    SELECT id, name, slug, segmentId, logoUrl, collectionsSlugs, isLF, status
    FROM insights_projects_populated_ds
    WHERE contributorCount <= 50
    GROUP BY id, name, slug, segmentId, logoUrl, collectionsSlugs, isLF, status

NODE leaderboards_small_project_commit_activities
DESCRIPTION >
    Counts total git commits for each project

SQL >
    SELECT segmentId, count() as commits
    FROM activityRelations_deduplicated_cleaned_bucket_union
    WHERE activityId != '' AND type = 'authored-commit' AND platform = 'git'
    GROUP BY segmentId

NODE leaderboards_small_project_commit_results
DESCRIPTION >
    Joins small project metadata with commit counts, ranks by highest commit activity

SQL >
    SELECT
        toStartOfInterval(now(), INTERVAL 1 day) as snapshotId,
        row_number() OVER (ORDER BY coalesce(o.commits, 0) DESC) as rank,
        p.id as id,
        p.segmentId as segmentId,
        p.name as name,
        p.slug as slug,
        p.logoUrl as logoUrl,
        'small-teams-massive-output' as leaderboardType,
        cast(coalesce(o.commits, 0) as Float64) as value,
        0.0 as previousPeriodValue,
        p.collectionsSlugs as collectionsSlugs,
        p.isLF as isLF,
        [] as githubHandleArray,
        p.status as status,
        count() OVER () as totalCount
    FROM leaderboards_small_project_commit_projects p
    INNER JOIN leaderboards_small_project_commit_activities o ON p.segmentId = o.segmentId
    WHERE o.commits > 0
    ORDER BY value DESC

TYPE COPY
TARGET_DATASOURCE leaderboards_copy_ds
COPY_MODE append
COPY_SCHEDULE 0 3 * * *