SQL / Python code to transform input data in to Output Rank Metrics
Based on
- Affiliate Leadership (Percentile)
- Grants Leadership $ weighted by PPP + triangulating counts (Percentile)
- EDITORSHIP (CORRECTED RANK)
- Readership Percentile
SQL / Python code to transform input data in to Output Rank Metrics
Based on
| Status | Subtype | Assigned | Task | ||
|---|---|---|---|---|---|
| Open | None | T305468 Deploy the GDI Equity Landscape Dashboard | |||
| Open | None | T305475 Milestone: Ingest and Transform Input Data | |||
| Resolved | • ntsako | T306622 Overall Engagement output rank metric |
@JAnstee_WMF The QA of overall engagement metric is ready for your review: https://docs.google.com/spreadsheets/d/1GnKHC9yT5tN_xmEltCGdHEONI5GqjTiWaVI9zXNp4rQ/edit#gid=1551753866&range=E2
WITH
inputs AS (
SELECT * FROM readership_output_ranks WHERE metric = 'readership'
UNION ALL
SELECT * FROM editorship_output_ranks WHERE metric = 'editorship'
-- UNION ALL
-- SELECT * FROM programs_output_ranks WHERE metric = 'programs_leadership'
UNION ALL
SELECT * FROM affiliates_output_ranks WHERE metric = 'affiliates_leadership'
UNION ALL
SELECT * FROM grants_output_ranks WHERE metric = 'grants_leadership')
SELECT
geography_level,
country_code,
ROUND(
PERCENT_RANK() OVER (
PARTITION BY geography_level
ORDER BY AVG(metric_value))*10, 2) AS metric_value,
'overall_engagement' AS metric
FROM
inputs
GROUP BY
geography_level,
country_code