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