Omid - Challenge 103

data-challenges
advanced-exercises
🔰 Average Today is 20/08/2024, and we’re calculating the average delivery time per product in days.
Published

March 24, 2026

Illustration for Omid - Challenge 103

Challenge Description

🔰 Average Today is 20/08/2024, and we’re calculating the average delivery time per product in days.

Solutions

library(tidyverse)
library(readxl)

path = "files/CH-103 Custom Average.xlsx"
input = read_excel(path, range = "B2:D21")
test = read_excel(path, range = "I2:J6")

today = as.POSIXct(as.Date("2024-08-20"))

result = input %>%
  mutate(del_time = as.numeric(`Delivery Date` - `Order Date`),
         adj_del_time = ifelse(is.na(`Delivery Date`), today - `Order Date`, del_time)) %>%
  mutate(avg_del_time = mean(del_time, na.rm = T), .by = `Product ID`) %>%
  filter(!is.na(del_time) | adj_del_time >= avg_del_time) %>%
  summarise(`Avg delivery time` = mean(adj_del_time, na.rm = T), .by = `Product ID`) %>%
  arrange(`Product ID`)

identical(result$`Avg delivery time`, test$`Avg delivery time`)
#> [1] TRUE

  • Logic:

    • Reads the workbook ranges needed for the challenge

    • Aggregates or ranks values at the relevant grouping level

    • Builds the intermediate columns that drive the final result

  • Strengths:

    • The R solution stays close to the workbook rule and keeps the transformation compact.

  • Areas for Improvement:

    • The code assumes the sheet structure and source ranges remain stable.

  • Gem:

    • The strongest part of the solution is choosing the right intermediate representation before shaping the final output.
import pandas as pd
import numpy as np

path = "CH-103 Custom Average.xlsx"
input = pd.read_excel(path, usecols="B:D", skiprows = 1, nrows = 19)
test = pd.read_excel(path, usecols="I:J", skiprows = 1, nrows = 4)

today = pd.to_datetime("2024-08-20")

input["Delivery Time"] = (input["Delivery Date"] - input["Order Date"]).dt.days
input["Adjusted Delivery Time"] = np.where(input["Delivery Date"].isnull(), (today - input["Order Date"]).dt.days, input["Delivery Time"])
input = input[input["Delivery Time"].notnull() | (input["Adjusted Delivery Time"] > input.groupby("Product ID")["Delivery Time"].transform("mean"))]\
    .groupby("Product ID")["Adjusted Delivery Time"].mean().reset_index()

print(input["Adjusted Delivery Time"].equals(test["Avg delivery time"])) # True

  • Logic:

    • Reads the workbook ranges needed for the challenge

    • Aggregates or ranks values at the relevant grouping level

  • Strengths:

    • The Python version follows the same rule in a direct dataframe-oriented implementation.

  • Areas for Improvement:

    • The code assumes the workbook layout remains stable, so any sheet redesign would require small adjustments.

  • Gem:

    • The implementation stays close to the original workbook rule instead of adding unnecessary abstraction.

Difficulty Level

This task is moderate:

  • The business rule is readable, but the workbook still requires careful implementation to reach the expected layout.