instagram

SQL: post type distribution

You have one table named content_action which has 5 fields:

Date,

User_id (content_creator_id),

Content_id (this is the primary key),

Content_type (with 4 types: status_update, photo, video, comment),

Target_id (it’s the original content_id associated with the comment, if the content type is not comment, this will be null)

Question:

1.find the distribution of stories（photo+video） based on comment count?

SELECT num, count(DISTINCT Target_id)
FROM
    (
    SELECT c1.Target_id, count(DISTINCT Content_id) as num
    FROM content_action c1 
        LEFT JOIN content_action c2 ON c1.Target_id = c2.Content_id 
                                    AND c1.Content_type = 'comment' 
                                    AND c2.Content_type IN ('photo', 'video')
    GROUP BY c1.Target_id
    ) AS temp
GROUP BY num

2.what if content_type becomes (comment/ post), what is the distribution of comments?

3.Now what if content_type becomes {comment, post, video, photo, article}，what is the comment distribution for each content type?

Analytics

增加了切换用户的功能

1. 有什么metrics可以衡量performance

   1. Answer: number of new users，active user，time spend

2. 如果发现新用户注册人数增加，但是insta总体time spent没有变化，这是为什么。

   1. Answer: One user sign up multiple accounts, so the number of new accounts increase. But the overall time each user spend on Instagram doesn't change.

3. 如何判断两个user是不是一个人注册的多个号

   1. Answer: check the similarity of shared friends，shared followers，id