Wayfair OA 两道SQL

rongy2018 发表于 2019-4-21 05:37
赶汽车的那道题， 不知道这么写对不对？
with best_catch as (
        select p.id as passenger_id,  ...

我觉得这是最优解

duck的题后边没有截图看不到了 :(

鸭子那道题截图好像题目不完整，看不到需要积分部分，是我漏了什么吗？

bus:
SELECT id, ISNULL(Passengers_on_board,0) as Passengers_on_board FROM buses
LEFT JOIN
        (SELECT Bus_id, COUNT(*) Passengers_on_board
        FROM
                (SELECT Passenger_id, Bus_id
                FROM
                        (SELECT *, RANK()OVER(PARTITION BY Passenger_id ORDER BY Bus_time) Rk_num
                        FROM
                                (SELECT P.id as Passenger_id, P.origin, P.destination, P.time as P_time, B.time as Bus_time, B.id as Bus_id
                                FROM passengers P JOIN buses B
                                ON P.origin=B.origin AND P.destination=B.destination AND B.time>=P.time) a
                        )b
                WHERE Rk_num=1) c
        GROUP BY Bus_id) d
ON buses.id = d.Bus_id

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