262. Trips And Users

Tutd Schema:

data Status = Completed | CancelledByDriver | CancelledByClient;
trips := relation{id Integer, client_id Integer, driver_id Integer, city_id Integer, status Status, request_at Day}{
  tuple{id 1, client_id 1, driver_id 10, city_id 1, status Completed, request_at fromGregorian(2013,10,1)},
  tuple{id 2, client_id 2, driver_id 11, city_id 1, status CancelledByDriver, request_at fromGregorian(2013,10,1)},
  tuple{id 3, client_id 3, driver_id 12, city_id 6, status Completed, request_at fromGregorian(2013,10,1)},
  tuple{id 4, client_id 4, driver_id 13, city_id 6, status CancelledByClient, request_at fromGregorian(2013,10,1)},
  tuple{id 5, client_id 1, driver_id 10, city_id 1, status Completed, request_at fromGregorian(2013,10,2)},
  tuple{id 6, client_id 2, driver_id 11, city_id 6, status Completed, request_at fromGregorian(2013,10,2)},
  tuple{id 7, client_id 3, driver_id 12, city_id 6, status Completed, request_at fromGregorian(2013,10,2)},
  tuple{id 8, client_id 2, driver_id 12, city_id 12, status Completed, request_at fromGregorian(2013,10,3)},
  tuple{id 9, client_id 3, driver_id 10, city_id 12, status Completed, request_at fromGregorian(2013,10,3)},
  tuple{id 10, client_id 4, driver_id 13, city_id 12, status CancelledByDriver, request_at fromGregorian(2013,10,3)}};
data Role = Client | Driver | Partner;
data Banned = Yes | No;
user := relation{user_id Integer, banned Banned, role Role}{
  tuple{ user_id 1, banned No, role Client},
  tuple{ user_id 2, banned Yes, role Client},
  tuple{ user_id 3, banned No, role Client},
  tuple{ user_id 4, banned No, role Client},
  tuple{ user_id 10, banned No, role Driver},
  tuple{ user_id 11, banned No, role Driver},
  tuple{ user_id 12, banned No, role Driver},
  tuple{ user_id 13, banned No, role Driver}};
 

Thinking Process:

-- find unbanned_users

unbanned_users := (user where banned = No){user_id}

-- Both client and driver must be unbanned_users. So I cross-product the unbanned_users.

pair := (((unbanned_users:{tmp := true}) rename {user_id as client_id}) join ((unbanned_users:{tmp := true}) rename {user_id as driver_id})){a
ll but tmp}

-- join with trips and we found trips that clients and drivers both are unbanned users. 

:showexpr (trips join pair) group ({all but request_at} as perDay)
┌────────────────────────────────────────────────────────────────────────────────────────────────────┬───────────────┐
│perDay::relation {status::Status,city_id::Integer,client_id::Integer,driver_id::Integer,id::Integer}│request_at::Day│
├────────────────────────────────────────────────────────────────────────────────────────────────────┼───────────────┤
│┌────────────────┬──────────────────┬──────────────────┬───────────┬──────────────┐                 │2013-10-02     │
││city_id::Integer│client_id::Integer│driver_id::Integer│id::Integer│status::Status│                 │               │
│├────────────────┼──────────────────┼──────────────────┼───────────┼──────────────┤                 │               │
││6               │3                 │12                │7          │Completed     │                 │               │
││1               │1                 │10                │5          │Completed     │                 │               │
│└────────────────┴──────────────────┴──────────────────┴───────────┴──────────────┘                 │               │
│┌────────────────┬──────────────────┬──────────────────┬───────────┬─────────────────┐              │2013-10-03     │
││city_id::Integer│client_id::Integer│driver_id::Integer│id::Integer│status::Status   │              │               │
│├────────────────┼──────────────────┼──────────────────┼───────────┼─────────────────┤              │               │
││12              │4                 │13                │10         │CancelledByDriver│              │               │
││12              │3                 │10                │9          │Completed        │              │               │
│└────────────────┴──────────────────┴──────────────────┴───────────┴─────────────────┘              │               │
│┌────────────────┬──────────────────┬──────────────────┬───────────┬─────────────────┐              │2013-10-01     │
││city_id::Integer│client_id::Integer│driver_id::Integer│id::Integer│status::Status   │              │               │
│├────────────────┼──────────────────┼──────────────────┼───────────┼─────────────────┤              │               │
││6               │3                 │12                │3          │Completed        │              │               │
││1               │1                 │10                │1          │Completed        │              │               │
││6               │4                 │13                │4          │CancelledByClient│              │               │
│└────────────────┴──────────────────┴──────────────────┴───────────┴─────────────────┘              │               │
└────────────────────────────────────────────────────────────────────────────────────────────────────┴───────────────┘

-- the rest of it is to use a div to create rates.