charts_broken_origin_url (view)

Data license: CC-BY

191 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Next page

CREATE VIEW charts_broken_origin_url AS WITH chartOriginUrl AS (
    SELECT id AS chartId,
        slug AS chartSlug,
        "https://owid.cloud/admin/charts/" || id || "/edit" AS chartEditLink,
        JSON_EXTRACT(config, "$.originUrl") AS originUrlAsAuthored,
        trim(
            regexp_match(
                '^.*[Oo]ur[Ww]orld[Ii]n[Dd]ata.org/(.+)$',
                JSON_EXTRACT(config, "$.originUrl")
            ),
            '/'
        ) AS originUrlPostSlug,
        JSON_EXTRACT(config, "$.isPublished") as isPublished
    FROM charts
    WHERE originUrlAsAuthored IS NOT NULL
        AND originUrlAsAuthored IS NOT ""
        AND originUrlAsAuthored not like "%tinyco.re%"
        AND originUrlAsAuthored not like "%core-econ%"
)
SELECT c.chartId,
    c.chartSlug,
    c.chartEditLink,
    c.originUrlAsAuthored,
    c.originUrlPostSlug,
    CASE
        WHEN c.originUrlPostSlug IS NOT NULL THEN "https://ourworldindata.org/" || c.originUrlPostSlug
        ELSE NULL
    END AS originUrlPostLink,
    CASE
        WHEN c.isPublished IS NOT NULL THEN TRUE
        ELSE FALSE
    END AS isPublished
FROM chartOriginUrl c
    LEFT JOIN posts p ON p.slug = c.originUrlPostSlug
WHERE p.id IS NULL;