Patrick Trainer - Kirsten Alana Instagram Challenge

#1

3 Likes
#2

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;
1 Like