-- Get articles created in Igbo Wikipedia using Content Translation

-- including source wiki, translation count, and timestamp

SELECT 

    p.page_title AS igbo_article_title,

    -- Extract source wiki information from rc_params

    JSON_EXTRACT(rc_params, '$.source.wiki') AS source_wiki,

    COUNT(DISTINCT ll.ll_lang) AS translation_count,

    rc.rc_timestamp AS translation_timestamp

FROM page p

JOIN recentchanges rc ON p.page_id = rc.rc_cur_id

JOIN change_tag ct ON rc.rc_id = ct.ct_rc_id

JOIN change_tag_def ctd ON ct.ct_tag_id = ctd.ctd_id

LEFT JOIN langlinks ll ON p.page_id = ll.ll_from

WHERE 

    ctd.ctd_name = 'contenttranslation'

    AND rc.rc_timestamp >= '20140101000000'

    AND rc.rc_timestamp <= '20251231235959'

    AND p.page_namespace = 0  -- Main namespace articles only

    AND rc.rc_params LIKE '%"source"%'  -- Ensure rc_params contains source information

GROUP BY 

    p.page_title,

    JSON_EXTRACT(rc_params, '$.source.wiki'),

    rc.rc_timestamp

ORDER BY rc.rc_timestamp DESC

LIMIT 100 OFFSET {offset};