数据分析师 SQL 笔试面试题 汇总【二】

7，Leetcode 1204题 最后一个能进入电梯的人

这道题非常有意思，主要是想要考察如何判断体重总量不超过1000.一共有三种思路，第一种是变量法（相当于递归）；第二种是自连接（谨记：对表自身的运算，就是递归，自连接永远都是一种办法；第三种是窗口函数，这个我之前不清楚可以这么用。

#解法1：自定义变量
select 
    person_name        
from 
(
    select
        person_name,      
        @sum_weight:=@sum_weight+weight as sum_weight    
    from Queue q,(select @sum_weight:=0)s 
    order by turn asc
)s1 
where sum_weight<=1000 
order by sum_weight desc 
limit 1


#解法2：自连接
SELECT a.person_name
FROM Queue a, Queue b
WHERE a.turn >= b.turn
GROUP BY a.person_id 
HAVING SUM(b.weight) <= 1000
ORDER BY a.turn DESC
LIMIT 1

#解法3：窗口函数
select person_name 
from
(
    select person_name,turn,sum(weight) over(order by turn) as addup_weight
    from Queue a
) t where addup_weight<=1000
order by turn desc
LIMIT 1

8, Leetcode  1205 每月交易2

-- 第一步 把trans_date统一改成年-月形式
-- 第二步 按照地区月份统计approved的数量和总额
-- 第三步 连接两个表，求出chargeback中的transID对应的amount是什么
with transactions1 as(
    select *,date_format(trans_date, '%Y-%m') AS month
    from transactions
),
chargebacks1 as(
    select *,date_format(trans_date, '%Y-%m') AS month
    from chargebacks
)


select month, country,count(*) as approved_count,sum(amount) as approved_amount
from transactions1
where state = 'approved'
group by month,country

select c1.month, country,
count(trans_id) as chargeback_count,
sum(amount) as chargeback_amount
from transactions1 as t1
right join 
chargebacks1 as c1
on t1.id = c1.trans_id
group by c1.month,country




---------------------------


select 
    date_format(trans_date, '%Y-%m') month, 
    country,
    sum(state = 'approved') approved_count,
    sum(if(state = 'approved', amount, 0)) approved_amount,
    sum(state = 'chargeback') chargeback_count,
    sum(if(state = 'chargeback', amount, 0)) chargeback_amount
from (
    select * from transactions
    union all
    select id, country, 'chargeback' state, amount, c.trans_date
    from chargebacks c left join transactions t 
    on c.trans_id = t.id
) tmp 
group by month, country
having approved_amount or chargeback_amount

注意需要group by两个列

9, Leetcode 1098 小众书籍

这道题需要注意的是：

1，orders表明显缺少books表中的部分book_id，而这又是需要的，所以一定要books left join orders，否则会缺失。

2，ifnull(sum(quantity),0) ==> 如果不能存在quantity，就变成0

select b.book_id, name
from books b left join orders o
on b.book_id = o.book_id and dispatch_date >= '2018-06-23'
where available_from < '2019-05-23'
group by b.book_id
having ifnull(sum(quantity), 0) < 10

SELECT b.book_id, b.name
FROM (
    SELECT book_id, name
    FROM books
    WHERE DATEDIFF('2019-06-23',available_from) >= 30
) b LEFT JOIN (
    SELECT book_id, quantity
    FROM orders
    WHERE DATEDIFF('2019-06-23',dispatch_date) <= 365
) o ON b.book_id=o.book_id
GROUP BY b.book_id
HAVING SUM(IFNULL(quantity,0))<10

AND dispatch_date BETWEEN DATE_ADD('2019-06-23', INTERVAL -1 YEAR) AND '2019-06-23' 
WHERE a.available_from <= DATE_ADD('2019-06-23',INTERVAL -1 MONTH) 

10, leetcode 1083 销售分析

select distinct s.buyer_id
from product p, sales s
where p.product_id = s.product_id
and s.buyer_id not in
(select s.buyer_id
from product p, sales s
where p.product_id = s.product_id
and p.product_name = 'iphone')
and p.product_name = 'S8'

select s.buyer_id 
from sales as s left join product as p 
on s.product_id=p.product_id
group by buyer_id
having sum(p.product_name='S8')>0 and sum(p.product_name='iPhone')=0

11, Leetcode 1264 页面推荐

select distinct page_id as recommended_page
from likes
where (user_id in (
    select user2_id
    from friendship
    where user1_id = 1
)
OR user_id in (
    select user1_id
    from friendship
    where user2_id = 1
))
and page_id not in (
    select page_id
    from likes
    where user_id = 1
)

这是第一种，最常规的做法。必须要注意的是，当and和or一起用的时候

SELECT DISTINCT page_id AS recommended_page
FROM Likes
WHERE user_id IN (
    SELECT user1_id AS user_id FROM Friendship WHERE user2_id = 1
    UNION ALL
    SELECT user2_id AS user_id FROM Friendship WHERE user1_id = 1
) AND page_id NOT IN (
    SELECT page_id FROM Likes WHERE user_id = 1
)

这是第二种，union all不同于union会去掉重复的内容