[jerry40]

As far as I understand, 'project_commits' and 'commits' have one-by-one relationship (project_commits.commit_id = commits.id). From my point of view it is a strange design since they could just add 'project_id' column into 'commits' table. 'project_commits' table seems to be redundant here.

I'd write this query:

    select pc.project_id, date_format(c.created_at, '%x%v1'), count(*)
    from commits c
        join project_commits pc 
            on c.id = pc.commit_id
    group by pc.project_id, date_format(c.created_at, '%x%v1')

And I'd use left join only at the stage when it is needed to join 'projects' dictionary with the result of the query.