亲！马上注册或者登录会查看更多内容！
您需要 登录 才可以下载或查看，没有帐号？立即注册
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 | 
发表于 11-14-2014 11:37 AM
发表于 1-8-2015 01:10 AM
