WITH intervals AS (

    SELECT 1970 AS year, '19700630220000' AS start_date, '19701008220000' AS end_date # dummy to define cols

    UNION ALL

    SELECT 2017, '20170630220000', '20171007220000'

    UNION ALL

    SELECT 2018, '20180630220000', '20181007220000'

    UNION ALL

    SELECT 2019, '20190630220000', '20191006220000'

    UNION ALL

    SELECT 2020, '20200630220000', '20201004220000'

    UNION ALL

    SELECT 2021, '20210630220000', '20211003220000'

    UNION ALL

    SELECT 2022, '20220630220000', '20221002220000'

    UNION ALL

    SELECT 2023, '20230630220000', '20231008220000'

    UNION ALL

    SELECT 2024, '20240630220000', '20241006220000'

    UNION ALL

    SELECT 2025, '20241231230000', '20251005220000'

),

date_intervals AS (

    SELECT

        YEAR(img_timestamp) AS year,

        COUNT(CASE WHEN img_timestamp BETWEEN intervals.start_date AND intervals.end_date THEN 1 END) AS wikiDaheim,

        COUNT(CASE WHEN img_timestamp < intervals.start_date OR img_timestamp > intervals.end_date THEN 1 END) AS outside_wikiDaheim

        ,categorylinks.cl_to as cat

    FROM

        image

    JOIN

        page ON image.img_name = page.page_title

    JOIN

        categorylinks ON page.page_id = categorylinks.cl_from

    JOIN

        intervals ON YEAR(img_timestamp) = intervals.year

    WHERE

  categorylinks.cl_to in (

    'Uploaded_via_Campaign:kellergasse-at',

    'Uploaded_via_Campaign:Schallaburg2024-at',

    'Uploaded_via_Campaign:WikiDaheim-at',

    'Uploaded_via_Campaign:WikiDaheim-at-bw',

    'Uploaded_via_Campaign:WikiDaheim-at-cemeteries',

    'Uploaded_via_Campaign:WikiDaheim-at-commons',

    'Uploaded_via_Campaign:WikiDaheim-at-unesco',

    'Uploaded_via_Campaign:WikiDaheim-at-wd',

    'Uploaded_via_Campaign:wle-at-glt',

    'Uploaded_via_Campaign:wle-at-hoe',

    'Uploaded_via_Campaign:wle-at-nap',

    'Uploaded_via_Campaign:wle-at-nd',

    'Uploaded_via_Campaign:wle-at-np',

    'Uploaded_via_Campaign:wle-at-nsg',

    'Uploaded_via_Campaign:wlm-at',

    'Uploaded_via_Campaign:wlm-rail-at',

    'Uploaded_via_Campaign:wlpa-at-2',

    'Uploaded_via_Campaign:wlpa-at-3',

    'Uploaded_via_Campaign:wlpa-at-9',

    'Uploaded_via_Campaign:wlpa-at-9-KÖR',

    'Uploaded_via_Campaign:wlpa-at-401',

    'Uploaded_via_Campaign:wlpa-at-601',

    'Uploaded_via_Campaign:wltd-at'

  )

    GROUP BY

        year,

        cat

)

SELECT

    year,

    wikiDaheim,

    outside_wikiDaheim

    ,cat

FROM

    date_intervals

ORDER BY

    year DESC;

​