一亩三分地

 找回密码 注册账号

扫描二维码登录本站

BBS
指尖新闻
Offer多多
Salarytics
Learn
Who's Hiring?
疫情动态
Instant
客户端
微信公众号
扫码关注公众号
留学申请公众号
扫码关注留学申请公众号
Youtube频道
留学博客
关于我们
查看: 1273|回复: 4
收起左侧

[统计--教材|资料] 总结了下近期sql面试题,还有我写的答案。求米!!

 关闭 [复制链接] |试试Instant~ |数学|统计, 统计--教材|资料
抢楼 抢楼 本帖为抢楼帖,欢迎抢楼! 
我的人缘0

分享帖子到朋友圈
本楼: 👍   100% (1)
 
 
0% (0)   👎
全局: 👍   100% (82)
 
 
0% (0)    👎

注册一亩三分地论坛,查看更多干货!

您需要 登录 才可以下载或查看,没有帐号?注册账号

x
Tables
1: Users   
user_id | install_date | channel (paid/organic)
2: Revenue --- records each subscription
         user_id | calender_date | revenue
3: Events --- records each instance of user actions (each time user sings, each time user is active)
         user_id | calender_date | event_name(singing,active_user) | timestamp


Q1: how many users  installed?

SELECT
COUNT(DISTINCT user_id) AS total_users. check 1point3acres for more.
FROM
Users. check 1point3acres for more.
WHERE
install_date IS NOT NULL

Q2: what proportion came through paid channels?
. 1point3acres
SELECT
SUM( CASE WHEN channel = ‘paid’ THEN 1 ELSE 0 END) / COUNT(*) AS paid_ratio
FROM
Users
WHERE
install_date IS NOT NULL

OR

Select Round(count(distinct user_id) *100 /(select count(distinct user_id) from users),2)
From users
Where channel=’Paid’

Q3: What was the average revenue per user generated between the day a user installed and seven days later (Day 8 Revenue per User)?  e.g. 1000 installs, $500 revenue, $0.50 RPU

WITH new_t AS (
SELECT
User_id
, install_date
, calender_date
, revenue
FROM-baidu 1point3acres
Users U
JOIN        Revenue R ON U.user_id = R.user_id
WHERE
Calender_date < = date_add(install_date, INTERVAL 7 DAY) )

SELECT
User_id
, AVG(revenue) AS avg_rvnue
FROM
New_t
GROUP BY
User_id

Q4: Calculate Day 2 retention based on the number of times a user sang on Day 1?

WITH new_t AS (
SELECT
User_id
, MIN(Calender_date) AS first_day
FROM
Events
WHERE
Event_name = ‘singing’
GROUP BY -baidu 1point3acres
user_id)
,
Return_user AS
(SELECT
user_id
FROM
Events E1
JOIN
. From 1point 3acres bbs new_t  ON (E1.user_id = new_t.user_id AND new_t.first_day + 1 = E1.canlender_date)) . From 1point 3acres bbs

SELECT
ROUND(COUNT(return_user.user_id)/COUNT(new_t.user_id),2) AS retention_rate
FROM
New_t
LEFT JOIN
Return_user ON return_user.user_id = new_t.user_id





FB 经典题目!
有个table,有content_id, content_type,target_id  其中content_type有post,comment,like,photo等, target_id 是comment 对应的post_id(for example comment 对应的post_id)   
Q1: 求最多comment的post 的id

SELECT
Target_id
FROM
(SELECT
Target_id
          , RANK() OVER( ORDER BY COUNT(content_id) DESC) AS rank_list
FROM
Table
WHERE
Content_type = ‘comment’) AS rank_table
WHERE
Rank_list = 1


Q2:求comment数目的分布

WITH comment_table AS (SELECT
   Content_id
, COUNT(Content_type) AS comment
FROM
Table
WHERE
Content_type = ‘comment’
GROUP BY
Content_id)

SELECT
  Comment -baidu 1point3acres
, COUNT(content_id) AS cnt
FROM
Comment_table
GROUP BY
Comment
ORDER BY
COUNT(content_id). check 1point3acres for more.
;


Q3: What is the total number of comments and total number of posts
SELECT
SUM(IF(content_type = ‘comment’,1,0)) AS total_comment
           , SUM(IF(content_type = ‘post’,1,0)) AS total_post
FROM. 1point3acres
Table
;


FB 经典题目!
Table Friend:   Date | Action (‘sent’, ‘accept’, ‘unfriend’) | User_id   | Target_id
如何判断两个人是不是好朋友. (Target user’s action is accepted)
SELECT
   F1.User_id
, F1.Target_id
, CASE WHEN accepter_table.action = ‘accept’ and sender_table.action = ‘sent’ THEN ‘yes’
  ELSE ‘no’ END AS Friends
FROM
Friend AS sender_table
LEFT JOIN
Friend AS accept_table ON accepter_table.User_id = sender_table.Target_id
;

generate friend request acceptance rate (a: Create the table for calculation, b: calculate acceptance rate)

SELECT
  ROUND( COUNT(accepter_table.User_id)/COUNT(sender_table.User_id),2) AS acceptence_rate
FROM
Friend AS sender_table
LEFT JOIN
           Friend AS accepter_table ON accepter_table.User_id = sender_table.Target_id

Generate the friend request acceptance rate for people who accept within 24 hours

SELECT
  ROUND( COUNT(accepter_table.User_id)/COUNT(sender_table.User_id),2) AS acceptence_rate
FROM. 1point3acres
Friend AS sender_table
LEFT JOIN
           Friend AS accepter_table
ON (accepter_table.User_id = sender_table.Target_id  AND DATE_ADD( ‘hour’, 24, sender_table.date) < = accept_table.date AND accept_table.date > sender_table.date)



Instagram NBA Follower
Table 1 (stars_category) [name,sports category] t1中只有celebrity运动员。pk=user_name
Table 2 (instgram_users) [user id, user_name, registration_date] t(2中是所有人的用户信息,包括celebrity和普通人,且不会出现celebrity和普通人重名的情况(重要假设)。pk=user_id
Table 3 (followee_follower) user_id, user_id_following, follow_date] 用户follow信息,注意user_id_following中包括celebrity和普通人

Q1:求每个category的follower/计算每个category有多少人follow

SELECT
Category
          , COUNT(ff.user_id)-baidu 1point3acres
FROM
(SELECT
  User_id
           , Category
  FROM
  Instgram_users iu
  LEFT JOIN stars_category sc ON sc.name = iu.user_name) as table_a
JOIN
Followee_follower ON table_a.user_id = ff.user_id_following
GROUP BY
Category

Q2: 求有多少个NBA category follow NFL

WITH NFL AS (SELECT
user_id
FROM
Instgram_users iu
JOIN
Starts_category sc ON iu.user_name = sc.name
WHERE
Category = ‘NFL’)

SELECT
          COUNT(iu.user_id) AS total_NBA
FROM
Instgram_users iu
JOIN
stars_category sc ON sc.name = iu.user_name
JOIN
Followee_follower ON iu.user_id = ff.user_id
JOIN
NFL ON NFL.user_id = ff.user_id_following
WHERE
Category = ‘NBA’



table  member_id|company_name|year_start    note: table is employee info  table . 1point3acres
Q1: count members who ever moved from Microsoft to Google?
SELECT
COUNT(Member_id) AS total_count
FROM
Table AS T1
JOIN
Table AS T2
ON        (T1.member_id = T2.member_id. check 1point3acres for more.
AND   T1. year_start < T2.year_start)

WHERE
T1.company_name = ‘Microsoft’
AND   T2.company_name = ‘Google’
;


Q2:  count members who directly moved from Microsoft to Google? (Microsoft -- linkedin -- Google doesn't count)

WITH rnk_table AS (
SELECT
   *
, RANK()OVER(PARTITION BY member_id ORDER BY year_start ASC) AS rnk_index
FROM
Table)

SELECT
COUNT(member_id) AS total_count
FROM
rnk_table T1
JOIN
Rnk_table T2 AS T2
ON         (T1.member_id = T2.member_id
AND         T2.rnk_index = T1.rnk_index + 1)
WHERE
T1.company_name = ‘Microsoft’
AND   T2.company_name = ‘Google’
;




评分

参与人数 9大米 +15 收起 理由
bryanjhy + 3 给你点个赞!
shinan88888 + 1 给你点个赞!
JLE + 1 给你点个赞!
j.li32 + 1 赞一个
tuoni + 1 赞一个
ShikiH + 2 给你点个赞!
FinalLi + 2 给你点个赞!
HHHHarold + 3 给你点个赞!
音无lac + 1 赞一个

查看全部评分


上一篇:SAS ADV机经哪里下载
下一篇:SAS ADV Guide有需要的朋友请下载

本帖被以下淘专辑推荐:

我的人缘0
罗小罗-_- 2020-6-3 12:16:05 | 显示全部楼层
本楼: 👍   0% (0)
 
 
0% (0)   👎
全局: 👍   100% (7)
 
 
0% (0)    👎
想问下lz,sql的题怎么刷呢(LeetCode,hackerrank),主刷easy和medium。sql在自学,时间充裕。地里基本都是algo的刷题方法。
回复

使用道具 举报

我的人缘0
本楼: 👍   0% (0)
 
 
0% (0)   👎
全局: 👍   100% (1)
 
 
0% (0)    👎
楼主都面了哪些职位呢?
回复

使用道具 举报

我的人缘0
heresoutheast 2020-6-3 21:58:28 | 显示全部楼层
本楼: 👍   0% (0)
 
 
0% (0)   👎
全局: 👍   100% (1)
 
 
0% (0)    👎
谢谢分享!
回复

使用道具 举报

我的人缘0
kittycerry 2020-6-3 23:47:40 | 显示全部楼层
本楼: 👍   0% (0)
 
 
0% (0)   👎
全局: 👍   92% (49)
 
 
7% (4)    👎
终于可以留言了,楼主,fb经典题第一个,q2,为什么group by 的是Content_id啊?我理解为group by的是target啊?可能是我什么地方理解错了?
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 注册账号

本版积分规则

隐私提醒:
■为防止被骚扰甚至人肉,不要公开留微信等联系方式,请以论坛私信方式发送。
■特定版块可以超级匿名:https://pay.1point3acres.com/tools/thread
■其他版块匿名方法:http://www.1point3acres.com/bbs/thread-405991-1-1.html

手机版|||一亩三分地

GMT+8, 2020-7-13 06:03

Powered by Discuz! X3

© 2001-2013 Comsenz Inc. Design By HUXTeam

快速回复 返回顶部 返回列表