leaderboards_codebase_size.pipe

DESCRIPTION >
    Leaderboard ranking projects by codebase size, measured as the net lines of code changed
    (total insertions minus deletions) across all git commits. Only includes projects with
    git platform integrations.

NODE leaderboards_codebase_size_projects
DESCRIPTION >
    Retrieves all projects that have git integration enabled

SQL >
    SELECT id, name, slug, segmentId, logoUrl, collectionsSlugs, isLF, status
    FROM insights_projects_populated_ds
    WHERE has(connectedPlatforms, 'git')
    GROUP BY id, name, slug, segmentId, logoUrl, collectionsSlugs, isLF, status

NODE leaderboards_codebase_size_activities
DESCRIPTION >
    Calculates net lines changed per project by summing git insertions and subtracting deletions

SQL >
    SELECT segmentId, SUM(gitInsertions) - SUM(gitDeletions) AS lineDifference
    FROM activityRelations_deduplicated_cleaned_bucket_union
    WHERE platform = 'git' AND (gitInsertions > 0 OR gitDeletions > 0)
    GROUP BY segmentId

NODE leaderboards_codebase_size_result
DESCRIPTION >
    Joins project metadata with codebase size metrics and assigns rankings based on total lines changed

SQL >
    SELECT
        toStartOfInterval(now(), INTERVAL 1 day) as snapshotId,
        row_number() OVER (ORDER BY coalesce(o.lineDifference, 0) DESC) as rank,
        p.id as id,
        p.segmentId as segmentId,
        p.name as name,
        p.slug as slug,
        p.logoUrl as logoUrl,
        'codebase-size' as leaderboardType,
        CAST(coalesce(o.lineDifference, 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_codebase_size_projects p
    INNER JOIN leaderboards_codebase_size_activities o ON p.segmentId = o.segmentId
    WHERE o.lineDifference > 0
    ORDER BY value DESC

TYPE COPY
TARGET_DATASOURCE leaderboards_copy_ds
COPY_MODE append
COPY_SCHEDULE 10 1 * * *