latest_data as (

select * from (

select row_number() over (partition by ENDPOINT_NAME, ENDPOINT_GITHUB order by SNAPSHOT_TIMESTAMP desc) as rank_, *

from near.core.fact_developer_activity)

where rank_=1),

rep_fork_state as (

select ENDPOINT_NAME,

data:fork::BOOLEAN AS is_forked,

data:parent:full_name::STRING AS original_repo,

SPLIT_PART(data:parent:full_name::STRING, '/', 0) AS original_repo_owner,

SPLIT_PART(data:parent:full_name::STRING, '/', 1) AS original_repo_name

from latest_data

where ENDPOINT_GITHUB='general'),

contributors_commits as (

select a.*, is_forked, original_repo, original_repo_owner, original_repo_name from

(select REPO_OWNER, REPO_NAME,

contributor.value:author.login::STRING AS contributor_name,

contributor.value:author.id::NUMBER AS contributor_id,

contributor.value:total::NUMBER AS total_commits,

weekly_stats.value:a::NUMBER AS lines_added,

weekly_stats.value:c::NUMBER AS commits,

weekly_stats.value:d::NUMBER AS lines_deleted,

TO_TIMESTAMP(weekly_stats.value:w::NUMBER) AS week_start_date,-- Convert Unix timestamp to readable date

ENDPOINT_NAME

from

latest_data,

LATERAL FLATTEN(input => DATA) AS contributor, -- Flatten the array of contributors

LATERAL FLATTEN(input => contributor.value:weeks) AS weekly_stats -- Flatten the nested weeks array

where ENDPOINT_GITHUB='contributors') a

left join rep_fork_state b

on replace(a.ENDPOINT_NAME,'/stats/contributors','')=b.ENDPOINT_NAME)