Uber Data Analysis Challenge

hacknobody

                               

SQL TEST:

Assume a PostgreSQL database, server timezone is UTC.

8 d9 t6 M: \8 b4 q- W" R$ b+ x

Table Name: trips

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

  p4 z7 ^! [9 {8 |0 v

Table Name: users

6 s9 k! X1 T. y1 }

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

! g( ^- w8 q5 v) u6 u
8 M. m2 g' |2 O/ W

• 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?
  
  4 ~0 |# l  U6 f3 [4 K

moonshell826

就三道SQL题？ 感觉略简单不真实啊。。。

whitebear

我也考到了这套题