Logistics · Intermediate
Join six tables of Kenyan marketplace orders to measure on-time delivery, find the carriers, sellers and routes behind delays, and link lateness to reviews.
Kilele Market is an online marketplace in Nairobi. About 80 sellers in Nairobi, Mombasa, Kisumu, Nakuru and Eldoret sell phones, fashion, groceries and more to customers in 26 counties, and five courier companies deliver the parcels. At checkout every customer is promised a delivery date, and after delivery they can leave a review from 1 to 5 stars. Low reviews are rising. The operations director thinks late deliveries, slow sellers and long routes are to blame, and wants evidence before renegotiating carrier contracts and seller service levels. You have two years of data (2023 and 2024) in six files: orders, order items, customers, sellers, carriers and reviews.
Your role
Supply Chain Data Analyst, Kilele Market operations team
Kilele Market promises a delivery date but does not track how often it keeps that promise, or who is responsible when it does not. Without that, it cannot hold carriers or sellers to account, and it is losing customers' goodwill through low reviews.
Look at all six tables and how they join. Count orders by status, then find the delivered orders whose delivery date is missing or earlier than the purchase date, and decide to leave them out of delivery measures.
Define on time as delivered on or before the promised date. Work out the overall on-time rate, the average and median days from order to delivery, and the on-time rate for every month.
Break lateness down by carrier, customer county, seller and route (seller city to customer county). Only rank groups with enough orders: 100 for counties and sellers, 50 for routes.
Join orders to reviews. Compare average scores for on-time and late orders, then by how many days late, and measure the share of 1 and 2 star reviews in each group.
Build a carrier scorecard (rate per parcel, on-time rate, review score, rank) and a monthly trend with LAG. Write a one-page memo with three recommendations for carrier contracts, seller service levels and promised dates.
Synthetic data, modelled on a real online marketplace's order, delivery and review records. Kilele Market and every shop, courier and person in it are fictional. It replaces the Brazilian Olist dataset used in the original brief, which we cannot host. Amounts are in Kenyan shillings (KES). Orders run from 1 January 2023 to 31 December 2024. logistics-orders.csv: 25,900 rows × 11 columns. One row is one order: customer, seller, carrier, status (delivered, cancelled or in_transit), purchase timestamp, ship-by date, dispatch date, promised delivery date, actual delivery date and delivery fee. logistics-order-items.csv: 36,476 rows × 5 columns. One row is one item line on an order: category, unit price and quantity. logistics-customers.csv: 6,734 rows × 5 columns. One row per customer: name, county, town and sign-up date. logistics-sellers.csv: 80 rows × 6 columns. One row per seller: shop name, city, county, main category and joining date. logistics-carriers.csv: 5 rows × 4 columns. One row per courier company: name, service type and contract rate per parcel. logistics-reviews.csv: 20,269 rows × 4 columns. One row per review (at most one per order): score from 1 to 5 and review date. How the tables join: orders.customer_id to customers, orders.seller_id to sellers, orders.carrier_id to carriers, and order_items and reviews to orders on order_id. Left for you to clean: a few delivered orders have no delivered_date, or one that is earlier than the purchase. Cancelled orders have no carrier, dispatch or delivery date; in-transit orders have not been delivered yet.
Open in SQL workspace
How to do this project
The scenario
Objectives
Tasks and business questions
Dataset and data dictionarylogistics_orders25,900 Kilele Market orders from 2023 and 2024 with carrier, status, purchase, dispatch, promised and delivered dates, and delivery fee in KES. · 25,900 rows
| Column | Type | Description | Example |
|---|---|---|---|
order_id | integer | Unique order number. | 100002 |
customer_id | text | Who placed the order; joins to logistics_customers.customer_id. | C00834 |
seller_id | text | The shop that sold it; joins to logistics_sellers.seller_id. | S055 |
carrier_id | text | The courier that delivered it; joins to logistics_carriers.carrier_id. Empty for cancelled orders. | CR03 |
order_status | text | delivered, cancelled or in_transit (not yet delivered when the data was taken on 31 December 2024). | delivered |
logistics_order_items36,476 item lines: one row per product line on an order, with category, unit price in KES and quantity. · 36,476 rows
| Column | Type | Description | Example |
|---|---|---|---|
order_id | integer | The order this line belongs to; joins to logistics_orders.order_id. | 100001 |
item_no | integer | Line number within the order, starting at 1. | 1 |
logistics_customers6,734 customers who placed at least one order, with county, town and sign-up date. · 6,734 rows
| Column | Type | Description | Example |
|---|---|---|---|
customer_id | text | Unique customer id. | C00001 |
customer_name | text | Customer's name (fictional). | Lucy Okoth |
logistics_sellers80 shops selling on Kilele Market, with city, county, main category and joining date. · 80 rows
| Column | Type | Description | Example |
|---|---|---|---|
seller_id | text | Unique seller id. | S001 |
seller_name | text | Shop name (fictional). | Kijani Mobile |
city |
logistics_carriersThe five courier companies Kilele Market uses, with service type and contract rate per parcel in KES. · 5 rows
| Column | Type | Description | Example |
|---|---|---|---|
carrier_id | text | Unique carrier id. | CR03 |
carrier_name | text | Courier company name (fictional). | Tembo Freight |
logistics_reviews20,269 customer reviews, at most one per delivered order, with a score from 1 to 5. · 20,269 rows
| Column | Type | Description | Example |
|---|---|---|---|
review_id | text | Unique review id. | R000001 |
order_id | integer | The order being reviewed; joins to logistics_orders.order_id. | 100004 |
15 exercises worth 245 points. Your query runs against this project’s dataset and is marked automatically.
The exercises are marked automatically. The memo is marked on whether on time and late are defined once and used the same way everywhere, whether rankings use a minimum number of orders, and whether each recommendation for carriers and sellers cites a figure from your queries.
Recommended tools
Skills covered
purchased_at | timestamp | Date and time the customer placed the order. | 2023-01-01 13:17:52 |
ship_by_date | date | Date by which the seller must hand the parcel to the carrier (two days after purchase). | 2023-01-03 |
dispatched_date | date | Date the seller handed the parcel to the carrier. Empty for cancelled orders. | 2023-01-02 |
promised_date | date | Delivery date promised to the customer at checkout. | 2023-01-07 |
delivered_date | date | Date the customer received the parcel. Empty if not delivered, and missing or wrong for a few delivered orders. | 2023-01-07 |
delivery_fee_kes | integer | Delivery fee the customer paid, in Kenyan shillings. | 450 |
product_category | text | Product category, for example Fashion or Phones & Accessories. | Beauty & Personal Care |
unit_price_kes | integer | Price of one unit in Kenyan shillings. | 2200 |
quantity | integer | Number of units on the line. | 1 |
All graded SQL exercises passed in the SQL workspacecounty| text |
| County the customer's orders are delivered to. |
Nairobi |
town | text | Main delivery town in that county. | Nairobi |
signup_date | date | Date the customer opened their account. | 2022-11-18 |
| text |
| City the shop ships from: Nairobi, Mombasa, Kisumu, Nakuru or Eldoret. |
Nairobi |
county | text | County of that city (Eldoret is in Uasin Gishu). | Nairobi |
main_category | text | The category most of the shop's items belong to. | Phones & Accessories |
joined_date | date | Date the shop joined the marketplace. | 2021-12-30 |
service_type| text |
| What kind of delivery service it runs. |
National road courier (economy) |
rate_per_parcel_kes | integer | What Kilele Market pays the carrier per parcel, in Kenyan shillings. | 230 |
review_score |
| integer |
| Stars from 1 (very unhappy) to 5 (very happy). |
5 |
review_date | date | Date the review was left. | 2023-01-03 |