16th February, 2016 Long Title SQL, Merge topic script
1. Total number of articles in system
select count(distinct cgroup_id) from topic_comments_history ;
2. Articles that have 1+ pageview
select * from ( select count(*) as count, cgroup_id, create_date from user_comment_view group by cgroup_id ) a where a.count > 0 order by count desc limit 1000000;
Articles that have 10+ pageview
select * from ( select count(*) as count, cgroup_id, create_date from user_comment_view group by cgroup_id ) a where a.count > 9 order by count desc limit 1000000;
3. Most recent 100 days daily pageviews
select create_date, count(*) from user_comment_view group by date(create_date) order by create_date desc limit 100;
4. PageViews for Articles that have 20-word topic that are created over 3 months
select * from user_comment_view a join (select cgroup_id from topic_comments_history where topic_id in (
SELECT
topic_id
FROM
topic
WHERE
(LENGTH(title) - LENGTH(REPLACE(title, ' ', '')) + 1 = 20)
AND create_date <= '2015-11-01')) b on a.cgroup_id = b.cgroup_id ;
5. All 20-word topic
SELECT
*
FROM
topic
WHERE
(LENGTH(title) - LENGTH(REPLACE(title, ' ', '')) + 1 = 20)
AND create_date <= '2015-11-01';
6. All 10+ word 3 month old topics sort by number of articles
SELECT
topic_id, title, articles, create_date
FROM
topic
WHERE
(LENGTH(title) - LENGTH(REPLACE(title, ' ', '')) + 1 >= 10)
AND create_date <= '2015-11-01' order by articles desc limit 30000;
7. Long 10+ wedding blog topics with articles < 10
SELECT
topic_id, title, articles, create_date
FROM
topic
WHERE
(LENGTH(title) - LENGTH(REPLACE(title, ' ', '')) + 1 >= 10)
AND create_date <= '2015-11-01' and articles < 10 and title like '%wedding blog%' limit 30000;
8. Long 10+ random topics with articles < 5
SELECT
topic_id, title, articles, create_date
FROM
topic
WHERE
(LENGTH(title) - LENGTH(REPLACE(title, ' ', '')) + 1 >= 10)
AND create_date <= '2015-11-01' and articles < 5 and title not like '%wedding blog%' limit 30000;
9. Top 100 topics pageviews
select count(*) as count, topic_id from user_comment_view a left join topic_comments_history b on a.cgroup_id = b.cgroup_id group by topic_id order by count desc limit 100;
10. To verify a particular topic (4430652) pageviews
a. SELECT
a.*, b.*
FROM
user_comment_view a
LEFT JOIN topic_comments_history b ON a.cgroup_id = b.cgroup_id where topic_id =4430652;
b.select * from topic where topic_id =4430652;
c.select * from topic_comments_history where topic_id =4430652;
11. Top publisher traffic
SELECT
*
FROM
(SELECT
user_id, COUNT(*)
FROM
user_comment_view a
JOIN topic_comments_history b ON a.cgroup_id = b.cgroup_id
GROUP BY user_id
ORDER BY COUNT(*) DESC limit 10000) aa
JOIN
user_blog bb ON aa.user_id = bb.user_id
LIMIT 0 , 10000
12. Top publisher traffic break down by month
SELECT
*
FROM
(SELECT
user_id, year(a.create_date) as year, month(a.create_date) as month, COUNT(*)
FROM
user_comment_view a
JOIN topic_comments_history b ON a.cgroup_id = b.cgroup_id
GROUP BY user_id, year(create_date), month(create_date)
ORDER BY COUNT(*) DESC limit 10000) aa
JOIN
user_blog bb ON aa.user_id = bb.user_id order by aa.user_id, year(aa.year), month(aa.month)
LIMIT 0 , 10000;
13. Check blog traffic by blog id
SELECT
*
FROM
user_comment_view a
JOIN topic_comments_history b ON a.cgroup_id = b.cgroup_id where user_id in (select user_id from user_blog where blog_id in(3,
125,
971,
3724,
4163,
4273,
4785,
4941,
5002,
5056,
5098,
5119,
5135,
5270,
5308,
5414));
14. Find topics that start with and without a character
es, b.title , b.articles from topic a, topic b where a.articles <5 and a.articles >0 and b.articles >0 and (a.title like '.%' or a.title like '#%') and b.title = substring(a.title, 2, char_length(a.title)-1) limit 100000;
Run merge.sh
./merge.sh topic_ids20160216.txt >merge_log_20160216.txt
Check for progress
tail -f merge_log_20160216.txt
Check for error
grep -v 'mergeResult' merge_log_20160216.txt | grep http > error20160216.txt
select count(distinct cgroup_id) from topic_comments_history ;
2. Articles that have 1+ pageview
select * from ( select count(*) as count, cgroup_id, create_date from user_comment_view group by cgroup_id ) a where a.count > 0 order by count desc limit 1000000;
Articles that have 10+ pageview
select * from ( select count(*) as count, cgroup_id, create_date from user_comment_view group by cgroup_id ) a where a.count > 9 order by count desc limit 1000000;
3. Most recent 100 days daily pageviews
select create_date, count(*) from user_comment_view group by date(create_date) order by create_date desc limit 100;
4. PageViews for Articles that have 20-word topic that are created over 3 months
select * from user_comment_view a join (select cgroup_id from topic_comments_history where topic_id in (
SELECT
topic_id
FROM
topic
WHERE
(LENGTH(title) - LENGTH(REPLACE(title, ' ', '')) + 1 = 20)
AND create_date <= '2015-11-01')) b on a.cgroup_id = b.cgroup_id ;
5. All 20-word topic
SELECT
*
FROM
topic
WHERE
(LENGTH(title) - LENGTH(REPLACE(title, ' ', '')) + 1 = 20)
AND create_date <= '2015-11-01';
6. All 10+ word 3 month old topics sort by number of articles
SELECT
topic_id, title, articles, create_date
FROM
topic
WHERE
(LENGTH(title) - LENGTH(REPLACE(title, ' ', '')) + 1 >= 10)
AND create_date <= '2015-11-01' order by articles desc limit 30000;
7. Long 10+ wedding blog topics with articles < 10
SELECT
topic_id, title, articles, create_date
FROM
topic
WHERE
(LENGTH(title) - LENGTH(REPLACE(title, ' ', '')) + 1 >= 10)
AND create_date <= '2015-11-01' and articles < 10 and title like '%wedding blog%' limit 30000;
8. Long 10+ random topics with articles < 5
SELECT
topic_id, title, articles, create_date
FROM
topic
WHERE
(LENGTH(title) - LENGTH(REPLACE(title, ' ', '')) + 1 >= 10)
AND create_date <= '2015-11-01' and articles < 5 and title not like '%wedding blog%' limit 30000;
9. Top 100 topics pageviews
select count(*) as count, topic_id from user_comment_view a left join topic_comments_history b on a.cgroup_id = b.cgroup_id group by topic_id order by count desc limit 100;
10. To verify a particular topic (4430652) pageviews
a. SELECT
a.*, b.*
FROM
user_comment_view a
LEFT JOIN topic_comments_history b ON a.cgroup_id = b.cgroup_id where topic_id =4430652;
b.select * from topic where topic_id =4430652;
c.select * from topic_comments_history where topic_id =4430652;
11. Top publisher traffic
SELECT
*
FROM
(SELECT
user_id, COUNT(*)
FROM
user_comment_view a
JOIN topic_comments_history b ON a.cgroup_id = b.cgroup_id
GROUP BY user_id
ORDER BY COUNT(*) DESC limit 10000) aa
JOIN
user_blog bb ON aa.user_id = bb.user_id
LIMIT 0 , 10000
12. Top publisher traffic break down by month
SELECT
*
FROM
(SELECT
user_id, year(a.create_date) as year, month(a.create_date) as month, COUNT(*)
FROM
user_comment_view a
JOIN topic_comments_history b ON a.cgroup_id = b.cgroup_id
GROUP BY user_id, year(create_date), month(create_date)
ORDER BY COUNT(*) DESC limit 10000) aa
JOIN
user_blog bb ON aa.user_id = bb.user_id order by aa.user_id, year(aa.year), month(aa.month)
LIMIT 0 , 10000;
13. Check blog traffic by blog id
SELECT
*
FROM
user_comment_view a
JOIN topic_comments_history b ON a.cgroup_id = b.cgroup_id where user_id in (select user_id from user_blog where blog_id in(3,
125,
971,
3724,
4163,
4273,
4785,
4941,
5002,
5056,
5098,
5119,
5135,
5270,
5308,
5414));
14. Find topics that start with and without a character
es, b.title , b.articles from topic a, topic b where a.articles <5 and a.articles >0 and b.articles >0 and (a.title like '.%' or a.title like '#%') and b.title = substring(a.title, 2, char_length(a.title)-1) limit 100000;
Run merge.sh
./merge.sh topic_ids20160216.txt >merge_log_20160216.txt
Check for progress
tail -f merge_log_20160216.txt
Check for error
grep -v 'mergeResult' merge_log_20160216.txt | grep http > error20160216.txt
Comments