Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- DROP TABLE IF EXISTS scratch.relcomments;
- CREATE TABLE scratch.relcomments AS
- SELECT A.post_id, A.page_id, B.created_on - A.created_at as delay
- FROM facebook.comment_contents B, scratch.relposts A
- WHERE A.post_id = B.post_id;
- DROP TABLE IF EXISTS scratch.delaytimings;
- CREATE TABLE scratch.delaytimings AS
- SELECT page_id, date_trunc('hour',delay) as hours, count(*) as count
- FROM scratch.relcomments
- GROUP BY page_id, hours
- ORDER BY page_id, hours;
- DROP TABLE IF EXISTS scratch.delaytimingsoverlaps;
- CREATE TABLE scratch.delaytimingsoverlaps AS
- SELECT A.count as countA, B.count as countB, A.page_id as page_idA, B.page_id as page_idB
- FROM scratch.delaytimings A INNER JOIN scratch.delaytimings B
- ON A.hours = B.hours
- WHERE A.page_id < B.page_id;
- DROP TABLE IF EXISTS scratch.delaytimingsoverlaps_counts;
- CREATE TABLE scratch.delaytimingsoverlaps_counts AS
- SELECT sum(countA) as tcountA, sum(countB) as tcountB, page_idA, page_idB
- FROM scratch.delaytimingsoverlaps
- GROUP BY page_idA, page_idB;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement