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 2022 AS year, '2022-07-01 00:00:00' AS start_date, '2022-08-31 23:59:59' AS end_date

    UNION ALL

    SELECT 2023, '2023-07-01 00:00:00', '2023-08-31 23:59:59'

    UNION ALL

    SELECT 2024, '2024-07-01 00:00:00', '2024-08-31 23:59:59'

),

date_intervals AS (

    SELECT

        YEAR(STR_TO_DATE(img_timestamp, '%Y%m%d%H%i%s')) AS year,

        COUNT(CASE WHEN STR_TO_DATE(img_timestamp, '%Y%m%d%H%i%s') BETWEEN intervals.start_date AND intervals.end_date THEN 1 END) AS interval_count,

        COUNT(CASE WHEN STR_TO_DATE(img_timestamp, '%Y%m%d%H%i%s') < intervals.start_date OR STR_TO_DATE(img_timestamp, '%Y%m%d%H%i%s') > intervals.end_date THEN 1 END) AS outside_interval_count

    FROM

        image

    JOIN

        page ON image.img_name = page.page_title

    JOIN

        categorylinks ON page.page_id = categorylinks.cl_from

    JOIN

        intervals ON YEAR(STR_TO_DATE(img_timestamp, '%Y%m%d%H%i%s')) = intervals.year

    WHERE

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

        #AND STR_TO_DATE(img_timestamp, '%Y%m%d%H%i%s') BETWEEN '2022-06-30 22:00:00' AND '2023-10-08 22:00:00'

    GROUP BY

        year

)

SELECT

    year,

    interval_count,

    outside_interval_count

FROM

    date_intervals

ORDER BY

    year DESC;

​