Data Analysis

I. Blogs Active: 5962 Break down by countries '3145','United States' '548','United Kingdom' '485','Philippines' '394','Canada' '319',NULL '231','Australia' '157','Hong Kong' Type 2 Blogs (Hidden): 1168 select * from user_blog a join user b on a.user_id = b.user_id and b.hidden=1 order by blog_id desc limit 100000; II. Articles No of type 1 articles: 8,883,886 1,269,249 articles have views 661,461 articles have 1 view only. 1115874 articles have 5 views or below 153,365 articles have 5 more views select count(*) from (select cgroup_id, count(*) as count from user_comment_view group by cgroup_id ) b ; No of type 2 articles: 985,429 III. Topics Out of 2,955,747 active topics, we have 311,776 type 2 topics with 0 article. 1,738,254 topics with 1 article. 328,269 topics with 2 articles. 134,003 topics with 3 articles. 443,451 topics with 4 or more articles. (Core topics) Topics with word length >=8 but articles < 3 SELECT topic_id FROM topic WHERE (LENGTH(title) - LENGTH(REPLACE(title, ' ', '')) + 1 >= 8) and articles <=3 and status = 1 limit 100000; Get the number of views for topics from user session select count(*), topic_id from user_comment_view a join topic_comments_history b on a.cgroup_id=b.cgroup_id group by topic_id limit 100;