Wayfair OA 两道SQL

60分钟两道SQL，看地理没有题目的

您好！
本帖隐藏的内容需要积分高于 188 才可浏览
您当前积分为 0。
使用VIP即刻解锁阅读权限或查看其他获取积分的方式

游客，您好！
本帖隐藏的内容需要积分高于 188 才可浏览
您当前积分为 0。
VIP即刻解锁阅读权限 或 查看其他获取积分的方式
Unlock interview details and practice with AI
Curated Interview Questions from Top Companies

vivianinus 发表于 2019-4-15 23:04
公车：
WITH on_board AS (
    SELECT p.id  AS p_id, MIN(CAST(b.time AS TIME)) AS b_time

赶汽车的那道题， 不知道这么写对不对？
with best_catch as (
        select p.id as passenger_id, b.id as bus_id, row_number() over(partition by p.id order by b.time) as row_num
        from passengers p
        right  join buses b
        on p. origin=b.origin and p.destination =b.destination and p.time<=b.time
)
select bus_id, coalesce(count(passenger_id) ,0)
from best_catch
where row_num=1 or p. id is null
group by 1

vivianinus 发表于 2019-4-15 07:04
公车：
WITH on_board AS (
    SELECT p.id  AS p_id, MIN(CAST(b.time AS TIME)) AS b_time

Happy Duck:

SELECT D.pond_id, SUM(CASE WHEN ((temp_preference='+' AND temperature>temp_limit) OR
                                                                                                  (temp_preference='-' AND temperature<temp_limit) ) THEN 1 ELSE 0
                                                                                                                                                                 END) as Happy_ducks
        FROM ducks D JOIN species S
        ON D.specied_id=S.id
        JOIN ponds P
        ON P.id=D.pond_id
GROUP BY pond_id
ORDER BY pond_id

you are welcome
drop table buses, passengers;
create table buses (bus_id int primary key, origin varchar not null, destination varchar not null, time varchar not null, unique(origin, destination, time));                               
INSERT INTO buses (bus_id, origin, destination, time) VALUES
(10, 'W', 'B', '10:55'),
(20, 'B', 'P', '06:20'),
(21, 'B', 'P', '14:00'),
(22, 'B', 'P', '21:40'),
(30, 'P', 'M', '13:30');

create table passengers (pass_id int primary key, origin varchar not null, destination varchar not null, time varchar not null);                               
INSERT INTO passengers (pass_id, origin, destination, time) VALUES
(1, 'P', 'M', '13:30'),
(2, 'P', 'M', '13:31'),
(10, 'W', 'P', '10:00'),
(11, 'W', 'B', '22:31'),
(40, 'B', 'P', '06:15'),
(41, 'B', 'P', '06:50'),
(42, 'B', 'P', '07:12'),
(43, 'B', 'P', '12:03'),
(44, 'B', 'P', '20:00');
select b.id, count(p.id) from buses b join passengers p on p.origin = b.origin and p.destination = b.destination and p.time <= b.time group by b.id order by b.id

-- 对每一个passager来说，最小的bus arrival time 就是他要乘的
with tmp as (
select p.pass_id, min(b.time) as boarding_time
from buses b join passengers p on p.origin = b.origin and p.destination = b.destination and p.time <= b.time
group by p.pass_id
order by p.pass_id)

select b.bus_id as id, count(pass_id)
from buses b
left join tmp
on b.time = tmp.boarding_time
group by id
order by id

感谢楼主！这两道是不是都是用case做出来的啊？

求分享duck的解法~~谢谢！

已注销用户-4990 发表于 2019/03/09 07:03:21
求分享duck的解法~~谢谢！

好的呢 等我晚上做了发哦

那个等车的题有谁能做出来呀…… 求思路 不知道怎么解决时间最近的那班车的问题

公车：
WITH on_board AS (
    SELECT p.id  AS p_id, MIN(CAST(b.time AS TIME)) AS b_time
    FROM passengers p
    LEFT JOIN buses b ON b.origin = p.origin AND b.destination = p.destination
        AND CAST(b.time AS TIME) >= CAST(p.time AS TIME)
    GROUP BY p.id
)
SELECT b.id, COUNT(b_time)
FROM on_board ob
LEFT JOIN passengers p ON ob.p_id = p.id
LEFT JOIN buses b ON (p.origin = b.origin AND p.destination = b.destination)
    AND (ob.b_time = CAST(b.time AS TIME) OR b_time IS NULL)
GROUP BY b.id
ORDER BY b.id

ducks：
SELECT p.id AS pond_id,
    SUM(CASE WHEN s.temp_preferences = '+' AND
                  p.temperature >= s.temp_limit THEN 1
             WHEN s.temp_preferences = '-' AND
                  p.temperature <= s.temp_limit THEN 1
             ELSE 0 END) AS happy_ducks
from ponds p
LEFT JOIN ducks d ON d.pond_id = p.id
LEFT JOIN species s ON s.id = d.species_id
GROUP BY p.id
ORDER BY p.id

楼主申请的是什么职位啊？

楼主可以分享一下公车的解法？谢谢哈

select b1.id, count(p1.id) as passengers_on_board
from buses b1 left join (
select p.id, min(b.time) as earliest_time
from buses b left join passengers p on b.origin=p.origin and b.destination=p.destination and b.time>=p.time
                        group by 1
) p_b
        on b1.time=p_b.earliest_time
left join passengers p1 on p1.id=p_b.id
group by 1
order by 1;