venkatamandavilli-code commented on issue #32496:
URL: https://github.com/apache/superset/issues/32496#issuecomment-4157076311
Try this SQL Template
This template assumes you have three inputs:
-Stories Delivered per Sprint
- Bugs Opened per Sprint**, split by priority
- Efficiency** (Stories / Total Bugs)
- Trend Line** (linear regression on P1 bugs)
You can plug in your own tables or CTEs.
```sql
WITH stories AS (
SELECT
sprint_id AS x,
COUNT(*) AS stories_delivered
FROM user_stories
GROUP BY sprint_id
),
bugs AS (
SELECT
sprint_id AS x,
SUM(CASE WHEN priority = 'P1' THEN 1 ELSE 0 END) AS bugs_p1,
SUM(CASE WHEN priority IN ('P2','P3') THEN 1 ELSE 0 END) AS bugs_p23
FROM bugs_opened
GROUP BY sprint_id
),
combined AS (
SELECT
s.x,
s.stories_delivered,
b.bugs_p1,
b.bugs_p23,
(s.stories_delivered * 1.0) / NULLIF((b.bugs_p1 + b.bugs_p23), 0) AS
efficiency
FROM stories s
LEFT JOIN bugs b ON s.x = b.x
),
trend AS (
-- Linear regression for P1 bugs
SELECT
x,
REGR_SLOPE(bugs_p1, sprint_index) OVER () * sprint_index +
REGR_INTERCEPT(bugs_p1, sprint_index) OVER () AS p1_trend
FROM (
SELECT
x,
bugs_p1,
ROW_NUMBER() OVER (ORDER BY x) AS sprint_index
FROM combined
)
)
-- Final long-form dataset
SELECT x, 'Stories Delivered' AS metric_name, stories_delivered AS
metric_value,
'Stories' AS metric_group, NULL AS metric_stack, 'bar' AS
metric_type, 'primary' AS y_axis
FROM combined
UNION ALL
SELECT x, 'Bugs P1', bugs_p1, 'Bugs', 'P1', 'bar', 'primary'
FROM combined
UNION ALL
SELECT x, 'Bugs P2/3', bugs_p23, 'Bugs', 'P2/3', 'bar', 'primary'
FROM combined
UNION ALL
SELECT x, 'Efficiency', efficiency, 'Efficiency', NULL, 'line', 'secondary'
FROM combined
UNION ALL
SELECT x, 'P1 Trend', p1_trend, 'Bugs', 'P1', 'trend', 'primary'
FROM trend
ORDER BY x, metric_name;
```
ClickHouse‑Optimized Version**
ClickHouse doesn’t support REGR_SLOPE/REGR_INTERCEPT, so we compute linear
regression manually.
```sql
WITH base AS (
SELECT
sprint_id AS x,
COUNTIf(type = 'story') AS stories_delivered,
COUNTIf(type = 'bug' AND priority = 'P1') AS bugs_p1,
COUNTIf(type = 'bug' AND priority IN ('P2','P3')) AS bugs_p23
FROM sprint_events
GROUP BY sprint_id
),
calc AS (
SELECT
x,
stories_delivered,
bugs_p1,
bugs_p23,
stories_delivered / NULLIF((bugs_p1 + bugs_p23), 0) AS efficiency,
row_number() OVER (ORDER BY x) AS idx
FROM base
),
stats AS (
SELECT
count() AS n,
sum(idx) AS sum_x,
sum(bugs_p1) AS sum_y,
sum(idx * bugs_p1) AS sum_xy,
sum(idx * idx) AS sum_xx
FROM calc
),
trend AS (
SELECT
c.x,
(
(n * sum_xy - sum_x * sum_y) / (n * sum_xx - sum_x * sum_x)
) * c.idx
+
(
(sum_y - ((n * sum_xy - sum_x * sum_y) / (n * sum_xx - sum_x *
sum_x)) * sum_x) / n
) AS p1_trend
FROM calc c
CROSS JOIN stats
)
-- Final long-form dataset
SELECT x, 'Stories Delivered' AS metric_name, stories_delivered AS
metric_value,
'Stories' AS metric_group, NULL AS metric_stack, 'bar' AS
metric_type, 'primary' AS y_axis
FROM calc
UNION ALL
SELECT x, 'Bugs P1', bugs_p1, 'Bugs', 'P1', 'bar', 'primary'
FROM calc
UNION ALL
SELECT x, 'Bugs P2/3', bugs_p23, 'Bugs', 'P2/3', 'bar', 'primary'
FROM calc
UNION ALL
SELECT x, 'Efficiency', efficiency, 'Efficiency', NULL, 'line', 'secondary'
FROM calc
UNION ALL
SELECT x, 'P1 Trend', p1_trend, 'Bugs', 'P1', 'trend', 'primary'
FROM trend
ORDER BY x, metric_name;
```
What This SQL Produces
A long‑form dataset with:
- Grouped bars (Stories vs Bugs)
- Stacked bars (P1 + P2/3)
- Line series (Efficiency)
- Trend line (P1 Bugs)
- Dual axes
- Clean metric metadata for Superset
Exactly the structure needed for the new chart type.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
