duplicate-builds.sql

WITH reference_commits as (
        /* commits for build X */
        select commits.id
        from build_commits, commits
        where
            build_commits.build_id = 5284 and
            build_commits.commit_id = commits.id

    ), builds_sharing_commits as (
        /* builds with commits subset of build X */
        select
            builds.id
        from
            builds, reference_commits
        where
            not (exists (
                select 1
                from build_commits, commits
                where
                    build_commits.build_id = builds.id and
                    build_commits.commit_id = commits.id and
                    commits.id not in (select reference_commits.id from reference_commits)
            ))
        group by
            builds.id

    ), builds_with_same_number_of_commits as (
        select
            builds.id, builds.build_number, count(build_commits.build_id)
        from
            builds
            join
            build_commits
            on builds.id = build_commits.build_id
        group by
            builds.id, builds.build_number
        having
            count(build_commits.build_id) = (select count(*) from reference_commits)
    )

/*
select * from reference_commits
select * from builds_sharing_commits
select * from builds_with_same_number_of_commits
*/

select sharing.id
from
    builds_sharing_commits as sharing
    inner join
    builds_with_same_number_of_commits as same_no
    on sharing.id = same_no.id