I checked the two examples of the query that you passed, then I found the common value that was the project_id and I modified the second example to bring the project_id and the created_date of the commits. Then I decided as you mention to format the created_date to bring the year and the month and to add it as a filter.
Then I join the two examples in a CTE and I only SELECT the names of the columns that are needed.
Finally I used a ROW_NUMBER only to bring the maximum value of the processed bytes of every language by country/year/month.
WITH ltb as(
select pl3.lang, sum(pl3.size) as total_bytes, pl3.project_id
from (
select pl2.bytes as size, pl2.language as lang, pl2.project_id
from (
select pl.language as lang, max(pl.created_at) as latest, pl.project_id as project_id
from `ghtorrent-bq.ght.project_languages` pl
join `ghtorrent-bq.ght.projects` p on p.id = pl.project_id
where p.deleted is false
and p.forked_from is null
group by lang, project_id
) pl1 join `ghtorrent-bq.ght.project_languages` pl2 on pl1.project_id = pl2.project_id
and pl1.latest = pl2.created_at
and pl1.lang = pl2.language
) pl3
group by pl3.lang, pl3.project_id
order by total_bytes desc
), fprt as(
SELECT country_code, count(*) AS NoOfCommits, c.project_id,
FORMAT_TIMESTAMP("%m", c.created_at)
AS formattedmonth,FORMAT_TIMESTAMP("%b", c.created_at)
AS formattedmonthname, FORMAT_TIMESTAMP("%Y", c.created_at)
AS formattedyear,
FROM `ghtorrent-bq.ght.commits` AS c
JOIN `ghtorrent-bq.ght.users` AS u
ON c.Committer_Id = u.id
WHERE NOT u.fake and country_code is not null
GROUP BY country_code, c.project_id, formattedmonth, formattedyear, formattedmonthname
ORDER BY NoOfCommits DESC
), almst as(
SELECT country_code,formattedmonth, formattedmonthname, formattedyear, lang, NoOfCommits, total_bytes FROM fprt JOIN ltb
on ltb.project_id=fprt.project_id
where country_code is not null
)
SELECT country_code, formattedyear as year, formattedmonthname as month, lang, NoOfCommits, total_bytes
FROM
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY country_code, formattedyear, formattedmonth ORDER BY total_bytes DESC) rn
FROM almst
) t
WHERE rn = 1
ORDER BY formattedyear asc, formattedmonth asc
Output:
