Hogql Recipes

SELECT
    number,
    title,
    author_handle,
    (author_handle LIKE '%[bot]'
        OR author_handle IN ('dependabot', 'github-actions', 'posthog-bot', 'renovate')) AS is_bot,
    arrayElement(repo_parts, 1) AS repo_owner,
    arrayElement(repo_parts, 2) AS repo_name,
    if(merged_at IS NOT NULL, 'merged', raw_state) AS state,
    is_draft,
    created_at,
    merged_at,
    closed_at,
    head_sha,
    if(merged_at IS NOT NULL, dateDiff('second', created_at, merged_at), NULL) AS open_to_merge_seconds
FROM (
    SELECT
        number,
        title,
        state AS raw_state,
        coalesce(draft, false) AS is_draft,
        ifNull(JSONExtractString(user, 'login'), '') AS author_handle,
        splitByChar('/', ifNull(JSONExtractString(base, 'repo', 'full_name'), '')) AS repo_parts,
        JSONExtractString(head, 'sha') AS head_sha,
        parseDateTimeBestEffort(created_at) AS created_at,
        parseDateTimeBestEffort(merged_at) AS merged_at,
        parseDateTimeBestEffort(closed_at) AS closed_at
    FROM github_pull_requests
)

SELECT
    workflow_name,
    head_sha,
    head_branch,
    status,
    conclusion,
    run_started_at,
    run_attempt,
    pr_number,
    if(status = 'completed', dateDiff('second', run_started_at, updated_at), NULL) AS duration_seconds,
    arrayElement(repo_parts, 1) AS repo_owner,
    arrayElement(repo_parts, 2) AS repo_name
FROM (
    SELECT
        name AS workflow_name,
        head_sha,
        head_branch,
        status,
        conclusion,
        run_attempt,
        JSONExtractInt(arrayElement(JSONExtractArrayRaw(ifNull(pull_requests, '[]')), 1), 'number') AS pr_number,
        splitByChar('/', ifNull(JSONExtractString(repository, 'full_name'), '')) AS repo_parts,
        parseDateTimeBestEffort(run_started_at) AS run_started_at,
        parseDateTimeBestEffort(updated_at) AS updated_at
    FROM github_workflow_runs
)

WITH prs AS (<PR base>)
SELECT
    toStartOfWeek(merged_at) AS week,
    median(open_to_merge_seconds) / 3600 AS p50_hours,
    quantile(0.95)(open_to_merge_seconds) / 3600 AS p95_hours,
    count() AS merged_prs
FROM prs
WHERE merged_at >= now() - INTERVAL 90 DAY
  AND NOT is_bot
  AND NOT is_draft
GROUP BY week
ORDER BY week

WITH runs AS (<workflow-runs base>)
SELECT
    toStartOfWeek(run_started_at) AS week,
    workflow_name,
    count() AS runs,
    countIf(conclusion = 'success')
        / nullIf(countIf(conclusion IN ('success', 'failure', 'timed_out', 'startup_failure', 'stale')), 0)
            AS success_rate,
    quantileIf(0.95)(duration_seconds, conclusion = 'success') AS p95_seconds
FROM runs
WHERE status = 'completed'
  AND run_started_at >= now() - INTERVAL 60 DAY
GROUP BY week, workflow_name
HAVING runs >= 5
ORDER BY week, runs DESC

WITH prs AS (<PR base>)
SELECT
    toStartOfWeek(created_at) AS week,
    countIf(state = 'merged') AS merged,
    countIf(state = 'closed') AS closed_unmerged
FROM prs
WHERE created_at >= now() - INTERVAL 90 DAY
  AND NOT is_bot
  AND NOT is_draft
GROUP BY week
ORDER BY week

WITH prs AS (<PR base>),
runs AS (<workflow-runs base>),
ci AS (
    SELECT
        head_sha,
        countIf(s = 'completed' AND c IN ('failure', 'timed_out', 'startup_failure', 'stale')) AS failing
    FROM (
        SELECT head_sha, workflow_name,
            argMax(status, run_started_at) AS s,
            argMax(conclusion, run_started_at) AS c
        FROM runs
        GROUP BY head_sha, workflow_name
    )
    GROUP BY head_sha
)
SELECT count() AS open_prs_failing_ci
FROM prs
INNER JOIN ci ON prs.head_sha = ci.head_sha
WHERE prs.state = 'open' AND ci.failing > 0

SELECT
    toStartOfWeek(created_at) AS week,
    workflow_name,
    round(sum(estimated_cost_usd), 2) AS estimated_cost_usd
FROM engineering_analytics_job_costs
WHERE created_at >= now() - INTERVAL 60 DAY
GROUP BY week, workflow_name
ORDER BY week, estimated_cost_usd DESC

WITH prs AS (<PR base>),
first_review AS (
    SELECT pr_number, min(parseDateTimeBestEffort(submitted_at)) AS first_review_at
    FROM github_reviews
    GROUP BY pr_number
)
SELECT
    toStartOfWeek(prs.created_at) AS week,
    median(dateDiff('second', prs.created_at, fr.first_review_at)) / 3600 AS p50_hours,
    quantile(0.95)(dateDiff('second', prs.created_at, fr.first_review_at)) / 3600 AS p95_hours,
    count() AS reviewed_prs
FROM prs
INNER JOIN first_review fr ON prs.number = fr.pr_number
WHERE prs.created_at >= now() - INTERVAL 90 DAY
  AND NOT prs.is_bot
GROUP BY week
ORDER BY week

WITH prs AS (<PR base>),
members AS (
    SELECT ifNull(login, '') AS member_handle
    FROM github_team_members
    WHERE ifNull(team_slug, '') = 'my-team'
      AND ifNull(login, '') != ''
)
SELECT
    toStartOfWeek(merged_at) AS week,
    median(open_to_merge_seconds) / 3600 AS p50_hours,
    count() AS merged_prs
FROM prs
WHERE merged_at >= now() - INTERVAL 90 DAY
  AND NOT is_bot
  AND author_handle IN (SELECT member_handle FROM members)
GROUP BY week
ORDER BY week

SELECT
    toStartOfDay(timestamp) AS day,
    uniq(fingerprint) AS distinct_failures,
    count() AS failed_test_lines
FROM engineering_analytics_ci_failures
WHERE timestamp >= now() - INTERVAL 14 DAY
GROUP BY day
ORDER BY day