This is an automated email from the ASF dual-hosted git repository. github-bot pushed a commit to branch release-v0.18-auto-cherry-pick-5966 in repository https://gitbox.apache.org/repos/asf/incubator-devlake.git
commit 5c49dffcf0a01588a48aae52cba33ca5ffae2fb1 Author: abeizn <[email protected]> AuthorDate: Tue Aug 29 14:50:35 2023 +0800 fix: remove the lake.table from the sql (#5966) --- grafana/dashboards/ContributorExperience.json | 12 +++++----- grafana/dashboards/EngineeringOverview.json | 34 +++++++++++++-------------- grafana/dashboards/WeeklyBugRetro.json | 30 +++++++++++------------ grafana/dashboards/WeeklyCommunityRetro.json | 8 +++---- 4 files changed, 42 insertions(+), 42 deletions(-) diff --git a/grafana/dashboards/ContributorExperience.json b/grafana/dashboards/ContributorExperience.json index 20af955f5..ee2833a4f 100644 --- a/grafana/dashboards/ContributorExperience.json +++ b/grafana/dashboards/ContributorExperience.json @@ -99,7 +99,7 @@ "metricColumn": "none", "queryType": "randomWalk", "rawQuery": true, - "rawSql": "with issue_comment_list as(\n select\n i.id as issue_id,\n i.url,\n i.title,\n i.created_date as issue_created_date,\n ic.id as comment_id,\n ic.created_date as comment_date,\n ic.body,\n case when ic.id is not null then rank() over (partition by i.id order by ic.created_date asc) else null end as comment_rank\n from\n lake.issues i\n join lake.board_issues bi on i.id = bi.issue_id\n join lake.boards b on bi.board_id = b.id\n lef [...] + "rawSql": "with issue_comment_list as(\n select\n i.id as issue_id,\n i.url,\n i.title,\n i.created_date as issue_created_date,\n ic.id as comment_id,\n ic.created_date as comment_date,\n ic.body,\n case when ic.id is not null then rank() over (partition by i.id order by ic.created_date asc) else null end as comment_rank\n from\n issues i\n join board_issues bi on i.id = bi.issue_id\n join boards b on bi.board_id = b.id\n left join issue_co [...] "refId": "A", "select": [ [ @@ -263,7 +263,7 @@ "metricColumn": "none", "queryType": "randomWalk", "rawQuery": true, - "rawSql": "with issue_comment_list as(\n select\n i.id as issue_id,\n i.url,\n i.title,\n i.created_date as issue_created_date,\n ic.id as comment_id,\n ic.created_date as comment_date,\n ic.body,\n case when ic.id is not null then rank() over (partition by i.id order by ic.created_date asc) else null end as comment_rank\n from\n lake.issues i\n join lake.board_issues bi on i.id = bi.issue_id\n join lake.boards b on bi.board_id = b.id\n lef [...] + "rawSql": "with issue_comment_list as(\n select\n i.id as issue_id,\n i.url,\n i.title,\n i.created_date as issue_created_date,\n ic.id as comment_id,\n ic.created_date as comment_date,\n ic.body,\n case when ic.id is not null then rank() over (partition by i.id order by ic.created_date asc) else null end as comment_rank\n from\n issues i\n join board_issues bi on i.id = bi.issue_id\n join boards b on bi.board_id = b.id\n left join issue_co [...] "refId": "A", "select": [ [ @@ -344,7 +344,7 @@ "metricColumn": "none", "queryType": "randomWalk", "rawQuery": true, - "rawSql": "select\n count(*)\nfrom\n lake.issues i\n join lake.board_issues bi on i.id = bi.issue_id\n join lake.boards b on bi.board_id = b.id\n join lake.issue_labels il on il.issue_id = i.id\nwhere\n il.label_name = \"$label_gfi\" and\n i.status != 'DONE' and\n b.id in ($repo_id)", + "rawSql": "select\n count(*)\nfrom\n issues i\n join board_issues bi on i.id = bi.issue_id\n join boards b on bi.board_id = b.id\n join issue_labels il on il.issue_id = i.id\nwhere\n il.label_name = \"$label_gfi\" and\n i.status != 'DONE' and\n b.id in ($repo_id)", "refId": "A", "select": [ [ @@ -426,7 +426,7 @@ "metricColumn": "none", "queryType": "randomWalk", "rawQuery": true, - "rawSql": "with pr_comment_list as(\n select\n pr.id as issue_id,\n pr.url,\n pr.title,\n pr.created_date as pr_created_date,\n prc.id as comment_id,\n prc.created_date as comment_date,\n prc.account_id,\n case when prc.id is not null then rank() over (partition by pr.id order by prc.created_date asc) else null end as comment_rank\n from\n lake.pull_requests pr\n left join lake.pull_request_comments prc on pr.id = prc.pull_request_id\n where\n [...] + "rawSql": "with pr_comment_list as(\n select\n pr.id as issue_id,\n pr.url,\n pr.title,\n pr.created_date as pr_created_date,\n prc.id as comment_id,\n prc.created_date as comment_date,\n prc.account_id,\n case when prc.id is not null then rank() over (partition by pr.id order by prc.created_date asc) else null end as comment_rank\n from\n pull_requests pr\n left join pull_request_comments prc on pr.id = prc.pull_request_id\n where\n date(pr. [...] "refId": "A", "select": [ [ @@ -736,7 +736,7 @@ "value": "$__all" }, "datasource": "mysql", - "definition": "select concat(name, '-', id) from lake.repos", + "definition": "select concat(name, '-', id) from repos", "description": null, "error": null, "hide": 0, @@ -745,7 +745,7 @@ "multi": true, "name": "repo_id", "options": [], - "query": "select concat(name, '-', id) from lake.repos", + "query": "select concat(name, '-', id) from repos", "refresh": 1, "regex": "/^(?<text>.*)-(?<value>.*)$/", "skipUrlSync": false, diff --git a/grafana/dashboards/EngineeringOverview.json b/grafana/dashboards/EngineeringOverview.json index 4c25122e2..4ab102cb9 100644 --- a/grafana/dashboards/EngineeringOverview.json +++ b/grafana/dashboards/EngineeringOverview.json @@ -122,7 +122,7 @@ "metricColumn": "none", "queryType": "randomWalk", "rawQuery": true, - "rawSql": "select\n count(*)\nfrom\n lake.issues i\n\tjoin board_issues bi on i.id = bi.issue_id\n\tjoin boards b on bi.board_id = b.id\n\tjoin project_mapping pm on b.id = pm.row_id\nwhere\n pm.project_name in ($project) and\n i.priority in ($priority) and\n i.type = 'BUG' and\n date(i.created_date) between STR_TO_DATE('$month','%Y-%m-%d') and STR_TO_DATE('$month','%Y-%m-%d') + INTERVAL 1 MONTH - INTERVAL 1 DAY;", + "rawSql": "select\n count(*)\nfrom\n issues i\n\tjoin board_issues bi on i.id = bi.issue_id\n\tjoin boards b on bi.board_id = b.id\n\tjoin project_mapping pm on b.id = pm.row_id\nwhere\n pm.project_name in ($project) and\n i.priority in ($priority) and\n i.type = 'BUG' and\n date(i.created_date) between STR_TO_DATE('$month','%Y-%m-%d') and STR_TO_DATE('$month','%Y-%m-%d') + INTERVAL 1 MONTH - INTERVAL 1 DAY;", "refId": "A", "select": [ [ @@ -240,7 +240,7 @@ "metricColumn": "none", "queryType": "randomWalk", "rawQuery": true, - "rawSql": "with _issues as(\n select\n DATE_ADD(date(i.created_date), INTERVAL -DAY(date(i.created_date))+1 DAY) as time,\n count(*) as defect_count\n from\n lake.issues i\n join board_issues bi on i.id = bi.issue_id\n\t join boards b on bi.board_id = b.id\n\t join project_mapping pm on b.id = pm.row_id\n where\n pm.project_name in ($project) and\n i.priority in ($priority)\n and i.type = 'BUG'\n and $__timeFilter(i.created_date)\n and i.created_ [...] + "rawSql": "with _issues as(\n select\n DATE_ADD(date(i.created_date), INTERVAL -DAY(date(i.created_date))+1 DAY) as time,\n count(*) as defect_count\n from\n issues i\n join board_issues bi on i.id = bi.issue_id\n\t join boards b on bi.board_id = b.id\n\t join project_mapping pm on b.id = pm.row_id\n where\n pm.project_name in ($project) and\n i.priority in ($priority)\n and i.type = 'BUG'\n and $__timeFilter(i.created_date)\n and i.created_date [...] "refId": "A", "select": [ [ @@ -538,7 +538,7 @@ "metricColumn": "none", "queryType": "randomWalk", "rawQuery": true, - "rawSql": "select\n count(distinct author_name)\nfrom\n lake.commits c\n join lake.repo_commits rc on c.sha = rc.commit_sha\n join project_mapping pm on rc.repo_id = pm.row_id\nwhere\n date(authored_date) between\n STR_TO_DATE('$month','%Y-%m-%d') and\n STR_TO_DATE('$month','%Y-%m-%d') + INTERVAL 1 MONTH - INTERVAL 1 DAY and\n pm.project_name in ($project);", + "rawSql": "select\n count(distinct author_name)\nfrom\n commits c\n join repo_commits rc on c.sha = rc.commit_sha\n join project_mapping pm on rc.repo_id = pm.row_id\nwhere\n date(authored_date) between\n STR_TO_DATE('$month','%Y-%m-%d') and\n STR_TO_DATE('$month','%Y-%m-%d') + INTERVAL 1 MONTH - INTERVAL 1 DAY and\n pm.project_name in ($project);", "refId": "A", "select": [ [ @@ -656,7 +656,7 @@ "metricColumn": "none", "queryType": "randomWalk", "rawQuery": true, - "rawSql": "with _developers as(\n select\n DATE_ADD(date(c.authored_date), INTERVAL -DAY(date(c.authored_date))+1 DAY) as time,\n count(distinct author_name) as developer_count\n from\n lake.commits c\n join lake.repo_commits rc on c.sha = rc.commit_sha\n join project_mapping pm on rc.repo_id = pm.row_id\n where\n $__timeFilter(c.authored_date)\n and c.authored_date >= DATE_ADD(DATE_ADD($__timeFrom(), INTERVAL -DAY($__timeFrom())+1 DAY), INTERVAL +1 MONT [...] + "rawSql": "with _developers as(\n select\n DATE_ADD(date(c.authored_date), INTERVAL -DAY(date(c.authored_date))+1 DAY) as time,\n count(distinct author_name) as developer_count\n from\n commits c\n join repo_commits rc on c.sha = rc.commit_sha\n join project_mapping pm on rc.repo_id = pm.row_id\n where\n $__timeFilter(c.authored_date)\n and c.authored_date >= DATE_ADD(DATE_ADD($__timeFrom(), INTERVAL -DAY($__timeFrom())+1 DAY), INTERVAL +1 MONTH)\n an [...] "refId": "A", "select": [ [ @@ -750,7 +750,7 @@ "metricColumn": "none", "queryType": "randomWalk", "rawQuery": true, - "rawSql": "with _num_issues_with_sprint_updated as (\n select\n count(*) as num_issues_with_sprint_updated\n from\n lake.issues i\n join board_issues bi on i.id = bi.issue_id\n\t join boards b on bi.board_id = b.id\n\t join project_mapping pm on b.id = pm.row_id\n join lake.issue_changelogs c on i.id = c.issue_id\n where\n pm.project_name in ($project) and\n c.field_name = 'Sprint' and\n c.original_from_value != '' and\n c.original_to_value != '' an [...] + "rawSql": "with _num_issues_with_sprint_updated as (\n select\n count(*) as num_issues_with_sprint_updated\n from\n issues i\n join board_issues bi on i.id = bi.issue_id\n\t join boards b on bi.board_id = b.id\n\t join project_mapping pm on b.id = pm.row_id\n join issue_changelogs c on i.id = c.issue_id\n where\n pm.project_name in ($project) and\n c.field_name = 'Sprint' and\n c.original_from_value != '' and\n c.original_to_value != '' and\n dat [...] "refId": "A", "select": [ [ @@ -867,7 +867,7 @@ "metricColumn": "none", "queryType": "randomWalk", "rawQuery": true, - "rawSql": "with _num_issues_with_sprint_updated as (\n select\n DATE_ADD(date(i.created_date), INTERVAL -DAY(date(i.created_date))+1 DAY) as time,\n count(*) as num_issues_with_sprint_updated\n from\n lake.issues i\n join board_issues bi on i.id = bi.issue_id\n\t join boards b on bi.board_id = b.id\n\t join project_mapping pm on b.id = pm.row_id\n join lake.issue_changelogs c on i.id = c.issue_id\n where\n pm.project_name in ($project) and\n c.field_na [...] + "rawSql": "with _num_issues_with_sprint_updated as (\n select\n DATE_ADD(date(i.created_date), INTERVAL -DAY(date(i.created_date))+1 DAY) as time,\n count(*) as num_issues_with_sprint_updated\n from\n issues i\n join board_issues bi on i.id = bi.issue_id\n\t join boards b on bi.board_id = b.id\n\t join project_mapping pm on b.id = pm.row_id\n join issue_changelogs c on i.id = c.issue_id\n where\n pm.project_name in ($project) and\n c.field_name = 'Spri [...] "refId": "A", "select": [ [ @@ -956,7 +956,7 @@ "metricColumn": "none", "queryType": "randomWalk", "rawQuery": true, - "rawSql": "select\n count(*)\nfrom\n lake.pull_requests pr\n join project_mapping pm on pr.base_repo_id = pm.row_id and pm.table = 'repos' \nwhere\n pr.merged_date is not null \n and date(pr.merged_date) between\n STR_TO_DATE('$month','%Y-%m-%d')\n and STR_TO_DATE('$month','%Y-%m-%d') + INTERVAL 1 MONTH - INTERVAL 1 DAY \n and pm.project_name in ($project);", + "rawSql": "select\n count(*)\nfrom\n pull_requests pr\n join project_mapping pm on pr.base_repo_id = pm.row_id and pm.table = 'repos' \nwhere\n pr.merged_date is not null \n and date(pr.merged_date) between\n STR_TO_DATE('$month','%Y-%m-%d')\n and STR_TO_DATE('$month','%Y-%m-%d') + INTERVAL 1 MONTH - INTERVAL 1 DAY \n and pm.project_name in ($project);", "refId": "A", "select": [ [ @@ -1075,7 +1075,7 @@ "metricColumn": "none", "queryType": "randomWalk", "rawQuery": true, - "rawSql": "with _merged_prs as(\n select\n DATE_ADD(date(pr.merged_date), INTERVAL -DAY(date(pr.merged_date))+1 DAY) as time,\n count(*) as pr_merged_count\n from\n lake.pull_requests pr\n join project_mapping pm on pr.base_repo_id = pm.row_id and pm.table = 'repos' \n where\n pm.project_name in ($project)\n and pr.merged_date is not null\n and $__timeFilter(pr.merged_date)\n and pr.merged_date >= DATE_ADD(DATE_ADD($__timeFrom(), INTERVAL -DAY($__time [...] + "rawSql": "with _merged_prs as(\n select\n DATE_ADD(date(pr.merged_date), INTERVAL -DAY(date(pr.merged_date))+1 DAY) as time,\n count(*) as pr_merged_count\n from\n pull_requests pr\n join project_mapping pm on pr.base_repo_id = pm.row_id and pm.table = 'repos' \n where\n pm.project_name in ($project)\n and pr.merged_date is not null\n and $__timeFilter(pr.merged_date)\n and pr.merged_date >= DATE_ADD(DATE_ADD($__timeFrom(), INTERVAL -DAY($__timeFrom( [...] "refId": "A", "select": [ [ @@ -1168,7 +1168,7 @@ "metricColumn": "none", "queryType": "randomWalk", "rawQuery": true, - "rawSql": "select \n 100*sum(case when id in (select pull_request_id from pull_request_issues) then 1 else 0 end)/count(*) as unlinked_pr_rate\nfrom lake.pull_requests pr\njoin project_mapping pm on pr.base_repo_id = pm.row_id and pm.table = 'repos' \nwhere pm.project_name in ($project)", + "rawSql": "select \n 100*sum(case when id in (select pull_request_id from pull_request_issues) then 1 else 0 end)/count(*) as unlinked_pr_rate\nfrom pull_requests pr\njoin project_mapping pm on pr.base_repo_id = pm.row_id and pm.table = 'repos' \nwhere pm.project_name in ($project)", "refId": "A", "select": [ [ @@ -1284,7 +1284,7 @@ "metricColumn": "none", "queryType": "randomWalk", "rawQuery": true, - "rawSql": "select\n DATE_ADD(date(created_date), INTERVAL -DAY(date(created_date))+1 DAY) as time,\n 100*sum(case when id in (select pull_request_id from pull_request_issues) then 1 else 0 end)/count(*) as unlinked_pr_rate\nfrom lake.pull_requests pr\njoin project_mapping pm on pr.base_repo_id = pm.row_id and pm.table = 'repos' \nwhere pm.project_name in ($project)\nand $__timeFilter(created_date)\nand created_date >= DATE_ADD(DATE_ADD($__timeFrom(), INTERVAL -DAY($__timeFrom [...] + "rawSql": "select\n DATE_ADD(date(created_date), INTERVAL -DAY(date(created_date))+1 DAY) as time,\n 100*sum(case when id in (select pull_request_id from pull_request_issues) then 1 else 0 end)/count(*) as unlinked_pr_rate\nfrom pull_requests pr\njoin project_mapping pm on pr.base_repo_id = pm.row_id and pm.table = 'repos' \nwhere pm.project_name in ($project)\nand $__timeFilter(created_date)\nand created_date >= DATE_ADD(DATE_ADD($__timeFrom(), INTERVAL -DAY($__timeFrom())+1 [...] "refId": "A", "select": [ [ @@ -1373,7 +1373,7 @@ "metricColumn": "none", "queryType": "randomWalk", "rawQuery": true, - "rawSql": "with _commits_groupby_name_and_date as (\n select\n author_name,\n date(authored_date) as _day,\n count(*)\n from\n lake.commits c\n join lake.repo_commits rc on c.sha = rc.commit_sha\n join project_mapping pm on rc.repo_id = pm.row_id\n where\n pm.project_name in ($project) and\n WEEKDAY(authored_date) between 0 and 4 and\n date(authored_date) between\n STR_TO_DATE('$month','%Y-%m-%d') and\n STR_TO_DATE('$month','%Y-%m-%d') + [...] + "rawSql": "with _commits_groupby_name_and_date as (\n select\n author_name,\n date(authored_date) as _day,\n count(*)\n from\n commits c\n join repo_commits rc on c.sha = rc.commit_sha\n join project_mapping pm on rc.repo_id = pm.row_id\n where\n pm.project_name in ($project) and\n WEEKDAY(authored_date) between 0 and 4 and\n date(authored_date) between\n STR_TO_DATE('$month','%Y-%m-%d') and\n STR_TO_DATE('$month','%Y-%m-%d') + INTERVAL 1 [...] "refId": "A", "select": [ [ @@ -1489,7 +1489,7 @@ "metricColumn": "none", "queryType": "randomWalk", "rawQuery": true, - "rawSql": "with _commits_groupby_name_and_date as (\n select\n author_name,\n date(authored_date) as _day,\n count(*)\n from\n lake.commits c\n join lake.repo_commits rc on c.sha = rc.commit_sha\n join project_mapping pm on rc.repo_id = pm.row_id\n where\n pm.project_name in ($project) and\n (WEEKDAY(authored_date) between 0 and 4)\n and $__timeFilter(authored_date)\n and authored_date >= DATE_ADD(DATE_ADD($__timeFrom(), INTERVAL -DAY($__timeFro [...] + "rawSql": "with _commits_groupby_name_and_date as (\n select\n author_name,\n date(authored_date) as _day,\n count(*)\n from\n commits c\n join repo_commits rc on c.sha = rc.commit_sha\n join project_mapping pm on rc.repo_id = pm.row_id\n where\n pm.project_name in ($project) and\n (WEEKDAY(authored_date) between 0 and 4)\n and $__timeFilter(authored_date)\n and authored_date >= DATE_ADD(DATE_ADD($__timeFrom(), INTERVAL -DAY($__timeFrom())+1 DAY [...] "refId": "A", "select": [ [ @@ -1580,7 +1580,7 @@ "metricColumn": "none", "queryType": "randomWalk", "rawQuery": true, - "rawSql": "select\n AVG(TIMESTAMPDIFF(MINUTE, pr.created_date, pr.merged_date) / 1440)\nfrom\n lake.pull_requests pr\n join project_mapping pm on pr.base_repo_id = pm.row_id and pm.table = 'repos' \nwhere\n pm.project_name in ($project) and\n pr.merged_date is not null\n and date(pr.created_date) between\n STR_TO_DATE('$month','%Y-%m-%d') \n and STR_TO_DATE('$month','%Y-%m-%d') + INTERVAL 1 MONTH - INTERVAL 1 DAY", + "rawSql": "select\n AVG(TIMESTAMPDIFF(MINUTE, pr.created_date, pr.merged_date) / 1440)\nfrom\n pull_requests pr\n join project_mapping pm on pr.base_repo_id = pm.row_id and pm.table = 'repos' \nwhere\n pm.project_name in ($project) and\n pr.merged_date is not null\n and date(pr.created_date) between\n STR_TO_DATE('$month','%Y-%m-%d') \n and STR_TO_DATE('$month','%Y-%m-%d') + INTERVAL 1 MONTH - INTERVAL 1 DAY", "refId": "A", "select": [ [ @@ -1699,7 +1699,7 @@ "metricColumn": "none", "queryType": "randomWalk", "rawQuery": true, - "rawSql": "select\n DATE_ADD(date(pr.created_date), INTERVAL -DAY(date(pr.created_date))+1 DAY) as time,\n AVG(TIMESTAMPDIFF(MINUTE, pr.created_date, pr.merged_date) / 1440) as pr_time_to_merge_in_days\nfrom\n lake.pull_requests pr\n join project_mapping pm on pr.base_repo_id = pm.row_id and pm.table = 'repos' \nwhere\n pm.project_name in ($project) and\n pr.merged_date is not null\n and $__timeFilter(pr.created_date)\n and pr.created_date >= DATE_ADD(DATE_ADD($__timeFr [...] + "rawSql": "select\n DATE_ADD(date(pr.created_date), INTERVAL -DAY(date(pr.created_date))+1 DAY) as time,\n AVG(TIMESTAMPDIFF(MINUTE, pr.created_date, pr.merged_date) / 1440) as pr_time_to_merge_in_days\nfrom\n pull_requests pr\n join project_mapping pm on pr.base_repo_id = pm.row_id and pm.table = 'repos' \nwhere\n pm.project_name in ($project) and\n pr.merged_date is not null\n and $__timeFilter(pr.created_date)\n and pr.created_date >= DATE_ADD(DATE_ADD($__timeFrom(), [...] "refId": "A", "select": [ [ @@ -1834,7 +1834,7 @@ "metricColumn": "none", "queryType": "randomWalk", "rawQuery": true, - "rawSql": "select\n i.priority as 'Priority',\n AVG(TIMESTAMPDIFF(MINUTE, i.created_date, NOW()) / 1440) as 'Average Age'\nfrom\n lake.issues i\n join board_issues bi on i.id = bi.issue_id\n\tjoin boards b on bi.board_id = b.id\n\tjoin project_mapping pm on b.id = pm.row_id\nwhere\n pm.project_name in ($project) and\n i.status = 'TODO'\n and i.type = 'BUG'\n and i.priority in ($priority)\ngroup by\n i.priority", + "rawSql": "select\n i.priority as 'Priority',\n AVG(TIMESTAMPDIFF(MINUTE, i.created_date, NOW()) / 1440) as 'Average Age'\nfrom\n issues i\n join board_issues bi on i.id = bi.issue_id\n\tjoin boards b on bi.board_id = b.id\n\tjoin project_mapping pm on b.id = pm.row_id\nwhere\n pm.project_name in ($project) and\n i.status = 'TODO'\n and i.type = 'BUG'\n and i.priority in ($priority)\ngroup by\n i.priority", "refId": "A", "select": [ [ @@ -2082,14 +2082,14 @@ "type": "mysql", "uid": "P430005175C4C7810" }, - "definition": "select\n distinct(concat(date_format(DATE_ADD(date(created_date), INTERVAL -DAY(date(created_date))+1 DAY), '%Y-%m') , ':', date_format(DATE_ADD(date(created_date), INTERVAL -DAY(date(created_date))+1 DAY), '%Y-%m-%d'))) as month\nfrom\n lake.issues i\norder by month desc", + "definition": "select\n distinct(concat(date_format(DATE_ADD(date(created_date), INTERVAL -DAY(date(created_date))+1 DAY), '%Y-%m') , ':', date_format(DATE_ADD(date(created_date), INTERVAL -DAY(date(created_date))+1 DAY), '%Y-%m-%d'))) as month\nfrom\n issues i\norder by month desc", "hide": 0, "includeAll": false, "label": "Month", "multi": false, "name": "month", "options": [], - "query": "select\n distinct(concat(date_format(DATE_ADD(date(created_date), INTERVAL -DAY(date(created_date))+1 DAY), '%Y-%m') , ':', date_format(DATE_ADD(date(created_date), INTERVAL -DAY(date(created_date))+1 DAY), '%Y-%m-%d'))) as month\nfrom\n lake.issues i\norder by month desc", + "query": "select\n distinct(concat(date_format(DATE_ADD(date(created_date), INTERVAL -DAY(date(created_date))+1 DAY), '%Y-%m') , ':', date_format(DATE_ADD(date(created_date), INTERVAL -DAY(date(created_date))+1 DAY), '%Y-%m-%d'))) as month\nfrom\n issues i\norder by month desc", "refresh": 1, "regex": "/^(?<text>.*):(?<value>.*)$/", "skipUrlSync": false, diff --git a/grafana/dashboards/WeeklyBugRetro.json b/grafana/dashboards/WeeklyBugRetro.json index abee64503..a17d9cc05 100644 --- a/grafana/dashboards/WeeklyBugRetro.json +++ b/grafana/dashboards/WeeklyBugRetro.json @@ -118,7 +118,7 @@ "metricColumn": "none", "queryType": "randomWalk", "rawQuery": true, - "rawSql": "select\n count(*)\nfrom\n lake.issues as i\n join lake.board_issues bi on i.id = bi.issue_id\n join boards b on bi.board_id = b.id\nwhere\n i.type in ($issue_type)\n and date(i.created_date) BETWEEN curdate() - INTERVAL WEEKDAY(curdate())+7 DAY AND curdate() - INTERVAL WEEKDAY(curdate())+1 DAY\n and b.id in ($board_id)", + "rawSql": "select\n count(*)\nfrom\n issues as i\n join board_issues bi on i.id = bi.issue_id\n join boards b on bi.board_id = b.id\nwhere\n i.type in ($issue_type)\n and date(i.created_date) BETWEEN curdate() - INTERVAL WEEKDAY(curdate())+7 DAY AND curdate() - INTERVAL WEEKDAY(curdate())+1 DAY\n and b.id in ($board_id)", "refId": "A", "select": [ [ @@ -210,7 +210,7 @@ "metricColumn": "none", "queryType": "randomWalk", "rawQuery": true, - "rawSql": "select\n priority,\n count(*) as 'Issue Number'\nfrom\n lake.issues as i\n join lake.board_issues bi on i.id = bi.issue_id\n join boards b on bi.board_id = b.id\nwhere\n i.type in ($issue_type)\n and date(i.created_date) BETWEEN curdate() - INTERVAL WEEKDAY(curdate())+7 DAY AND curdate() - INTERVAL WEEKDAY(curdate())+1 DAY\n and b.id in ($board_id)\ngroup by 1", + "rawSql": "select\n priority,\n count(*) as 'Issue Number'\nfrom\n issues as i\n join board_issues bi on i.id = bi.issue_id\n join boards b on bi.board_id = b.id\nwhere\n i.type in ($issue_type)\n and date(i.created_date) BETWEEN curdate() - INTERVAL WEEKDAY(curdate())+7 DAY AND curdate() - INTERVAL WEEKDAY(curdate())+1 DAY\n and b.id in ($board_id)\ngroup by 1", "refId": "A", "select": [ [ @@ -381,7 +381,7 @@ "metricColumn": "none", "queryType": "randomWalk", "rawQuery": true, - "rawSql": "select\n i.issue_key as 'Issue Number',\n i.title as 'Title',\n i.url as 'Url'\nfrom\n lake.issues as i\n\tjoin lake.board_issues bi on i.id = bi.issue_id\n\tjoin lake.boards b on bi.board_id = b.id\nwhere\n i.type in ($issue_type)\n and date(i.created_date) BETWEEN curdate() - INTERVAL WEEKDAY(curdate())+7 DAY AND curdate() - INTERVAL WEEKDAY(curdate())+1 DAY\n and b.id in ($board_id)", + "rawSql": "select\n i.issue_key as 'Issue Number',\n i.title as 'Title',\n i.url as 'Url'\nfrom\n issues as i\n\tjoin board_issues bi on i.id = bi.issue_id\n\tjoin boards b on bi.board_id = b.id\nwhere\n i.type in ($issue_type)\n and date(i.created_date) BETWEEN curdate() - INTERVAL WEEKDAY(curdate())+7 DAY AND curdate() - INTERVAL WEEKDAY(curdate())+1 DAY\n and b.id in ($board_id)", "refId": "A", "select": [ [ @@ -469,7 +469,7 @@ "metricColumn": "none", "queryType": "randomWalk", "rawQuery": true, - "rawSql": "select\n count(*)\nfrom\n lake.issues as i\n join board_issues bi on i.id = bi.issue_id\n\tjoin boards b on bi.board_id = b.id\nwhere\n i.type in ($issue_type)\n and status = 'DONE'\n and date(i.resolution_date) BETWEEN curdate() - INTERVAL WEEKDAY(curdate())+7 DAY AND curdate() - INTERVAL WEEKDAY(curdate())+1 DAY\n and b.id in ($board_id)", + "rawSql": "select\n count(*)\nfrom\n issues as i\n join board_issues bi on i.id = bi.issue_id\n\tjoin boards b on bi.board_id = b.id\nwhere\n i.type in ($issue_type)\n and status = 'DONE'\n and date(i.resolution_date) BETWEEN curdate() - INTERVAL WEEKDAY(curdate())+7 DAY AND curdate() - INTERVAL WEEKDAY(curdate())+1 DAY\n and b.id in ($board_id)", "refId": "A", "select": [ [ @@ -562,7 +562,7 @@ "metricColumn": "none", "queryType": "randomWalk", "rawQuery": true, - "rawSql": "select\n avg(lead_time_minutes / 1440)\nfrom\n lake.issues as i\n\tjoin board_issues bi on i.id = bi.issue_id\n\tjoin boards b on bi.board_id = b.id\nwhere\n i.type in ($issue_type)\n and status = 'DONE'\n and date(i.resolution_date) BETWEEN curdate() - INTERVAL WEEKDAY(curdate())+7 DAY AND curdate() - INTERVAL WEEKDAY(curdate())+1 DAY\n and b.id in ($board_id)", + "rawSql": "select\n avg(lead_time_minutes / 1440)\nfrom\n issues as i\n\tjoin board_issues bi on i.id = bi.issue_id\n\tjoin boards b on bi.board_id = b.id\nwhere\n i.type in ($issue_type)\n and status = 'DONE'\n and date(i.resolution_date) BETWEEN curdate() - INTERVAL WEEKDAY(curdate())+7 DAY AND curdate() - INTERVAL WEEKDAY(curdate())+1 DAY\n and b.id in ($board_id)", "refId": "A", "select": [ [ @@ -706,7 +706,7 @@ "metricColumn": "none", "queryType": "randomWalk", "rawQuery": true, - "rawSql": "select\n i.issue_key as 'Issue Number',\n i.title as 'Title',\n lead_time_minutes/1440 as 'Lead Time in Days',\n i.url as 'Url'\nfrom\n lake.issues as i\n\tjoin board_issues bi on i.id = bi.issue_id\n\tjoin boards b on bi.board_id = b.id\nwhere\n i.type in ($issue_type)\n and status = 'DONE'\n and date(i.resolution_date) BETWEEN curdate() - INTERVAL WEEKDAY(curdate())+7 DAY AND curdate() - INTERVAL WEEKDAY(curdate())+1 DAY\n and b.id in ($board_id)", + "rawSql": "select\n i.issue_key as 'Issue Number',\n i.title as 'Title',\n lead_time_minutes/1440 as 'Lead Time in Days',\n i.url as 'Url'\nfrom\n issues as i\n\tjoin board_issues bi on i.id = bi.issue_id\n\tjoin boards b on bi.board_id = b.id\nwhere\n i.type in ($issue_type)\n and status = 'DONE'\n and date(i.resolution_date) BETWEEN curdate() - INTERVAL WEEKDAY(curdate())+7 DAY AND curdate() - INTERVAL WEEKDAY(curdate())+1 DAY\n and b.id in ($board_id)", "refId": "A", "select": [ [ @@ -825,7 +825,7 @@ "metricColumn": "none", "queryType": "randomWalk", "rawQuery": true, - "rawSql": "select\n concat('#',i.issue_key, ' ', i.title) as issue_key,\n lead_time_minutes/1440 as lead_time\nfrom\n lake.issues as i\n\tjoin board_issues bi on i.id = bi.issue_id\n\tjoin boards b on bi.board_id = b.id\nwhere\n i.type in ($issue_type)\n and status = 'DONE'\n and date(i.resolution_date) BETWEEN curdate() - INTERVAL WEEKDAY(curdate())+7 DAY AND curdate() - INTERVAL WEEKDAY(curdate())+1 DAY\n and b.id in ($board_id)\norder by lead_time desc", + "rawSql": "select\n concat('#',i.issue_key, ' ', i.title) as issue_key,\n lead_time_minutes/1440 as lead_time\nfrom\n issues as i\n\tjoin board_issues bi on i.id = bi.issue_id\n\tjoin boards b on bi.board_id = b.id\nwhere\n i.type in ($issue_type)\n and status = 'DONE'\n and date(i.resolution_date) BETWEEN curdate() - INTERVAL WEEKDAY(curdate())+7 DAY AND curdate() - INTERVAL WEEKDAY(curdate())+1 DAY\n and b.id in ($board_id)\norder by lead_time desc", "refId": "A", "select": [ [ @@ -913,7 +913,7 @@ "metricColumn": "none", "queryType": "randomWalk", "rawQuery": true, - "rawSql": "select\n count(*)\nfrom \n lake.issues i\n\tjoin board_issues bi on i.id = bi.issue_id\n\tjoin boards b on bi.board_id = b.id\nwhere\n i.type in ($issue_type)\n and i.status != 'DONE'\n and b.id in ($board_id)", + "rawSql": "select\n count(*)\nfrom \n issues i\n\tjoin board_issues bi on i.id = bi.issue_id\n\tjoin boards b on bi.board_id = b.id\nwhere\n i.type in ($issue_type)\n and i.status != 'DONE'\n and b.id in ($board_id)", "refId": "A", "select": [ [ @@ -1001,7 +1001,7 @@ "metricColumn": "none", "queryType": "randomWalk", "rawQuery": true, - "rawSql": "select \n avg((TIMESTAMPDIFF(MINUTE, i.created_date,NOW()))/1440)\nfrom \n lake.issues i\n\tjoin board_issues bi on i.id = bi.issue_id\n\tjoin boards b on bi.board_id = b.id\nwhere\n i.type in ($issue_type)\n and i.status != 'DONE'\n and b.id in ($board_id)", + "rawSql": "select \n avg((TIMESTAMPDIFF(MINUTE, i.created_date,NOW()))/1440)\nfrom \n issues i\n\tjoin board_issues bi on i.id = bi.issue_id\n\tjoin boards b on bi.board_id = b.id\nwhere\n i.type in ($issue_type)\n and i.status != 'DONE'\n and b.id in ($board_id)", "refId": "A", "select": [ [ @@ -1168,7 +1168,7 @@ "metricColumn": "none", "queryType": "randomWalk", "rawQuery": true, - "rawSql": "select \n i.issue_key as 'Issue Number',\n i.title as 'Title',\n (TIMESTAMPDIFF(MINUTE, i.created_date,NOW()))/1440 as 'Queue Time in Days',\n i.url as 'Url',\n priority\nfrom \n lake.issues i\n\tjoin board_issues bi on i.id = bi.issue_id\n\tjoin boards b on bi.board_id = b.id\nwhere\n i.type in ($issue_type)\n and i.status != 'DONE'\n and b.id in ($board_id)\n and priority in ($priority)\norder by 'Queue Time' desc", + "rawSql": "select \n i.issue_key as 'Issue Number',\n i.title as 'Title',\n (TIMESTAMPDIFF(MINUTE, i.created_date,NOW()))/1440 as 'Queue Time in Days',\n i.url as 'Url',\n priority\nfrom \n issues i\n\tjoin board_issues bi on i.id = bi.issue_id\n\tjoin boards b on bi.board_id = b.id\nwhere\n i.type in ($issue_type)\n and i.status != 'DONE'\n and b.id in ($board_id)\n and priority in ($priority)\norder by 'Queue Time' desc", "refId": "A", "select": [ [ @@ -1289,7 +1289,7 @@ "metricColumn": "none", "queryType": "randomWalk", "rawQuery": true, - "rawSql": "select \n concat('#', i.issue_key) as issue_key,\n (TIMESTAMPDIFF(MINUTE, i.created_date,NOW()))/1440 as 'Queue Time in Days'\nfrom \n lake.issues i\n\tjoin board_issues bi on i.id = bi.issue_id\n\tjoin boards b on bi.board_id = b.id\nwhere\n i.type in ($issue_type)\n and i.status != 'DONE'\n and b.id in ($board_id)\norder by 2 desc", + "rawSql": "select \n concat('#', i.issue_key) as issue_key,\n (TIMESTAMPDIFF(MINUTE, i.created_date,NOW()))/1440 as 'Queue Time in Days'\nfrom \n issues i\n\tjoin board_issues bi on i.id = bi.issue_id\n\tjoin boards b on bi.board_id = b.id\nwhere\n i.type in ($issue_type)\n and i.status != 'DONE'\n and b.id in ($board_id)\norder by 2 desc", "refId": "A", "select": [ [ @@ -1433,7 +1433,7 @@ "metricColumn": "none", "queryType": "randomWalk", "rawQuery": true, - "rawSql": "select \n i.issue_key as 'Issue Number',\n i.title as 'Title',\n (TIMESTAMPDIFF(MINUTE, i.created_date,NOW()))/1440 as 'Queue Time in Days',\n i.url as 'Url'\nfrom \n lake.issues i\n\tjoin board_issues bi on i.id = bi.issue_id\n\tjoin boards b on bi.board_id = b.id\nwhere\n i.type in ($issue_type)\n and i.status != 'DONE'\n and i.assignee_name = ''\n and b.id in ($board_id)\norder by 'Queue Time' desc", + "rawSql": "select \n i.issue_key as 'Issue Number',\n i.title as 'Title',\n (TIMESTAMPDIFF(MINUTE, i.created_date,NOW()))/1440 as 'Queue Time in Days',\n i.url as 'Url'\nfrom \n issues i\n\tjoin board_issues bi on i.id = bi.issue_id\n\tjoin boards b on bi.board_id = b.id\nwhere\n i.type in ($issue_type)\n and i.status != 'DONE'\n and i.assignee_name = ''\n and b.id in ($board_id)\norder by 'Queue Time' desc", "refId": "A", "select": [ [ @@ -1550,7 +1550,7 @@ "metricColumn": "none", "queryType": "randomWalk", "rawQuery": true, - "rawSql": "with bugs as ( \n select \n DATE_ADD(date(i.created_date), INTERVAL -WEEKDAY(date(i.created_date)) DAY) as time,\n count(*) as bug_count\n from\n lake.issues as i\n\t join lake.board_issues bi on i.id = bi.issue_id\n\t join lake.boards b on bi.board_id = b.id\n where \n i.type in ($issue_type)\n and $__timeFilter(i.created_date)\n and b.id in ($board_id)\n group by time\n order by time desc\n),\n\ncalendar_date as(\n\tSELECT CAST((SYSDATE()- [...] + "rawSql": "with bugs as ( \n select \n DATE_ADD(date(i.created_date), INTERVAL -WEEKDAY(date(i.created_date)) DAY) as time,\n count(*) as bug_count\n from\n issues as i\n\t join board_issues bi on i.id = bi.issue_id\n\t join boards b on bi.board_id = b.id\n where \n i.type in ($issue_type)\n and $__timeFilter(i.created_date)\n and b.id in ($board_id)\n group by time\n order by time desc\n),\n\ncalendar_date as(\n\tSELECT CAST((SYSDATE()-INTERVAL (H+T+U [...] "refId": "A", "select": [ [ @@ -1667,7 +1667,7 @@ "metricColumn": "none", "queryType": "randomWalk", "rawQuery": true, - "rawSql": "with bugs as (\n select \n DATE_ADD(date(i.resolution_date), INTERVAL -WEEKDAY(date(i.resolution_date)) DAY) as time,\n count(*) as bug_count\n from\n lake.issues as i\n\t join lake.board_issues bi on i.id = bi.issue_id\n\t join lake.boards b on bi.board_id = b.id\n where \n i.type in ($issue_type)\n and status = 'DONE'\n and $__timeFilter(i.resolution_date)\n and b.id in ($board_id)\n group by time\n order by time desc\n),\n\ncalendar_dat [...] + "rawSql": "with bugs as (\n select \n DATE_ADD(date(i.resolution_date), INTERVAL -WEEKDAY(date(i.resolution_date)) DAY) as time,\n count(*) as bug_count\n from\n issues as i\n\t join board_issues bi on i.id = bi.issue_id\n\t join boards b on bi.board_id = b.id\n where \n i.type in ($issue_type)\n and status = 'DONE'\n and $__timeFilter(i.resolution_date)\n and b.id in ($board_id)\n group by time\n order by time desc\n),\n\ncalendar_date as(\n\tSELECT [...] "refId": "A", "select": [ [ @@ -1786,7 +1786,7 @@ "metricColumn": "none", "queryType": "randomWalk", "rawQuery": true, - "rawSql": "with calendar_date as(\n\tSELECT CAST((SYSDATE()-INTERVAL (H+T+U) DAY) AS date) d\n\tFROM ( SELECT 0 H\n\t\t\tUNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL SELECT 300\n\t\t) H CROSS JOIN ( SELECT 0 T\n\t\t\tUNION ALL SELECT 10 UNION ALL SELECT 20 UNION ALL SELECT 30\n\t\t\tUNION ALL SELECT 40 UNION ALL SELECT 50 UNION ALL SELECT 60\n\t\t\tUNION ALL SELECT 70 UNION ALL SELECT 80 UNION ALL SELECT 90\n\t\t) T CROSS JOIN ( SELECT 0 U\n\t\t\tUNION ALL SELEC [...] + "rawSql": "with calendar_date as(\n\tSELECT CAST((SYSDATE()-INTERVAL (H+T+U) DAY) AS date) d\n\tFROM ( SELECT 0 H\n\t\t\tUNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL SELECT 300\n\t\t) H CROSS JOIN ( SELECT 0 T\n\t\t\tUNION ALL SELECT 10 UNION ALL SELECT 20 UNION ALL SELECT 30\n\t\t\tUNION ALL SELECT 40 UNION ALL SELECT 50 UNION ALL SELECT 60\n\t\t\tUNION ALL SELECT 70 UNION ALL SELECT 80 UNION ALL SELECT 90\n\t\t) T CROSS JOIN ( SELECT 0 U\n\t\t\tUNION ALL SELEC [...] "refId": "A", "select": [ [ diff --git a/grafana/dashboards/WeeklyCommunityRetro.json b/grafana/dashboards/WeeklyCommunityRetro.json index 539fbaee1..cd232b5d7 100644 --- a/grafana/dashboards/WeeklyCommunityRetro.json +++ b/grafana/dashboards/WeeklyCommunityRetro.json @@ -144,7 +144,7 @@ "metricColumn": "none", "queryType": "randomWalk", "rawQuery": true, - "rawSql": "select\n count(*)\nfrom\n lake.issues as i\n join lake.board_issues bi on i.id = bi.issue_id\n join boards b on bi.board_id = b.id\nwhere\n i.type in ($issue_type)\n and date(i.created_date) BETWEEN curdate() - INTERVAL WEEKDAY(curdate())+7 DAY AND curdate() - INTERVAL WEEKDAY(curdate())+1 DAY\n and b.id in ($repo_id)", + "rawSql": "select\n count(*)\nfrom\n issues as i\n join board_issues bi on i.id = bi.issue_id\n join boards b on bi.board_id = b.id\nwhere\n i.type in ($issue_type)\n and date(i.created_date) BETWEEN curdate() - INTERVAL WEEKDAY(curdate())+7 DAY AND curdate() - INTERVAL WEEKDAY(curdate())+1 DAY\n and b.id in ($repo_id)", "refId": "A", "select": [ [ @@ -232,7 +232,7 @@ "metricColumn": "none", "queryType": "randomWalk", "rawQuery": true, - "rawSql": "select\n count(*)\nfrom\n lake.issues as i\n join lake.board_issues bi on i.id = bi.issue_id\n join boards b on bi.board_id = b.id\nwhere\n i.type in ($issue_type)\n and date(i.created_date) BETWEEN curdate() - INTERVAL WEEKDAY(curdate())+7 DAY AND curdate() - INTERVAL WEEKDAY(curdate())+1 DAY\n and b.id in ($repo_id)\n and i.creator_id not in (select distinct id from accounts where organization in ($org))", + "rawSql": "select\n count(*)\nfrom\n issues as i\n join board_issues bi on i.id = bi.issue_id\n join boards b on bi.board_id = b.id\nwhere\n i.type in ($issue_type)\n and date(i.created_date) BETWEEN curdate() - INTERVAL WEEKDAY(curdate())+7 DAY AND curdate() - INTERVAL WEEKDAY(curdate())+1 DAY\n and b.id in ($repo_id)\n and i.creator_id not in (select distinct id from accounts where organization in ($org))", "refId": "A", "select": [ [ @@ -321,7 +321,7 @@ "metricColumn": "none", "queryType": "randomWalk", "rawQuery": true, - "rawSql": "select\n sum(case when i.creator_id not in (select distinct id from accounts where organization in ($org)\n ) then 1 else 0 end)/count(*) as community_issue_ratio\nfrom\n lake.issues as i\n join lake.board_issues bi on i.id = bi.issue_id\n join boards b on bi.board_id = b.id\nwhere\n i.type in ($issue_type)\n and date(i.created_date) BETWEEN curdate() - INTERVAL WEEKDAY(curdate())+7 DAY AND curdate() - INTERVAL WEEKDAY(curdate())+1 DAY\n and b.id in ($repo_id)", + "rawSql": "select\n sum(case when i.creator_id not in (select distinct id from accounts where organization in ($org)\n ) then 1 else 0 end)/count(*) as community_issue_ratio\nfrom\n issues as i\n join board_issues bi on i.id = bi.issue_id\n join boards b on bi.board_id = b.id\nwhere\n i.type in ($issue_type)\n and date(i.created_date) BETWEEN curdate() - INTERVAL WEEKDAY(curdate())+7 DAY AND curdate() - INTERVAL WEEKDAY(curdate())+1 DAY\n and b.id in ($repo_id)", "refId": "A", "select": [ [ @@ -413,7 +413,7 @@ "metricColumn": "none", "queryType": "randomWalk", "rawQuery": true, - "rawSql": "select\n distinct i.creator_name\nfrom\n lake.issues as i\n join lake.board_issues bi on i.id = bi.issue_id\n join boards b on bi.board_id = b.id\nwhere\n i.type in ($issue_type)\n and date(i.created_date) BETWEEN curdate() - INTERVAL WEEKDAY(curdate())+7 DAY AND curdate() - INTERVAL WEEKDAY(curdate())+1 DAY\n and b.id in ($repo_id)\n and i.creator_name not in (select distinct creator_name from issues where created_date < curdate() - INTERVAL WEEKDAY(curdate( [...] + "rawSql": "select\n distinct i.creator_name\nfrom\n issues as i\n join board_issues bi on i.id = bi.issue_id\n join boards b on bi.board_id = b.id\nwhere\n i.type in ($issue_type)\n and date(i.created_date) BETWEEN curdate() - INTERVAL WEEKDAY(curdate())+7 DAY AND curdate() - INTERVAL WEEKDAY(curdate())+1 DAY\n and b.id in ($repo_id)\n and i.creator_name not in (select distinct creator_name from issues where created_date < curdate() - INTERVAL WEEKDAY(curdate())+7 DAY a [...] "refId": "A", "select": [ [
