SELECT /* SLOW_OK */ DATE_FORMAT(DATE_ADD(img_timestamp, INTERVAL 2 HOUR),'%Y') AS date, COUNT(image.img_name) as images

    FROM

      image

      JOIN page ON image.img_name = page.page_title

      JOIN categorylinks ON page.page_id = categorylinks.cl_from

    WHERE

        categorylinks.cl_to = "Uploaded_via_Campaign:WikiDaheim-at-wd"

        AND img_timestamp BETWEEN '20220630220000' AND '20231008220000' 

    GROUP BY date desc;

# -----

WITH date_intervals AS (

    SELECT

        YEAR(img_timestamp) AS year,

        #COUNT(CASE WHEN img_timestamp BETWEEN CONCAT(YEAR(img_timestamp), '0701000000') AND CONCAT(YEAR(img_timestamp), '0831000000') THEN 1 END) AS wikiDaheim,

        #COUNT(CASE WHEN img_timestamp < CONCAT(YEAR(img_timestamp), '0701000000') OR img_timestamp > CONCAT(YEAR(img_timestamp), '0831000000') THEN 1 END) AS outside_wikiDaheim

        #COUNT(CASE WHEN img_timestamp BETWEEN '20230630220000' AND '20231008220000' THEN 1 END) AS wikiDaheim,

        #COUNT(CASE WHEN img_timestamp < '20230630220000' OR img_timestamp > '20231008220000' THEN 1 END) AS outside_wikiDaheim,

        COUNT(CASE WHEN

                            (img_timestamp BETWEEN '20240630220000' AND '20241008220000')

                         OR (img_timestamp BETWEEN '20230630220000' AND '20231008220000')

                         OR (img_timestamp BETWEEN '20220630220000' AND '20221008220000')

                         OR (img_timestamp BETWEEN '20210630220000' AND '20211008220000')

                         OR (img_timestamp BETWEEN '20200630220000' AND '20201008220000')

                         OR (img_timestamp BETWEEN '20190630220000' AND '20191008220000')

                   THEN 1 END) AS wikiDaheim,

        COUNT(CASE WHEN

                            (YEAR(img_timestamp)=2024 and (img_timestamp < '20240630220000' OR img_timestamp > '20241008220000'))

                         OR (YEAR(img_timestamp)=2023 and (img_timestamp < '20230630220000' OR img_timestamp > '20231008220000'))

                         OR (YEAR(img_timestamp)=2022 and (img_timestamp < '20220630220000' OR img_timestamp > '20221008220000'))

                         OR (YEAR(img_timestamp)=2021 and (img_timestamp < '20210630220000' OR img_timestamp > '20211008220000'))

                         OR (YEAR(img_timestamp)=2020 and (img_timestamp < '20200630220000' OR img_timestamp > '20201008220000'))

                         OR (YEAR(img_timestamp)=2029 and (img_timestamp < '20190630220000' OR img_timestamp > '20191008220000'))

                    THEN 1 END) AS outside_wikiDaheim

    FROM

        image

    JOIN

        page ON image.img_name = page.page_title

    JOIN

        categorylinks ON page.page_id = categorylinks.cl_from

    WHERE

        categorylinks.cl_to = 'Uploaded_via_Campaign:WikiDaheim-at-wd'

        #AND img_timestamp BETWEEN '20220630220000' AND '20231008220000'

    GROUP BY

        year

)

SELECT

    year,

    wikiDaheim,

    outside_wikiDaheim

FROM

    date_intervals

ORDER BY

    year DESC;

​

#-----

WITH intervals AS (

    SELECT 1970 AS year, '19700630220000' AS start_date, '19701008220000' AS end_date

    UNION ALL

    SELECT 2019, '20190630220000', '20191008220000'

    UNION ALL

    SELECT 2020, '20200630220000', '20201008220000'

    UNION ALL

    SELECT 2021, '20210630220000', '20211008220000'

    UNION ALL

    SELECT 2022, '20220630220000', '20221008220000'

    UNION ALL

    SELECT 2023, '20230630220000', '20231008220000'

    UNION ALL

    SELECT 2024, '20240630220000', '20241008220000'

),

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

    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 = 'Uploaded_via_Campaign:WikiDaheim-at-wd'

    GROUP BY

        year

)

SELECT

    year,

    wikiDaheim,

    outside_wikiDaheim

FROM

    date_intervals

ORDER BY

    year DESC;

​