Skip to content

Add work items query

What does this MR do?

  • Adds gitlab.platform.query.v1.DataInsightsPlatformQueryService/GetWorkItemAggregations query that returns:
    • Opened work items
    • Closed work items
    • Median lead time (opened - closed date)
  • Removed opened work items from protobuf, as this was the same as opened.

How do I test it?

  • The table should be created, as the testdata SQL file was added.
  • Add a bunch of test data - here's the snippet I used, thanks Claude.
INSERT INTO hierarchy_work_items (
    traversal_path,
    id,
    created_at,
    updated_at,
    closed_at,
    start_date,
    state_id,
    version,
    work_item_type_id,
    deleted
)
WITH 
    500000 as num_rows,
    toDate('2025-05-01') as min_date,
    toDate('2025-05-19') as max_date,
    toDateTime64('2025-05-01 00:00:00.000000', 6, 'UTC') as min_datetime,
    toDateTime64('2025-05-19 23:59:59.999999', 6, 'UTC') as max_datetime
SELECT
    '1/9970/' as traversal_path,
    number + 1 as id,
    toDateTime64(toUnixTimestamp(min_datetime) + rand() % (toUnixTimestamp(max_datetime) - toUnixTimestamp(min_datetime)), 6, 'UTC') as created_at,
    multiIf(
        rand() % 10 > 8, 
        NULL, -- 10% chance of NULL
        rand() % 10 > 5, 
        created_at, -- 30% chance same as created_at
        toDateTime64(toUnixTimestamp(created_at) + rand() % (toUnixTimestamp(max_datetime) - toUnixTimestamp(created_at)), 6, 'UTC') -- 60% chance of a later date
    ) as updated_at,
    multiIf(
        rand() % 10 > 7, 
        NULL, -- 30% chance of NULL
        toDateTime64(
            toUnixTimestamp(created_at) + (rand() % 864000 - 432000), -- +/- 5 days in seconds
            6, 'UTC'
        ) -- 70% chance: created_at +/- 5 days
    ) as closed_at,
    multiIf(
        rand() % 10 > 7, 
        NULL, -- 30% chance of NULL
        min_date + rand() % (max_date - min_date + 1) -- 70% random date in range
    ) as start_date,
    1 + rand() % 4 as state_id, -- Values between 1 and 4
    toDateTime64(toUnixTimestamp(min_datetime) + rand() % (toUnixTimestamp(max_datetime) - toUnixTimestamp(min_datetime)), 6, 'UTC') as version,
    1 + rand() % 4 as work_item_type_id, -- Values between 1 and 4
    rand() % 100 < 5 as deleted -- 5% chance of being deleted
FROM numbers(num_rows);
  • Start the platform make build && make start
  • Run the query:
grpcurl -plaintext -d '{
  "traversal_path": "1/9970/",
  "start_timestamp": "2023-05-01T00:00:00Z",
  "end_timestamp": "2023-05-23T00:00:00Z"
}' localhost:8083 gitlab.platform.query.v1.DataInsightsPlatformQueryService/GetWorkItemAggregations

Mentions #33

Merge request reports

Loading