FB DS电面面经

Select big.advertiser_id, count(big.user_id) as num from
(Select advertiser.advertiser_id, advertiser.ad_id, user.user_id, user.price from advertiser left join user on (user.ad_id = advertiser.ad_id)) big
where big.price > 0 or big.price is null [some ads have no customers]
Group by big.advertiser_id
having num > 0

ROI: by advertiser
Select revenue.adertiser_id, (revenue.revenue-cost.cost)/cost.cost as ROI from
(
Select sub1.advertiser_id, sum(ifnull(user.price, 0)) as revenue from
        (Select a1.advertiser_id, a1.ad_id, u1.user_id, u1.price
        from advertiser a1
        left join user u1 on (user.ad_id = advertiser.ad_id)) sub1
) revenue
Inner join
(
Select a2.advertiser_id, sum(a2.spend) as cost from advertiser a2 group by a2.advertiser_id
) cost
On cost.advertiser_id = revenue.advertiser_id

我觉得这题可以两个subquery也可以直接写。。。
我的习惯都是写subquery思路清晰一些，但是subquery并不efficient
请问大家，这些大公司的DS面试要是写的不efficient会减分吗？？

(1) Ads whose ad_id don't appear in the user table is not converted. We can use INNER JOIN here.

1. SELECT (COUNT DISTINCT advertiser_id) AS num_advertiser
   
2. FROM
   
3. advertiser
   
4. INNER JOIN user
   
5. ON advertiser.ad_id = user.ad_id

复制代码

(2) Two cases here:
No.1: ROI on every ad_id

1. SELECT advertiser_id, ad_id, SUM(IFNULL(price,0)) / spend
   
2. FROM advertiser
   
3. LEFT JOIN user
   
4. ON advertiser.ad_id = user.ad_id
   
5. GROUP BY advertiser_id, ad_id

复制代码

No.2: ROI for every advertiser_id

1. SELECT advertiser_id, SUM(IFNULL(rev,0)) / SUM(spend) AS ROI
   
2. FROM
   
3. advertiser
   
4. LEFT JOIN
   
5. (SELECT ad_id, SUM(price) AS rev
   
6. FROM user
   
7. GROUP BY ad_id) tbl1
   
8. ON advertiser.ad_id = tbl1.ad_id
   
9. GROUP BY advertiser_id

复制代码