SELECT /* SLOW_OK */ DISTINCT actor_name AS user, COUNT(image.img_name) as img_count 

    FROM actor, revision, image, page, categorylinks

    WHERE page.page_id=categorylinks.cl_from

        AND actor.actor_id = revision.rev_actor

        AND image.img_name = page.page_title

        and revision.rev_page = page.page_id

        and revision.rev_parent_id = 0

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

        #AND categorylinks.cl_to = "Prejury_selection_for_WikiDaheim_2023_in_Austria" 

    GROUP BY user 

    ORDER BY img_count DESC limit 300;

​

SELECT /* SLOW_OK */ DISTINCT actor_name AS user, COUNT(image.img_name) as img_count 

    FROM

        image

        JOIN page AS page_img ON image.img_name = page_img.page_title

        JOIN categorylinks ON page_img.page_id = categorylinks.cl_from

        JOIN revision ON revision.rev_page = page_img.page_id

        JOIN actor ON actor.actor_id = revision.rev_actor

    where

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

        and revision.rev_parent_id = 0

    GROUP BY user 

    ORDER BY img_count DESC limit 300;

​

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;

 # -----------------

 CREATE TABLE intervals (

    year INT,

    start_date DATETIME,

    end_date DATETIME

);

​

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;

​