一亩三分地

 找回密码 注册账号

扫描二维码登录本站


Salarytics=Salary Analytics
查询工资数据
系统自动计算每年收入

码农求职神器Triplebyte
不用海投
内推多家公司面试

科技公司如何
用数据分析驱动产品开发
coupon code 250off 立减$250

深入浅出AB Test
从入门到精通
coupon code 250off 立减$250
游戏初创公司招聘工程师、UIUX Designer和游戏策划
坐标湾区
DreamCraft创始团队
招聘游戏开发工程师
查看: 615|回复: 3
收起左侧

PayPal Product Analytics SQL 面經

[复制链接] |试试Instant~
我的人缘0

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

2019(7-9月) 分析|数据科学类 硕士 全职@Paypal - 猎头 - 技术电面  | Other | 在职跳槽

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

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

x
第一輪SQL電面,如果過了就會給take home challenge
廢話不多說,直接分享題目:

游客,本帖隐藏的内容需要积分高于 150 才可浏览,您当前积分为 0。
查看如何攒积分 Click here for more info.


聽說PayPal現在有hiring freeze,不知道是不是全公司都freeze.... check 1point3acres for more.

评分

参与人数 7大米 +37 收起 理由
cici_ + 1 给你点个赞!
清道神君 + 30
zhongyujun222 + 1 很有用的信息!
elvirazyyan + 1 赞一个
Jackie2931 + 1 给你点个赞
cindy410xi + 1 给你点个赞!
yvonnewu1992 + 2 很有用的信息!

查看全部评分


上一篇:Facebook PXA组 DA 电面面筋 求加米!
下一篇:Facebook ds-analytics timeline, HR, 店面 & onsite 面经 ,求大米!!!

本帖被以下淘专辑推荐:

我的人缘0
cindy410xi 2019-8-9 05:31:00 | 显示全部楼层
本楼: 👍   100% (1)
 
 
0% (0)   👎
全局: 👍   100% (55)
 
 
0% (0)    👎
我的解答,不知道楼主是不是这么答的。。。
(1) Display the total transaction amount in Australia (country code = AU) in the month of Jan 2019.
SELECT SUM(Transaction_amount_USD) total_trans
FROM Fact_transaction t
JOIN Dim_customer c on t.Cust_id = c.Cust_id
Where c.Cust_country_code = ‘AU’ and Transaction_date between ‘2019-01-01’ and ‘2019-01-31’;

(2) Display the number of customers in Australia (country code = AU) who did at least $500 in volume (combining all tr​​ansactions) in Jan 2019.
Select count(t.Cust_id)
From (SELECT t.Cust_id, SUM(Transaction_amount_USD) jan_trans
FROM Fact_transaction t
JOIN Dim_customer c on t.Cust_id = c.Cust_id
Where c.Cust_country_code = ‘AU’ and Transaction_date between ‘2019-01-01’ and ‘2019-01-31’
Group by t.Cust_id
Having jan_trans >=500) temp;

(3) Display all the countries in which company XYZ has acquired customers along with the total transaction volume in each country in Jan 2019.  
--Jan 2019 may not have any transactions from certain countries. For those countries, display the value as 0.
Select Cust_country_code, sum(ifnull(Transaction_amount_USD, 0)) total_amt
FROM Dim_customer c
LEFT JOIN Fact_transaction t on t.Cust_id = c.Cust_id
WHERE Transaction_date between ‘2019-01-01’ and ‘2019-01-31’
GROUP BY Cust_country_code
Having sum(case when Cust_signup_date between ‘2019-01-01’ and ‘2019-01-31’ then 1 else 0 end) > 0;

(4) Display the first time each Australian customer transacted with PayPal, and the product they used during that first transaction. If some customers have not yet transacted, add '1970-01-01' as the date AND keep the first product value as null.
Select Cust_id, Transaction_date, Product_type
From (Select c.Cust_id, ifnull(Transaction_date, ‘1970-01-01’) as Transaction_date, Product_type, rank() over (partition by c.Cust_id order by Transaction_date) as date_rank
FROM Dim_customer c
LEFT JOIN Fact_transaction t on t.Cust_id = c.Cust_id
WHERE c.Cust_country_code = ‘AU’ and date_rank = 1) temp;
回复

使用道具 举报

我的人缘0
cindy410xi 2019-8-9 05:31:35 | 显示全部楼层
本楼: 👍   0% (0)
 
 
0% (0)   👎
全局: 👍   100% (55)
 
 
0% (0)    👎
求交流!紫薯紫薯
回复

使用道具 举报

我的人缘0
Borennn 2019-8-11 11:41:27 | 显示全部楼层
本楼: 👍   0% (0)
 
 
0% (0)   👎
全局: 👍   100% (17)
 
 
0% (0)    👎
(1) Display the total transaction amount in Australia (country code = AU) in the month of Jan 2019.

CREATE VIEW AU_201901 AS
SELECT trans.cust_id, trans.total_spend
FROM
  (
  SELECT cust_id, SUM(Transaction_amount_USD) total_spend
  FROM Transaction_id
  WHERE MONTH(Transaction_date) = 1 AND YEAR(Transaction_date) = 2019
  GROUP BY cust_id
  ) trans
JOIN Dim_customer cust
ON trans.cust_id = cust.cust_id AND
                cust.Cust_country_code == 'AU'
  
SELECT SUM(total_spend) total
FROM AU_201901

(2) Display the number of customers in Australia (country code = AU) who did at least $500 in volume (combining all tr​​ansactions) in Jan 2019.

SELECT COUNT(*)
FROM AU_201901
WHERE total_spend >= 500. From 1point 3acres bbs

(3) Display all the countries in which company XYZ has acquired customers along with the total transaction volume in each country in Jan 2019.  
--Jan 2019 may not have any transactions from certain countries. For those countries, display the value as 0.
SELECT c.Cust_country_code, SUM(COALESCE(total_spend, 0)) AS total_transaction
FROM
  (
  SELECT cust_id, Cust_country_code
  FROM Dim_customer
  WHERE Cust_signup_date BETWEEN '2019-01-01' AND '2019-01-31'
  ) c
LEFT JOIN
  (
  SELECT cust_id, SUM(Transaction_amount_USD) total_spend
  FROM Transaction_id
  WHERE Transaction_date BETWEEN '2019-01-01' AND '2019-01-31'
  GROUP BY cust_id
  ) t. From 1point 3acres bbs
ON c.cust_id = t.cust_id
GROUP BY c.Cust_country_code
  

(4) Display the first time each Australian customer transacted with PayPal, and the product they used during that first transaction.
If some customers have not yet transacted, add '1970-01-01' as the date AND keep the first product value as null.
CREATE VIEW AUS AS
SELECT t.Cust_id, t.Transaction_date, t.Product_type,
                                ROW_NUMBER() OVER(PARTITION BY t.Cust_id ORDER BY t.Transaction_date) rnb
FROM
(
  SELECT cust_id, Cust_country_code
  FROM Dim_customer
  WHERE Cust_country_code = 'AU'
)
JOIN Transaction_id t
ON c.cust_id = t.cust_id
       
SELECT Cust_id, Transaction_date, Product_type
FROM Aus
WHERE rnb = 1
. 1point3acres
回复

使用道具 举报

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

本版积分规则

提醒:发帖可以选择内容隐藏,部分板块支持匿名发帖。请认真读完以下全部说明:

■隐藏内容方法 - 不要多加空格: [hide=200]你想要隐藏的内容比如面经[/hide]
■意思是:用户积分低于200则看不到被隐藏的内容
■可以自行设置积分值,不建议太高(200以上太多人看不到),也不建议太低(那就没必要隐藏了)
■建议只隐藏关键内容,比如具体的面试题目、涉及隐私的信息,大部分内容没必要隐藏。
■微信/QQ/电子邮件等,为防止将来被骚扰甚至人肉,以论坛私信方式发给对方最安全。
■匿名发帖的板块和方法:http://www.1point3acres.com/bbs/thread-405991-1-1.html

手机版||一亩三分地

GMT+8, 2019-9-20 12:01

Powered by Discuz! X3

© 2001-2013 Comsenz Inc. Design By HUXTeam

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