charts_potential_duplicates (view)

Data license: CC-BY

105 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Next page

CREATE VIEW charts_potential_duplicates AS WITH chart_list AS (
    SELECT chartId,
        group_concat(variableId) AS variables
    FROM chart_variables
    WHERE chartId in (
            SELECT id
            FROM charts
            WHERE json_extract(config, "$.isPublished")
                AND json_extract(config, "$.logo") IS NULL
        )
    GROUP BY chartId
),
charts_per_variable AS (
    SELECT variables,
        group_concat(chartId, '%29+%28%3D+charts.id+') AS chart_ids
    FROM chart_list
    GROUP BY variables
    HAVING COUNT(*) > 1
    ORDER BY variables
)
SELECT 'https://owid.cloud/admin/bulk-grapher-config-editor?filter=%28OR+%28%3D+charts.id+' || chart_ids || '%29%29' AS compare_link,
    variables
FROM charts_per_variable;