亲!马上注册或者登录会查看更多内容!
您需要 登录 才可以下载或查看,没有帐号?立即注册
x
SQL TEST: ' n9 l, z ~$ t3 o- h
Assume a PostgreSQL database, server timezone is UTC. - Z$ x: w. ^) a9 K6 y
Table Name: trips ' T5 k P6 d2 C
Column Name | Datatype: | id | integer | client_id | integer (Foreign keyed to users.usersid) | driver_id | integer (Foreign keyed to users.usersid) | city_id | integer | client_rating | integer | driver_rating | integer | request_device | Enum(‘android’, ‘iphone’, ‘sms’, ‘mobile_web’) | status | Enum(‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’) | predicted_eta | integer | actual_eta | integer | request_at | timestamp with timezone | 5 f) j# U! b- F2 f7 E' _
Table Name: users
. l+ |; J# p/ j# n! E$ h6 Y) n Column Name: | Datatype: | usersid | integer | email | character varying | firstname | character varying | lastname | character varying | banned | Boolean | role | Enum(‘client’, ‘driver’, ‘partner’) | creationtime | timestamp with time zone | * K6 {; O+ i" J" y) B
, {0 A' L3 L. ]0 e
For request times between 12/1/2013 10:00:00 PST & 12/8/2013 17:00:00 PST, how many completed trips (Hint: look at the trips.status column) were requested on iphones in City #5? on android phones? In City #8, how many unique, currently unbanned clients requested a trip in October 2013 that was eventually completed? Of these, how many trips did each client take? In City #8, how many unique, currently unbanned clients requested a trip between 9/10/2013 and 9/20/2013, with drivers who started between 9/1/2013 and 9/10/2013 and are currently banned, that was eventually completed?
6 j7 t2 B' ~; j2 j3 _( n
3 S: U( M! r: K0 p4 n
$ p; M' I* O$ B7 N# s: ~' F$ O5 J: ]1 |
% N e1 j4 S w, a* p |