Facebook 面经题

Facebook

常考类型总结：

1. JOIN

2. CASE

3. update + join

4. replace + join

5. Building a histogram, 算某个量的分布，需要用到多个 GROUP BY

面经题

Q1

T1:user_id (showing today pays ads fee) on day T

T2:two columns, user_id and their status on day T-1

Status include: new, churn, resurrecting, existing

And ask you to use today’s user_id list to update T2

t是当天paid的fee，t-1是昨天的状态，分为几种情况，是否是新用户，还是回头客，还是旧用户。然后根据t的情况去更新t-1的table。用的是case when条件语句。

UPDATE T1
JOIN T2 ON T1.user_id = T2.user_id
SET (
    CASE
    WHEN THEN
    END
    )

Note: 也可能是考 replace into， 如果有新的user出现在T

Q2

you have a table where you have date, user_id, song_id and count. It shows at the end of each day how many times in her history a user has listened to a given song. So count is cumulative sum. You have to update this on a daily basis based on a second table that records in real time when a user listens to a given song. Basically, at the end of each day, you go to this second table and pull a count of each user/song combination and then add this count to the first table that has the lifetime count. If it is the first time a user has listened to a given song, you won't have this pair in the lifetime table, so you have to create the pair there and then add the count of the last day.

REPLACE INTO summary_table(name,song,tot_count)
SELECT d_name, d_song, d_cnt + IFNULL(tot_count,0)
FROM
    (
    SELECT 
        name AS d_name,
        song AS d_song,
        count(*) AS d_cnt 
    FROM today_table AS d 
    WHERE date ='12-26-2017' 
    GROUP BY name,song
    )
LEFT JOIN
    (
    SELECT 
        name AS s_name,
        song AS s_song,
        tot_count 
    FROM summary_table AS s
    )
ON s_name=d_name AND s_song=d_song;

Q3

There is a table that tracks every time a user turns a feature on or off, with columns user_id, action ("on" or "off), date, and time.

1. How many users turned the feature on today?

2. How many users have ever turned the feature on?

3. In a table that tracks the status of every user every day, how would you add today's data to it?

-- 1
SELECT COUNT(user_id)
FROM table
WHERE date = CURDATE() AND action = 'on';

-- 2
SELECT COUNT(DISTINCT user_id)
FROM table
WHERE action = 'on';

-- 3
INSERT INTO total(user_id, action, date, time)
SELECT user_id, action, date, time
FROM today

Q4

2 people listen to same music(at least 3 same music) find them out.

Solution: GROUP BY music, get the names of the music that 2 people are listening. Then find the 2 people listen to that music.

SELECT people
FROM table
WHERE music_name IN
    (
    SELECT music_name
    FROM table
    GROUP BY music_name
    HAVING COUNT(*) = 2
    );

Q5

给一个post and comment table，找出comment数量的分布，用两个group by就可以了

SELECT commentFreq, COUNT(commentFreq)
FROM
    (
    SELECT PostID, COUNT(CommentID) AS commentFreq
    FROM Comments
    GROUP BY PostID
    )
GROUP BY commentFreq

Q6 leetcode 602. Friend Requests II: Who Has the Most Friends 升级版

Given an event-level table of interactions between pairs of users (note that there aren't duplicates in one day for one pair of users), for each possible number of "people interacted with" find the count for that group in a given day (i.e. 10 people interacted with only one person, 20 with 2, etc.).

Solution

Find the "union all" of the user 1 col and the user 2 col for one particular day. Group by user, and find the count for that user (user_count), this is how many people interacted with that user in that day. Finally, group this table by user_count and find a count by user_count. This solves the original question.

SELECT freq, COUNT(freq)
FROM
    (
    SELECT user, COUNT(user) AS freq
    FROM
        (
        SELECT user1 AS user
        FROM table
        UNION ALL
        SELECT user2 AS user
        FROM table
        ) as total
    GROUP BY user
    )
GROUP BY freq

Q7

left outer join, inner join,如果inner join两个表以后行数变多了怎么回事，行数变少了怎么回事

Solution: 行数变多了是因为有link有重复值，行数变少了是因为link有NULL。

Q8

Write a sql query to find out the overall friend acceptance rate for a given date?

Table :- User_id_who_sent|User_id_to_whom|date|Action (Sent, accepted, rejected etc)

SELECT
    (
    SELECT COUNT(DISTINCT User_id_who_sent, User_id_to_whom)
    FROM table
    WHERE date = '' AND action = 'accepted'
    )
    /
    (
    SELECT COUNT(DISTINCT User_id_who_sent, User_id_to_whom)
    FROM table
    WHERE date = '' AND action = 'sent'
    )

Q9

dialoglog

(userid int

appid int

eventID char , a flag either "imp" or "click"

timestamp

)

1. How to compute click-through rate (in mySQL)?

2. Now do it in for each app.

-- 1
SELECT click/impression AS CTR
FROM
    (
    SELECT 
        SUM(CASE WHEN eventID= 'imp' THEN 1 ELSE 0 END) AS impression,
        SUM(CASE WHEN eventID= 'click' THEN 1 ELSE 0 END) AS click,
    FROM dialoglog
    )

-- 2
SELECT appId, click/impression AS CTR
FROM
    (
    SELECT 
        appId,
        SUM(CASE WHEN eventID= 'imp' THEN 1 ELSE 0 END) AS impression,
        SUM(CASE WHEN eventID= 'click' THEN 1 ELSE 0 END) AS click,
    FROM dialoglog
    GROUP BY appId
    )

Q10

Write an SQL query that makes recommendations using the pages that your friends liked. Assume you have two tables: a two-column table of users and their friends, and a two-column table of users and the pages they liked. It should not recommend pages you already like.

SELECT user.Id, p1.pageId
FROM user
 JOIN page AS p1 ON user.friendId = p1.userId
 LEFT JOIN page AS p2 ON (p2.pageId = p1.pageId AND p2.userId = user.Id)
WHERE p2.userId is NULL;

Q11

how to generate all possible friendship given four tables with the "request", "accept","reject", and "remove" information.

Others:

• Leetcode上Database里Customers Who Never Order/Find Cumulative Salary of an Employee/Customer Placing the Largest Number of Orders这几道题。

• SQL assignment was to write a query which computes conversion rates for 2 products given the log of users actions with type of action for each product type.