API tools faq
paste
Advertisement
Guest User

Untitled

a guest
May 24th, 2019
87
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 8.70 KB | None | 0 0
raw download clone embed print report
  1. diff --git a/ss_dbt/models/platform/github_metrics.sql b/ss_dbt/models/platform/github_metrics.sql
  2. index 0d80597..0135570 100644
  3. --- a/ss_dbt/models/platform/github_metrics.sql
  4. +++ b/ss_dbt/models/platform/github_metrics.sql
  5. @@ -11,8 +11,8 @@ SELECT
  6. coalesce(issues_closed, 0) AS issues_closed,
  7. coalesce(bugs_created, 0) AS bugs_created,
  8. coalesce(bugs_closed, 0) AS bugs_closed,
  9. - coalesce(bugs_created, 0) AS enhancements_created,
  10. - coalesce(bugs_closed, 0) AS enhancements_closed,
  11. + coalesce(enhancements_created, 0) AS enhancements_created,
  12. + coalesce(enhancements_closed, 0) AS enhancements_closed,
  13. coalesce(docissues_created, 0) AS docissues_created,
  14. coalesce(docissues_closed, 0) AS docissues_closed,
  15. coalesce(notype_created, 0) AS notype_created,
  16. @@ -22,92 +22,41 @@ SELECT
  17. coalesce(prs_closed, 0) AS prs_closed,
  18.  
  19. -- Backlog sizes at end of week
  20. - (
  21. - SELECT COUNT(*)
  22. - FROM {{ ref('github_issues') }}
  23. - WHERE created_at < weeks.weeks + INTERVAL '7 day' AND (closed_at IS NULL OR closed_at > weeks.weeks + INTERVAL '7 day')
  24. - AND repository = repos.repository
  25. - AND NOT is_pr
  26. - ) issues_total_open,
  27. - (
  28. - SELECT COUNT(*)
  29. - FROM {{ ref('github_issues') }}
  30. - WHERE closed_at < weeks.weeks + INTERVAL '7 day'
  31. - AND repository = repos.repository
  32. - AND NOT is_pr
  33. - ) issues_total_closed,
  34. - (
  35. - SELECT COUNT(*)
  36. - FROM github_data.issues i INNER JOIN github_data.issues__labels il ON il._sdc_source_key_id = i.id AND il.name = 'type/bug'
  37. - WHERE created_at < weeks.weeks + INTERVAL '7 day' AND (closed_at IS NULL OR closed_at > weeks.weeks + INTERVAL '7 day')
  38. - AND _sdc_repository = repos.repository
  39. - AND pull_request__url IS NULL
  40. - ) bugs_total_open,
  41. - (
  42. - SELECT COUNT(*)
  43. - FROM github_data.issues i INNER JOIN github_data.issues__labels il ON il._sdc_source_key_id = i.id AND il.name = 'type/bug'
  44. - WHERE closed_at < weeks.weeks + INTERVAL '7 day'
  45. - AND _sdc_repository = repos.repository
  46. - AND pull_request__url IS NULL
  47. - ) bugs_total_closed,
  48. - (
  49. - SELECT COUNT(*)
  50. - FROM github_data.issues i INNER JOIN github_data.issues__labels il ON il._sdc_source_key_id = i.id AND il.name IN ('type/enhancement', 'feature', 'enhancement')
  51. - WHERE created_at < weeks.weeks + INTERVAL '7 day' AND (closed_at IS NULL OR closed_at > weeks.weeks + INTERVAL '7 day')
  52. - AND _sdc_repository = repos.repository
  53. - AND pull_request__url IS NULL
  54. - ) enhancements_total_open,
  55. - (
  56. - SELECT COUNT(*)
  57. - FROM github_data.issues i INNER JOIN github_data.issues__labels il ON il._sdc_source_key_id = i.id AND il.name IN ('type/enhancement', 'feature', 'enhancement')
  58. - WHERE closed_at < weeks.weeks + INTERVAL '7 day'
  59. - AND _sdc_repository = repos.repository
  60. - AND pull_request__url IS NULL
  61. - ) enhancements_total_closed,
  62. - (
  63. - SELECT COUNT(*)
  64. - FROM github_data.issues i INNER JOIN github_data.issues__labels il ON il._sdc_source_key_id = i.id AND il.name = 'type/docs'
  65. - WHERE created_at < weeks.weeks + INTERVAL '7 day' AND (closed_at IS NULL OR closed_at > weeks.weeks + INTERVAL '7 day')
  66. - AND _sdc_repository = repos.repository
  67. - AND pull_request__url IS NULL
  68. - ) docissues_total_open,
  69. - (
  70. - SELECT COUNT(*)
  71. - FROM github_data.issues i INNER JOIN github_data.issues__labels il ON il._sdc_source_key_id = i.id AND il.name = 'type/docs'
  72. - WHERE closed_at < weeks.weeks + INTERVAL '7 day'
  73. - AND _sdc_repository = repos.repository
  74. - AND pull_request__url IS NULL
  75. - ) docissues_total_closed,
  76. - (
  77. - SELECT COUNT(*)
  78. - FROM {{ ref('github_issues') }}
  79. - WHERE created_at < weeks.weeks + INTERVAL '7 day' AND (closed_at IS NULL OR closed_at > weeks.weeks + INTERVAL '7 day')
  80. - AND repository = repos.repository
  81. - AND NOT is_pr
  82. - AND type IS NULL
  83. - ) notype_total_open,
  84. - (
  85. - SELECT COUNT(*)
  86. - FROM {{ ref('github_issues') }}
  87. - WHERE closed_at < weeks.weeks + INTERVAL '7 day'
  88. - AND repository = repos.repository
  89. - AND NOT is_pr
  90. - AND type IS NULL
  91. - ) notype_total_closed,
  92. - (
  93. - SELECT COUNT(*)
  94. - FROM {{ ref('github_issues') }}
  95. - WHERE created_at < weeks.weeks + INTERVAL '7 day' AND (closed_at IS NULL OR closed_at > weeks.weeks + INTERVAL '7 day')
  96. - AND repository = repos.repository
  97. - AND is_pr
  98. - ) prs_total_open,
  99. - (
  100. - SELECT COUNT(*)
  101. - FROM {{ ref('github_issues') }}
  102. - WHERE closed_at < weeks.weeks + INTERVAL '7 day'
  103. - AND repository = repos.repository
  104. - AND is_pr
  105. - ) prs_total_closed,
  106. + SUM(issues_created) OVER (PARTITION BY created.repository ORDER BY created.week ASC ROWS BETWEEN unbounded preceding AND CURRENT row)
  107. + - COALESCE(SUM(issues_closed) OVER (PARTITION BY closed.repository ORDER BY closed.week ASC ROWS BETWEEN unbounded preceding AND CURRENT row), 0)
  108. + AS issues_total_open,
  109. + COALESCE(SUM(issues_closed) OVER (PARTITION BY closed.repository ORDER BY closed.week ASC ROWS BETWEEN unbounded preceding AND CURRENT row), 0)
  110. + AS issues_total_closed,
  111. +
  112. + SUM(bugs_created) OVER (PARTITION BY created.repository ORDER BY created.week ASC ROWS BETWEEN unbounded preceding AND CURRENT row)
  113. + - COALESCE(SUM(bugs_closed) OVER (PARTITION BY closed.repository ORDER BY closed.week ASC ROWS BETWEEN unbounded preceding AND CURRENT row), 0)
  114. + AS bugs_total_open,
  115. + COALESCE(SUM(bugs_closed) OVER (PARTITION BY closed.repository ORDER BY closed.week ASC ROWS BETWEEN unbounded preceding AND CURRENT row), 0)
  116. + AS bugs_total_closed,
  117. +
  118. + SUM(enhancements_created) OVER (PARTITION BY created.repository ORDER BY created.week ASC ROWS BETWEEN unbounded preceding AND CURRENT row)
  119. + - COALESCE(SUM(enhancements_closed) OVER (PARTITION BY closed.repository ORDER BY closed.week ASC ROWS BETWEEN unbounded preceding AND CURRENT row), 0)
  120. + AS enhancements_total_open,
  121. + COALESCE(SUM(enhancements_closed) OVER (PARTITION BY closed.repository ORDER BY closed.week ASC ROWS BETWEEN unbounded preceding AND CURRENT row), 0)
  122. + AS enhancements_total_closed,
  123. +
  124. + SUM(docissues_created) OVER (PARTITION BY created.repository ORDER BY created.week ASC ROWS BETWEEN unbounded preceding AND CURRENT row)
  125. + - COALESCE(SUM(docissues_closed) OVER (PARTITION BY closed.repository ORDER BY closed.week ASC ROWS BETWEEN unbounded preceding AND CURRENT row), 0)
  126. + AS docissues_total_open,
  127. + COALESCE(SUM(docissues_closed) OVER (PARTITION BY closed.repository ORDER BY closed.week ASC ROWS BETWEEN unbounded preceding AND CURRENT row), 0)
  128. + AS docissues_total_closed,
  129. +
  130. + SUM(notype_created) OVER (PARTITION BY created.repository ORDER BY created.week ASC ROWS BETWEEN unbounded preceding AND CURRENT row)
  131. + - COALESCE(SUM(notype_closed) OVER (PARTITION BY closed.repository ORDER BY closed.week ASC ROWS BETWEEN unbounded preceding AND CURRENT row), 0)
  132. + AS notype_total_open,
  133. + COALESCE(SUM(notype_closed) OVER (PARTITION BY closed.repository ORDER BY closed.week ASC ROWS BETWEEN unbounded preceding AND CURRENT row), 0)
  134. + AS notype_total_closed,
  135. +
  136. + SUM(prs_created) OVER (PARTITION BY created.repository ORDER BY created.week ASC ROWS BETWEEN unbounded preceding AND CURRENT row)
  137. + - COALESCE(SUM(prs_closed) OVER (PARTITION BY closed.repository ORDER BY closed.week ASC ROWS BETWEEN unbounded preceding AND CURRENT row), 0)
  138. + AS prs_total_open,
  139. + COALESCE(SUM(prs_closed) OVER (PARTITION BY closed.repository ORDER BY closed.week ASC ROWS BETWEEN unbounded preceding AND CURRENT row), 0)
  140. + AS prs_total_closed,
  141.  
  142. gmd.total_triaged,
  143. gmd.total_untriaged,
  144. @@ -127,7 +76,7 @@ LEFT JOIN (
  145. count(*) FILTER (WHERE NOT is_pr) AS issues_created,
  146. count(*) FILTER (WHERE is_pr) AS prs_created,
  147. count(*) FILTER (WHERE NOT is_pr AND type = 'bug') AS bugs_created,
  148. - count(*) FILTER (WHERE NOT is_pr AND type = 'enhancement') AS enhancments_created,
  149. + count(*) FILTER (WHERE NOT is_pr AND type = 'enhancement') AS enhancements_created,
  150. count(*) FILTER (WHERE NOT is_pr AND type = 'docs') AS docissues_created,
  151. count(*) FILTER (WHERE NOT is_pr AND type IS NULL) AS notype_created
  152. FROM {{ ref('github_issues') }}
  153. @@ -140,7 +89,7 @@ LEFT JOIN (
  154. count(*) FILTER (WHERE NOT is_pr) AS issues_closed,
  155. count(*) FILTER (WHERE is_pr) AS prs_closed,
  156. count(*) FILTER (WHERE NOT is_pr AND type = 'bug') AS bugs_closed,
  157. - count(*) FILTER (WHERE NOT is_pr AND type = 'enhancement') AS enhancments_closed,
  158. + count(*) FILTER (WHERE NOT is_pr AND type = 'enhancement') AS enhancements_closed,
  159. count(*) FILTER (WHERE NOT is_pr AND type = 'docs') AS docissues_closed,
  160. count(*) FILTER (WHERE NOT is_pr AND type IS NULL) AS notype_closed
  161. FROM {{ ref('github_issues') }}
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!