AI Translation Progress Graph

Community Building Data & reporting
,
4

I have been working on the data explorer SQL to create the report listed above. I am not a SQL expert, but these are working. The queries below will provide this level of information.

Translation Status Report

Area Eligible Translated To Be Translated
Topics 540 450 90
Posts 3,700 800 2900

Topics:

-- Setup:
-- Upate sql your translation settings
-- Days to Backfill - Interval 'xxx'
-- Category to ignore - Category_id NOT IN ()
-- Topic type - Regular or private_message
--
-- Translation status:
--  Total Topics to translate: comment out both 'and topics.locale' where statements
--  Topics not translated: uncomment only - topics.locale is null
--  Topics translated: uncomment only - topics.local = 'en'

SELECT count(distinct topics.id)
     FROM topics
     JOIN posts  ON topics.id  = posts.topic_id
    WHERE posts.created_at >= NOW() - INTERVAL '100 days' 
     AND  posts.user_id > 0
     AND  topics.category_id NOT IN (22,3)
     AND  topics.archetype = 'regular'
--   AND  topics.locale = 'en'
--   AND  topics.locale is null

Posts:

-- Setup:
-- Upate sql your translation settings
-- Days to Backfill - Interval 'xxx'
-- Category to ignore - Category_id NOT IN ()
-- Topic type - Regular or private_message
--
-- Translation status:
--  Total posts to translate: comment out both 'and posts.locale' where statements
--  posts not translated: uncomment only - posts.locale is null
--  posts translated: uncomment only - posts.locale = 'en'

SELECT count(*)
     FROM posts
     JOIN topics  ON topics.id  = posts.topic_id
    WHERE posts.created_at >= NOW() - INTERVAL '100 days' 
     AND  posts.user_id > 0
     AND  topics.category_id NOT IN (22,3)
     AND  topics.archetype = 'regular'
--     AND  posts.locale = 'en'
--     AND  posts.locale is null