shipzero

README.md

Task 1

From our clients we receive two files and imported the to a database:

1. vehicles.csv → a list of trucks
2. Trackings.csv → vehicle data with timestamp ("time") and position ("lat", "lng") • Consider and treat these files as if they were tables • Both tables can be matched via the vehicle_id. Please answer the following questions via a SQL script:
3. Which vehicles are active/inactive (in the sense of having tracking data at all)? Result table with two columns: vehicle_id, active_Boolean

SELECT v.vehicle_id,
       CASE 
           WHEN t.vehicle_id IS NOT NULL THEN TRUE
           ELSE FALSE
       END AS active_Boolean
FROM vehicles v
LEFT JOIN trackings t ON v.vehicle_id = t.vehicle_id
GROUP BY v.vehicle_id;

2. Which vehicle has the highest number of individual tracking points? Result (single row) with two columns: vehicle_id, number_of_tracking_points

SELECT t.vehicle_id, COUNT(*) AS number_of_tracking_points
FROM trackings t
GROUP BY t.vehicle_id
ORDER BY number_of_tracking_points DESC
LIMIT 1;

Task 2

Based on our data we want to provide our clients with a new dashboard that visualizes their main country to country as well as city to city connections.

Imagine you are in charge of this task:

• Which API endpointss would you create in the backend?

/api/tradelanes

{
  "tradelanes": [
    {
      "id": "0",
      "level": "city",
      "location_dep": "Hamburg",
      "location_arr": "Shanghai",
      "monthly_weight": 600,
      "occurrences_per_month": 6,
      "mode_of_transport": "sea",
      "contractor_list": ["Contractor A"],
      "customer_list": ["Customer X", "Customer Y"]
    },
    {
      "id": "1",
      "level": "country",
      "location_dep": "Germany",
      "location_arr": "China",
      "monthly_weight": 2000,
      "occurrences_per_month": 15,
      "mode_of_transport": "sea",
      "contractor_list": ["Contractor A"],
      "customer_list": ["Customer X", "Customer Y"]
    },
  ]
}

/api/tradelanes/{id}

{
  "id": 0,
  "location_dep": "Hamburg",
  "location_arr": "Shanghai",
  "monthly_data": [
    {"month": "2024-01", "weight": 500, "occurrences": 5},
    {"month": "2024-02", "weight": 700, "occurrences": 7}
  ],
  "contractor_list": ["Contractor A"],
  "customer_list": ["Customer X", "Customer Y"]
}

/api/locations

{
  "countries": ["Germany", "China"],
  "cities": [
    {"city": "Hamburg", "country": "Germany"},
    {"city": "Shanghai", "country": "China"}
  ]
}

/api/dashboard/settings

{
  "available_filters": {
    "transport_modes": ["sea", "air", "road"],
    "countries": ["Germany", "China"]
  }
}

• How would you structure your frontend?

Country Level Dashboard View:

• A map that shows the country-to-country tradelines.
• Data: /api/tradelines?level=country
• UI: Filter panel, Map, Button to switch to city-level view.

City Level Dashboard View:

• A map that shows the city-to-city tradelines.
• Data: /api/tradelines?level=city
• UI: Filter panel, Map, Button to switch to country-level view.

Tradeline Detail View

• Shows weights, contractor/customer details for a specific tradeline.

• Data: /api/tradelines/{id}

• UI: Tables of contractors & customers

• In which steps/ commits would you implement the project?

Step 1: Initial setup FE (e.g. React) and backend (e.g. FastAPI) Step 2: Implement /api/tradelanes and /api/locations Step 3: Implement country-to-country visualisation Step 4: Implement city-to-city visualization Step 5: Implement /api/tradelines/{id} and Tradeline Detail View