queries.sql

.mode markdown
-- Common queries to generate stats like in example.md
-- Total commits in release train
select
    COUNT(*) as total_commits
from
    commits
where
    major_version = <MAJOR_VERSION>;

-- Total releases
select
    COUNT(*) as total_releases
from
    releases
where
    major_version = <MAJOR_VERSION>;

-- Total minor releases
select
    COUNT(*) as minor_releases
from
    releases
where
    major_version = <MAJOR_VERSION>
    and is_minor = 1;

-- Commits by type (all)
select
    type,
    COUNT(*) as count
from
    commits
where
    major_version = <MAJOR_VERSION>
    and type is not null
group by
    type
order by
    count desc;

-- Commits by type (excluding dependency updates)
select
    type,
    COUNT(*) as count
from
    commits
where
    major_version = <MAJOR_VERSION>
    and type is not null
    and is_dependency_update = 0
group by
    type
order by
    count desc;

-- Top 10 scopes
select
    scope,
    COUNT(*) as count
from
    commits
where
    major_version = <MAJOR_VERSION>
    and scope is not null
    and is_dependency_update = 0
group by
    scope
order by
    count desc
limit
    10;

-- Dates with most releases
select
    release_date as date,
    COUNT(*) as num_releases
from
    releases
where
    major_version = <MAJOR_VERSION>
group by
    release_date
order by
    num_releases desc
limit
    3;

-- Find longest stream of consecutive commit days
with RECURSIVE commit_dates as (
    select
        distinct commit_date
    from
        commits
    where
        major_version = <MAJOR_VERSION>
    order by
        commit_date
),
date_gaps as (
    select
        commit_date,
        LAG(commit_date) OVER (
            order by
                commit_date
        ) as prev_date,
        julianday(commit_date) - julianday(
            LAG(commit_date) OVER (
                order by
                    commit_date
            )
        ) as day_diff
    from
        commit_dates
),
streak_groups as (
    select
        commit_date,
        SUM(
            case
                when day_diff > 1
                or day_diff is null then 1
                else 0
            end
        ) OVER (
            order by
                commit_date
        ) as streak_id
    from
        date_gaps
),
streaks as (
    select
        streak_id,
        MIN(commit_date) as start_date,
        MAX(commit_date) as end_date,
        julianday(MAX(commit_date)) - julianday(MIN(commit_date)) + 1 as days
    from
        streak_groups
    group by
        streak_id
)
select
    days,
    start_date,
    end_date
from
    streaks
order by
    days desc
limit
    1;