Skip to main content

Comments

1 comment

  • Patrick

    The visualization was made using Tableau

    SQL used is attached!

    Had a great time paying around with this!

    WITH t1 AS (
      WITH comments AS (
        SELECT c.id,
               c.media_id,
               c.created_time,
               c.from_username
        FROM kirstenalanainstagram_instagram_comments c
      ),
    
           posts    AS (
             SELECT date_trunc('month', media.created_time) AS post_date,
                    count(DISTINCT media.id)                AS post_count,
                    sum(media.comments_count)               AS total_cmt
             FROM kirstenalanainstagram_instagram_media media
             GROUP BY 1
             ORDER BY 1 ASC
           )
    
      SELECT comments.from_username,
             date_trunc('month', comments.created_time) AS comment_date,
             count(DISTINCT comments.id)                AS comment_count,
             count(DISTINCT comments.media_id)          AS monthly_post_count,
             posts.post_count                           AS total_post_count,
             posts.total_cmt                            AS total_comments
    
      FROM comments
           INNER JOIN posts
                      ON posts.post_date = date_trunc('month', comments.created_time)
      GROUP BY 1, 2, 5, 6
    
      ORDER BY comment_date ASC
    )
    
    SELECT t1.from_username,
           t1.comment_date,
           t1.comment_count,
           t1.monthly_post_count,
           t1.total_post_count,
           t1.total_comments,
           max(t2.comment_date) AS last_comment_date
    FROM t1
         LEFT JOIN t1 AS t2
                   ON t1.from_username = t2.from_username
                     AND date_add('month', 1, t1.comment_date) != t2.comment_date
    GROUP BY t1.from_username,
             t1.comment_date,
             t1.comment_count,
             t1.monthly_post_count,
             t1.total_post_count,
             t1.total_comments
    ORDER BY t1.comment_date ASC;
    
    0

Please sign in to leave a comment.

Powered by Zendesk