学习帖， 每天总结今天学习成果

今天看完了w3school 上所有SQL的， 看了SQL的window function。
Datacamp 的一篇文章写得不错。
https://www.datacamp.com/community/tutorials/sql-tutorial-query

今天看了mode sql, google analytics, lean analytics
Chapter 7: What business are you in?

Chapter 11: Model Four: Media Site
Media sites care most about click through or display rate, because they are actual revenue. But they also need to maximize the time visitors spend on the site, the number of pages they see, the number of unique visitors.

Metric media sites care about:
Audience and churn:
How many people visit the website and how loyal they are
Ad inventory:
The number of impressions (refers to the point in which an ad is viewed once by a visitor, or displayed once on a web page) that can be monetarized
Ad rates:
How much the site can make from the impressions based on the content it covers and the people who visit.
Click through rate:
How many impressions actually turn into money. (Number of clicks/Number of page views)
Content/advertising balance:
The balance of ad inventory rates and content that maximizes overall performance. (not too much or too little ads)

Chapter 12: User-Generated Content
Facebook’s primary concern is the growth of an engaged community which creates content. In the business model, you are focused on creating good contents, which means not only posts and uploads but also votes, comments, spam flagging, and other valuable activity. UGC is about constantly trying to move your uses to higher and higher levels of engagement, turning lurkers into voters, voters into commenters and so on.

Engagement tiers of Facebook:
Drive by (one time) visitors                                visitors
Returning visitors                                        lurkers
Enrolled users                                                users
Voters/Flaggers                                        like, flag
Commenters                                                post a comment
Content creators                                        poster
Moderators                                                group admin
Group creators                                        event, place, or group creators

Besides the metrics for media sites, other metrics:
Number of engaged visitors:
How often people come back and how long they stick around. (depending on how you define engaged)
Content creation:
The percentage of visitors who interact with content in some way, from creating it to voting on it.
Engagement funnel changes:
How well the site moves people to more engaged level of content over time
Value of created content:
The business value of content, from donations to media clicks
Content sharing and virality:
How contents get shared, and how this drives growth.
Notification effectiveness:
The percentage of users who, when told something by push, email, act on it

今天继续mode SQL. Lean analytics 看到了17章。总体感觉这本书比较讲的比较笼统。

这两天上了google analytics 的课，拿了beginner, advances, power 的certification.
继续看了udacity 的ABtesting.
Additional techniques for brainstorming and validation metrics:
External data:
External data source:
1 Outside market share data, like Comscore and Hitwise, which includes things like how much users visit your website.
2 There are companies like Nielsen, Forrester run and publish their own studies.
3 Higher level aggregators of data, such as eMarketer, which provide summaries from all of these sources
4 Public research papers
Usage of external data:
1 Compare your data directly with the external data to validate your simple metric, they are measuring the same thing
2 Provide supporting evidence for your metric, might measure different thing, but related.
3 Publicly available academic papers, for example, a research paper compared user’s satisfaction with the duration of the search task. Then we can evaluate the not scalable user reported satisfaction by using the duration of the task.

Get your own in-depth data:
User experience search (UER):
+ Good for brainstorming  
+ Can use special equipment
_ You need to validate the result
Focus group:
+ Get feedback from hypotheticals
_ run into group thinking
Surveys:
+ Useful for metrics you can’t directly measure
_ Can’t directly compare the results
_ The response is not truthful

Retrospective analysis:
Running analysis on the existing data set without an experiment structure. Useful for generating ideas about A/B testing and for validating metrics. For example, you observe something from the UER, then you find patterns from the existing data set.

Long-term prospective experiments:
For example, one thing we care about is the long term revenue, we want to know by showing users fewer ads but good ads can increase the long term revenue. Run experiments over months or years to measure the long-term effects, then build models to determine which short-term metrics best predict the long-term effects.

Human evaluation:
You give guideline for keeping the data consistent. Pay users to label the data.


Seven questions to ask yourself before building a feature:
1 Why will it make things better?
2 Can you measure the effect of the feature?
3 How long will the feature take to build?
4 Will the feature overcomplicate things?
5 How much risk is there in this new feature?
6 How innovative is the new feature?
7 What do users say they want?

For example, we want to choose a metric for evaluating the load time of the video.
Sensitive is about for different videos (may be different length, different resolution), and the metric is sensible to that difference. If the metric is the same, which means this metric is not sensitive.
Robustness is about for similar videos, and the metric shouldn’t vary.

Law of large numbers: perform the experiment for a large number of times, the average of the results will be the expected value. For example, roll a fair dice 1-6, the expected value is 3.5, after roll a long time, the observed average will be 3.5. There is small difference between strong LLN and weak LLN, strong LLN says the observed average will be exactly 3.5. Weak LLN says the observed average will be approximately.

Central limit theorem: there are a lot of observations of a variable, it doesn’t follow normal distribution. Draw a sample of size n from the observations, calculate the average. Repeat the process many times. The average will follow a normal distribution. The larger the sample size, the closer to the normal distribution.

楼主好厉害 鼓掌

SQL 1:
Table: survey_log   Columns: user_id, question_id, question_order, event(impressed, answered, skipped), timestamp

Background: The user will get a survey, first he gets an impression, then he can choose to answer or skip. Which means each user each question has two observations. The question can be answered in different order which represents by question order.

Assume the user has to answer the survey and only has to answer this survey for one time.

Q1: What is the question with highest answer rate?
SELECT
question_id, SUM(CASE WHEN event = 'answered' THEN 1 ELSE 0 END)/SUM(CASE WHEN event = 'impressed' THEN 1 ELSE 0 END) AS answer_rate
FROM survey_log
GROUP BY question_id
ORDER BY answer_rate desc
LIMIT 1;

Q2: A new user who has already skipped two questions which have two highest answer_rate, how to determine the next question which is most possible for this user to answer.
The userid is given as xxx.

The simplest idea is to look at all the questions which the user hasn't answered. Pick up the question with the highest answer rate.

SELECT
question_id, SUM(CASE WHEN event = 'answered' THEN 1 ELSE 0 END)/SUM(CASE WHEN event = 'impressed' THEN 1 ELSE 0 END) AS answer_rate
FROM survey_log
WHERE question_id NOT IN (
SELECT
question_id
FROM survey_log
WHERE user_id = xxx
    AND event = 'skipped'
)
GROUP BY question_id
ORDER BY answer_rate DESC
LIMIT 1;

This method is not personalized for this user, it includes all the users as long as not including these skipped questions.

The second method tries to find users who skip same question as the new user and find the highest conversion rate question among these users. One subquery finds the two skipped questions. The next subquery finds the users who also skip the two questions. Finally, find the question with highest answer rate among users skipped two questions except two skipped questions.

SELECT
question_id, SUM(CASE WHEN event = 'answered' THEN 1 ELSE 0 END)/SUM(CASE WHEN event = 'impressed' THEN 1 ELSE 0 END) AS answer_rate
FROM survey_log
WHERE user_id IN (
SELECT
user_id
FROM survey_log
GROUP BY user_id
HAVING SUM(CASE WHEN question_id IN
(SELECT
question_id
FROM survey_log
GROUP BY question_id
ORDER BY SUM(CASE WHEN event = 'answer' THEN 1 ELSE 0 END)/SUM(CASE WHEN event = 'impressed' THEN 1 ELSE 0 END) DESC
LIMIT 2) AND event = 'skipped'
THEN 1 ELSE 0 END
) = 2
)
AND question_id NOT IN (
SELECT
question_id
FROM survey_log
GROUP BY question_id
ORDER BY SUM(CASE WHEN event = 'answer' THEN 1 ELSE 0 END)/SUM(CASE WHEN event = 'impressed' THEN 1 ELSE 0 END) DESC
LIMIT 2
)
GROUP BY question_id
ORDER BY answer_rate DESC
LIMIT 1;

SQL2
Table: friend_request    Columns: sender_id, send_to_id, request_date
Table: request_accepted    Columns: request_id, accept_id, accept_date

Assume not all accepted requests need to come from friend_request, there is no 1 --> 3 and 3 -->1 accept pair in request_accepted, there is only one who has the most friends.

Q1: Calculate the acceptance rate
SELECT
IFNULL(SELECT COUNT(*) FROM (SELECT DISTINCT sender_id, send_to_id FROM friend_request)/SELECT COUNT(*) FROM (SELECT DISTINCT request_id, accept_id FROM request_accepted), 0) AS accept_rate

Q2: Calculate the one who has the most friends.
SELECT
a.request_id, COUNT(*) AS num_friends
FROM
(
SELECT
request_id
FROM request_accepted
UNION ALL
SELECT
accept_id
FROM request_accepted
) a
GROUP BY a.request_id
ORDER BY nun_friends DESC
LIMIT 1;

If request_accepted allow 1 --> 3, 3 -->1
SELECT
a.request_id, COUNT(DISTINCT a.accept_id) AS num_friends
FROM
(
SELECT
request_id, accept_id
FROM request_accepted
UNION ALL
SELECT
accept_id, request_id
FROM request_accepted
) a
GROUP BY a.request_id
ORDER BY num_friends
LIMIT 1;

SQL 3
Table: composer    Columns: userid, event(enter, post, cancel), date
Q1: What is the post success rate for each day last week?
SELECT
DATE, IFNULL(SUM(CASE WHEN event = 'post' THEN 1 ELSE 0 END)/SUM(CASE WHEN event = 'enter' THEN 1 ELSE 0 END), 0) AS post_success_rate
FROM composer
WHERE DATEDIFF(CURDATE(), date) <= 7
GROUP BY DATE;

Table: user     columns: userid, date, country, dau_flag(0, 1)
Q2: What is the average number of posts per daily active user by country today?
SELECT
u.country, IFNULL(COUNT(c.userid)/COUNT(DISTINCT u.userid), 0) AS avg_num_posts
FROM user u
LEFT JOIN composer c
ON u.userid = c.userid
    AND u.date = CURDATE()
    AND c.date = CURDATE()
    AND u.dau_flag = 1
    AND c.event = 'post'
GROUP BY u.country;

SQL4
Table: message    columns: date, sender_id, receiver_id
Q1: Who contact more than 5 friends in a day?
If only send counts as contact:
SELECT
date, sender_id
FROM message
GROUP BY date, sender_id
HAVING COUNT(DISTINCT receiver_id) >= 5;

If both send and receive counts as contact:
SELECT
a.date, a.user
FROM
(SELECT
date, sender_id AS user, receiver_id AS target
FROM message
UNION ALL
SELECT
date, receiver_id AS user, sender_id AS target
FROM message) a
GROUP BY a.date, a.user
HAVING COUNT(DISTINCT a.target) >= 5;

Q2: What is the fraction of users who contact more than 5 users today?
SELECT
SUM(CASE WHEN num_friends > 5 THEN 1 ELSE 0 END)/COUNT(*) AS the_fraction
FROM
(SELECT
sender_id, COUNT(DISTINCT receiver_id) AS num_friends
FROM
(SELECT
sender_id, receiver_id
FROM message
WHERE date = CURDATE()
UNION ALL
SELECT
receiver_id, sender_id
FROM message
WHERE date = CURDATE()) a
GROUP BY sender_id) b

Q3: What is the fraction of users who respond in a minute?
Assume this is defined as the users who get response in a minute divided by the users who get a response

SELECT
SUM(CASE WHEN TIMEDIFF(m2.date, m1.date) < 60 THEN 1 ELSE 0 END)/COUNT(*) AS fraction
FROM message m1
JOIN message m2
ON m1.sender_id = m2.receiver_id
    AND m1.receiver_id = m2.sender_id
    AND m1.date < m2.date