Условие

Дана таблица history (SQLite, файл test.db):

issue_key — ключ задачи (например, C-40460),
status — статус,
minutes_in_status — минут в статусе,
previous_status — предыдущий статус,
started_at — время появления статуса (unix ms),
ended_at — время перехода в другой статус (unix ms).

Saber test.docx — иллюстрация структуры

SQL 1. Сколько в среднем задачи каждой группы находятся в статусе Open?

Группа = первый символ ключа (для C-40460 — C).
Задача может переходить в один и тот же статус несколько раз — учитывайте все случаи.
Время — в часах с округлением до двух знаков.

SQL 2. Для задач, открытых на заданный момент времени, выведите ключ, последний статус и время его создания.

«Открыта» — значит её последний статус не Closed и не Resolved.
Запрос должен работать для любого момента времени в прошлом (параметризованный).
Время — в текстовом представлении.

Решение

Подход

SQL 1 — простая агрегация: фильтр status = 'Open' → группировка по первому символу ключа → среднее minutes_in_status → перевод в часы.

SQL 2 — point-in-time запрос: на момент T нужно найти последний переход каждой задачи. Это типичный паттерн с ROW_NUMBER() OVER (PARTITION BY issue_key ORDER BY started_at DESC).

Реализация

SQL 1. Среднее время в Open по группам

SELECT
  SUBSTR(issue_key, 1, 1)                                 AS group_letter,
  ROUND(AVG(minutes_in_status) * 1.0 / 60, 2)             AS avg_hours_in_open
FROM history
WHERE status = 'Open'
GROUP BY 1
ORDER BY 1;

Тонкости:

SUBSTR(issue_key, 1, 1) берёт первый символ. Альтернативы: LEFT(issue_key, 1) (Postgres/MySQL), SUBSTRING_INDEX(issue_key, '-', 1) (если префикс может быть длиннее одной буквы — например ABC-123). В условии приведён пример с одной буквой, поэтому SUBSTR(_, 1, 1) достаточен.
AVG(minutes_in_status) корректно усредняет по эпизодам статуса Open — задача может побывать в Open 3 раза и каждый эпизод посчитается.
ROUND(..., 2) — округление до двух знаков.
* 1.0 — для SQLite, чтобы деление не было целочисленным.

SQL 2. Открытые задачи на момент времени

WITH at_t AS (
  -- :ts_ms — параметр: точка времени в unix миллисекундах
  SELECT
    issue_key,
    status,
    started_at,
    ended_at,
    ROW_NUMBER() OVER (
      PARTITION BY issue_key
      ORDER BY started_at DESC
    ) AS rn
  FROM history
  WHERE started_at <= :ts_ms
    AND (ended_at > :ts_ms OR ended_at IS NULL)  -- статус действует на момент :ts_ms
)
SELECT
  issue_key,
  status                                                    AS last_status,
  -- Перевод unix ms в текстовое представление
  datetime(started_at / 1000, 'unixepoch')                  AS started_at_text
FROM at_t
WHERE rn = 1
  AND status NOT IN ('Closed', 'Resolved');

Тонкости:

Условие started_at <= :ts_ms AND (ended_at > :ts_ms OR ended_at IS NULL) — статус действует на момент :ts_ms, т.е. задача в этот момент находилась именно в этом статусе.
ended_at IS NULL — текущий статус (ещё не сменили).
ROW_NUMBER нужен только если на момент :ts_ms в history могло быть несколько строк с пересекающимися интервалами (некорректная вставка). Если данные чистые — ROW_NUMBER можно не использовать; достаточно фильтра по интервалу.
datetime(started_at / 1000, 'unixepoch') — SQLite-специфика. В Postgres: to_timestamp(started_at / 1000)::text.

Альтернативный SQL 2 без оконных функций

Если SQLite не поддерживает оконные функции (старые версии):

SELECT
  h.issue_key,
  h.status                                                  AS last_status,
  datetime(h.started_at / 1000, 'unixepoch')                AS started_at_text
FROM history h
WHERE h.started_at <= :ts_ms
  AND (h.ended_at > :ts_ms OR h.ended_at IS NULL)
  AND h.status NOT IN ('Closed', 'Resolved');

При корректных данных интервалы не пересекаются, значит для каждой задачи на любом :ts_ms есть ровно одна строка с таким условием — и ROW_NUMBER не нужен. Это, кстати, лучше: проще и быстрее.

Анализ / интерпретация

SQL 1 — даёт business view: какие команды (по букве проекта) дольше всего держат задачи в Open (значит, у них боттлнек на triage / груминге).

SQL 2 — point-in-time snapshot для отчётов о backlog. Параметр :ts_ms позволяет:

Получить срез на «вчера 23:59» — для ежедневной отчётности.
Сравнить backlog «месяц назад» vs «сегодня» — расти ли backlog.

Подводные камни

AVG минут vs SUM/COUNT. AVG усредняет по эпизодам, не по задачам. Если нужно «средняя задача» — сначала суммировать minutes_in_status по issue_key, потом усреднить.
Группа = первый символ vs «префикс до дефиса». В реальном Jira ключ ABC-123 — префикс ABC. Уточняйте.
ended_at NULL. Задача в текущем статусе — ended_at пустой. Не забудьте IS NULL в условии.
«Закрытые» статусы: Closed, Resolved — могут быть дополнены Done, Won't Do. Уточните полный список.
Часовые пояса. unixepoch SQLite даёт UTC. В отчёте может быть нужен local. Используйте datetime(_, 'unixepoch', 'localtime') или явный offset.
AVG с миллисекундами. Если minutes_in_status — это integer minutes (по описанию), всё ок. Если в данных миллисекунды — нужно делить на 60000, а не на 60.
Перекрывающиеся интервалы в history — баг данных. Проверьте: SUM(end - start) <= total_period.
Параметризация. В проде вместо :ts_ms — bind-параметр, не f-string в SQL.

Альтернативы

Для SQL 2 в DWH с журналом изменений часто строят slowly changing dimension type 2 (SCD2) — это и есть наша таблица history. PIT-запросы стандартизированы, можно использовать dbt + snapshot.

Эталонный ответ

SQL 1: WHERE status = 'Open' GROUP BY first_letter, ROUND(AVG(minutes)/60, 2).

SQL 2: фильтр started_at <= :ts_ms AND (ended_at > :ts_ms OR ended_at IS NULL) + status NOT IN ('Closed','Resolved'). При чистых данных ROW_NUMBER не нужен. Время — datetime(_/1000, 'unixepoch').

Saber Interactive — SQL: среднее время в статусе Open и текущие открытые задачи