Improvement: Add zero PR count when dates are not present

[JCZuurmond]

Description

Add zero PR count when a dates are not present. For example, now a week is missing when there is no PRs in that week. Which can be confusing, because the bar graph does not show this well.

Motivation

To avoid confusion.

[JCZuurmond]

@ramonvermeulen : This might be tougher then I thought. We could also risk it and assume we have a PR in every week in the last year.

[jochemloedeman]

@JCZuurmond @ramonvermeulen I played around with this a little bit, and it becomes more problematic once you start to filter on authors, which typically do not have a PR each week. We could do this;

    const weekStartsQuery = `
        SELECT DATE_TRUNC('week', start_date) AS week_start
        FROM (
            WITH recursive weeks AS (
                SELECT CURRENT_DATE - INTERVAL '1 year' + INTERVAL '1 week' AS start_date, 
                    CURRENT_DATE AS end_date
                UNION ALL
                SELECT start_date + INTERVAL '1 week',
                    end_date
                FROM weeks
                WHERE start_date < end_date
            )
            SELECT start_date
            FROM weeks
        )
    `;
    const PullRequestCountByWeekQuery = `
        SELECT DATE_TRUNC('week', CAST(created_at AS DATE)) AS orderedField,
               COUNT(DISTINCT title) AS amount
        FROM main_marts.fct_pull_requests
        ${useQueryFilter([...filters, { column: 'CAST(created_at AS DATE)', operator: '>=', target: 'date_add(CURRENT_DATE(), INTERVAL \'-1 year\')' }])}
        GROUP BY DATE_TRUNC('week', CAST(created_at AS DATE))
        ORDER BY orderedField
    `;
    const weeklyPullRequestCountsQuery = `
        WITH week_starts AS (${weekStartsQuery}),
        weekly_prs AS (${PullRequestCountByWeekQuery})
        SELECT week_starts.week_start as orderedField, COALESCE(weekly_prs.amount, 0) AS amount
        FROM week_starts
        LEFT JOIN weekly_prs ON week_starts.week_start = weekly_prs.orderedField
        ORDER BY orderedField;
    `;

Maybe we could organize the duckdb queries in seperate file(s)? To clean up the page files a bit.

[ramonvermeulen]

@JCZuurmond @ramonvermeulen I played around with this a little bit, and it becomes more problematic once you start to filter on authors, which typically do not have a PR each week. We could do this;

    const weekStartsQuery = `
        SELECT DATE_TRUNC('week', start_date) AS week_start
        FROM (
            WITH recursive weeks AS (
                SELECT CURRENT_DATE - INTERVAL '1 year' + INTERVAL '1 week' AS start_date, 
                    CURRENT_DATE AS end_date
                UNION ALL
                SELECT start_date + INTERVAL '1 week',
                    end_date
                FROM weeks
                WHERE start_date < end_date
            )
            SELECT start_date
            FROM weeks
        )
    `;
    const PullRequestCountByWeekQuery = `
        SELECT DATE_TRUNC('week', CAST(created_at AS DATE)) AS orderedField,
               COUNT(DISTINCT title) AS amount
        FROM main_marts.fct_pull_requests
        ${useQueryFilter([...filters, { column: 'CAST(created_at AS DATE)', operator: '>=', target: 'date_add(CURRENT_DATE(), INTERVAL \'-1 year\')' }])}
        GROUP BY DATE_TRUNC('week', CAST(created_at AS DATE))
        ORDER BY orderedField
    `;
    const weeklyPullRequestCountsQuery = `
        WITH week_starts AS (${weekStartsQuery}),
        weekly_prs AS (${PullRequestCountByWeekQuery})
        SELECT week_starts.week_start as orderedField, COALESCE(weekly_prs.amount, 0) AS amount
        FROM week_starts
        LEFT JOIN weekly_prs ON week_starts.week_start = weekly_prs.orderedField
        ORDER BY orderedField;
    `;

Maybe we could organize the duckdb queries in seperate file(s)? To clean up the page files a bit.

Sounds good to me, however I am not sure if that is easy/efficient to implement with react/typescript. Also because a lot of filtering/templating going on in the queries themselves, we can create some kind of hook for it and maybe move all the queries to one constant file.

Unfortunately there is not a lot of commonly used SQL-ish query libraries yet for react, main reason of course because it is not common practice to directly connect from the web browser to a database. However I found this package, also being downloaded a lot lately: https://www.npmjs.com/package/react-querybuilder

Then not implement the UI of it, but re-use some of it's hooks (https://react-querybuilder.js.org/docs/utils/hooks#usequerybuilder) not sure if this is possible, didn't deep dive into the package yet.

[ramonvermeulen]

solved by #81