de.wikibooks old+new+uploader image GFDL files by upload date in cat

SQL

-- CTE to find the earliest timestamp for each file in the image table
WITH earliest_images AS (
    SELECT img_name, MIN(img_timestamp) as earliest_timestamp
    FROM image
    GROUP BY img_name
),
-- CTE to find the earliest timestamp for each file in the oldimage table
earliest_oldimages AS (
    SELECT oi_name, MIN(oi_timestamp) as earliest_timestamp
    FROM oldimage
    WHERE oi_deleted = 0
    GROUP BY oi_name
)
-- Query to get original upload date from the image table
SELECT CONCAT("# [[:File:", page_title, "]]") as File, ei.earliest_timestamp as original_upload_date
FROM templatelinks
JOIN page ON tl_from = page_id
JOIN earliest_images ei ON ei.img_name = page_title AND page_namespace = 6
JOIN categorylinks ON cl_from = page_id
AND cl_type = "file"
AND cl_to = "GFDL-Bild"
GROUP BY page_title, ei.earliest_timestamp

UNION ALL

-- Query to get information from the oldimage table
SELECT CONCAT("# [[:File:", oi_name, "]]") as File, eoi.earliest_timestamp as original_upload_date
FROM oldimage
INNER JOIN page p ON p.page_title = oi_name AND p.page_namespace = 6
INNER JOIN earliest_oldimages eoi ON eoi.oi_name = p.page_title
INNER JOIN categorylinks c ON c.cl_from = p.page_id AND c.cl_to = "GFDL-Bild" -- Non-free files
-- INNER JOIN categorylinks c ON c.cl_from = p.page_id AND c.cl_to = "सभी_मुक्त_मीडिया" -- Free files
WHERE oi_deleted = 0
GROUP BY oi_name, eoi.earliest_timestamp;
SQL

Checking query status...
