Omid - Challenge 61

data-challenges
advanced-exercises
🔰 We want to calculate the total sales per customer based on their latest ID, as shown in the result table.
Published

March 24, 2026

Illustration for Omid - Challenge 61

Challenge Description

🔰 We want to calculate the total sales per customer based on their latest ID, as shown in the result table.

Solutions

library(tidyverse)
library(readxl)

input1 = read_excel("files/CH-061 Sales per customer.xlsx", range = "B2:D36")
input2 = read_excel("files/CH-061 Sales per customer.xlsx", range = "F2:G8")
test   = read_excel("files/CH-061 Sales per customer.xlsx", range = "I2:J10") %>%
  arrange(desc(Sales))

find_latest_id <- function(id, changes) {
  new_id <- changes %>% filter(`OLD ID` == id) %>% pull(`New ID`)
  if (length(new_id) == 0) {
    return(id)
  } else {
    return(find_latest_id(new_id, changes))
  }
}

transactions <- input1 %>%
  mutate(Customer = map_chr(`Customer ID`, find_latest_id, input2)) %>%
  summarise(Sales = sum(Quantity), .by = Customer) %>%
  arrange(desc(Sales))

identical(test, transactions)
# [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

input1 = pd.read_excel("CH-061 Sales per customer.xlsx", usecols="B:D", skiprows=1)
input2 = pd.read_excel("CH-061 Sales per customer.xlsx", usecols="F:G", skiprows=1, nrows = 6)
test = pd.read_excel("CH-061 Sales per customer.xlsx", usecols="I:J", skiprows=1, nrows = 8)\
    .sort_values(by="Sales", ascending=False)\
    .reset_index(drop=True)

def find_latest_id(id, changes):
    new_id = changes.loc[changes['OLD ID'] == id, 'New ID'].values
    if len(new_id) == 0:
        return id
    else:
        return find_latest_id(new_id[0], changes)

transactions = input1.copy()
transactions['Customer'] = transactions['Customer ID'].apply(lambda x: find_latest_id(x, input2))
transactions = transactions.groupby('Customer').agg({'Quantity': 'sum'})\
    .reset_index().rename(columns={'Quantity': 'Sales'})\
    .sort_values(by='Sales', ascending=False).reset_index(drop=True)

print(transactions.equals(test)) # 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.