一道SQL面试题

显示全部楼层 · 2012-5-21 10:19:41

本帖最后由〇〇于 2016-9-5 20:03 编辑
https://leetcode.com/problems/trips-and-users/
262. Trips and UsersPlease login first. Question Editorial Solution My Submissions · Total Accepted: 5702· Total Submissions: 36966· Difficulty: HardThe Trips table holds all taxi trips. Each trip has a unique Id, while Client_Id and Driver_Id are both foreign keys to the Users_Id at the Users table. Status is an ENUM type of (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’).+----+-----------+-----------+---------+--------------------+----------+| Id | Client_Id | Driver_Id | City_Id |Status|Request_at|+----+-----------+-----------+---------+--------------------+----------+| 1| 1 |10 |1| completed|2013-10-01|| 2| 2 |11 |1| cancelled_by_driver|2013-10-01|| 3| 3 |12 |6| completed|2013-10-01|| 4| 4 |13 |6| cancelled_by_client|2013-10-01|| 5| 1 |10 |1| completed|2013-10-02|| 6| 2 |11 |6| completed|2013-10-02|| 7| 3 |12 |6| completed|2013-10-02|| 8| 2 |12 |12 | completed|2013-10-03|| 9| 3 |10 |12 | completed|2013-10-03| | 10 | 4 |13 |12 | cancelled_by_driver|2013-10-03|+----+-----------+-----------+---------+--------------------+----------+The Users table holds all users. Each user has an unique Users_Id, and Role is an ENUM type of (‘client’, ‘driver’, ‘partner’).+----------+--------+--------+| Users_Id | Banned |Role|+----------+--------+--------+|1 | No | client ||2 | Yes| client ||3 | No | client ||4 | No | client ||10| No | driver ||11| No | driver ||12| No | driver ||13| No | driver |+----------+--------+--------+Write a SQL query to find the cancellation rate of requests made by unbanned clients between Oct 1, 2013 and Oct 3, 2013. For the above tables, your SQL query should return the following rows with the cancellation rate being rounded to two decimal places.+------------+-------------------+| Day| Cancellation Rate |+------------+-------------------+| 2013-10-01 | 0.33|| 2013-10-02 | 0.00|| 2013-10-03 | 0.50|+------------+-------------------+Credits:
Special thanks to @cak1erlizhou for contributing this question, writing the problem description and adding part of the test cases.

千问 · 2012-5-21 10:19:41

SQL> create table trip(id int,client_id int,driver_id int,city_id int,status varchar2(30),request_at date);
Table created
SQL>
SQL> insert into trip values(1, 1 , 10 ,1,trim(' completed'), date '2013-10-01');
1 row inserted
SQL> insert into trip values(2, 2 , 11 ,1,trim(' cancelled_by_driver'), date '2013-10-01');
1 row inserted
SQL> insert into trip values(3, 3 , 12 ,6,trim(' completed'), date '2013-10-01');
1 row inserted
SQL> insert into trip values(4, 4 , 13 ,6,trim(' cancelled_by_client'), date '2013-10-01');
1 row inserted
SQL> insert into trip values(5, 1 , 10 ,1,trim(' completed'), date '2013-10-02');
1 row inserted
SQL> insert into trip values(6, 2 , 11 ,6,trim(' completed'), date '2013-10-02');
1 row inserted
SQL> insert into trip values(7, 3 , 12 ,6,trim(' completed'), date '2013-10-02');
1 row inserted
SQL> insert into trip values(8, 2 , 12 ,12 ,trim(' completed'), date '2013-10-03');
1 row inserted
SQL> insert into trip values(9, 3 , 10 ,12 ,trim(' completed'), date '2013-10-03');
1 row inserted
SQL> insert into trip values(10 , 4 , 13 ,12 ,trim(' cancelled_by_driver'), date '2013-10-03');
1 row inserted
SQL> commit;
Commit complete
SQL> create table usr(user_id int,banned varchar2(3),role varchar2(10));
Table created
SQL>
SQL> insert into usr values (1 ,'No' ,'client');
1 row inserted
SQL> insert into usr values (2 ,'Yes','client');
1 row inserted
SQL> insert into usr values (3 ,'No' ,'client');
1 row inserted
SQL> insert into usr values (4 ,'No' ,'client');
1 row inserted
SQL> insert into usr values (10,'No' ,'driver');
1 row inserted
SQL> insert into usr values (11,'No' ,'driver');
1 row inserted
SQL> insert into usr values (12,'No' ,'driver');
1 row inserted
SQL> insert into usr values (13,'No' ,'driver');
1 row inserted
SQL> commit;
Commit complete
SQL>
SQL> select trunc(t.request_at) as "Day",
2 sum(case when status'completed' then 1 else 0 end)/count(*) "Cancellation Rate"
3from trip t
4 where t.request_at between to_date('20131001','yyyymmdd') and to_date('20131003','yyyymmdd')
5 and t.client_id in (select user_id from usr where banned='No' )
6 and t.driver_id in (select user_id from usr where banned='No' )
7group by trunc(t.request_at)
8order by 1;
Day Cancellation Rate
----------- -----------------
2013/10/1 0.333333333333333
2013/10/2
0
2013/10/3
0.5
SQL>

千问 · 2012-5-21 10:19:41

SQL> select trunc(t.request_at) as "Day",
2 round(sum(case when status'completed' then 1 else 0 end)/count(*),2) "Cancellation Rate"
3from trip t
4 where t.request_at between to_date('20131001','yyyymmdd') and to_date('20131003','yyyymmdd')
5 and t.client_id in (select user_id from usr where banned='No' )
6 and t.driver_id in (select user_id from usr where banned='No' )
7group by trunc(t.request_at)
8order by 1;
Day Cancellation Rate
----------- -----------------
2013/10/1
0.33
2013/10/2
0
2013/10/3
0.5

千问 · 2012-5-21 10:19:41

看不出有什么难的？

千问 · 2012-5-21 10:19:41

newkid 发表于 2016-9-6 00:05
看不出有什么难的？
作为面试题,可以了

千问 · 2012-5-21 10:19:41

solomon_007 发表于 2016-9-5 22:30
SQL> select trunc(t.request_at) as "Day",
2 round(sum(case when status'completed' then...
and t.driver_id in (select user_id from usr where banned='No' )
,这个应该不用加.

千问 · 2012-5-21 10:19:41

dhhb 发表于 2016-9-6 10:21
and t.driver_id in (select user_id from usr where banned='No' )
,这个应该不用加.
根据TRIP表的最后一行数据，cancelled_by_driver的也算进去了，所以觉得应该加上，虽然例题没有多的数据不加也不影响结果

千问 · 2012-5-21 10:19:41

solomon_007 发表于 2016-9-6 10:46
根据TRIP表的最后一行数据，cancelled_by_driver的也算进去了，所以觉得应该加上，虽然例题没有多的数据 ...
| 8| 2 |12 |12 | completed|2013-10-03|
应该是剔除这一条， client 2是banned.