Challenge 12 - Measuring Ticket Processing Time

Level: Medium

Description: Imagine you are managing your team's work using a Kanban board to track progress. On the board, there are tickets for different tasks and each ticket can move from one stage to the next, as work progresses. Each ticket has a unique ID, the date the ticket was created, the date of the move, and the “from” and “to” stages of the move. Typically, tickets move across different stages in this sequence: "New" → "Doing" → "Review" → "Done". Occasionally, tickets can move backwards (from "Review" → "Doing", if edits need to be made) or are not always created in the "New" stage. As the manager of the team, you always strive for better and more efficient ways to manage tickets. You are especially interested in uncovering how much time, on average, tickets spend in stage “Doing”. Understanding this could reveal bottlenecks and improve your team’s efficiency.

To answer the question, you decide to build a solution that reads the data, tracks each ticket’s time in stages, and zooms in on the "Doing" stage. Keep in mind, though, that you should not include tickets that are stuck in the "Doing" stage, as there is no way to know how long they will stay there for. Ready to uncover what’s really happening by calculating the average number of days tickets spend in "Doing"?

Beginner-friendly objectives: 1. Read the ticket movement data from a CSV file. 2. Convert string columns to date format, and lag the Move date column. 3. Compute how many days each ticket spends in each stage. 4. Focusing only on the "Doing" stage, calculate the average number of days tickets spend in this stage.

Intermediate-friendly objectives: 1. Notice that some tickets have visited the "Doing" stage more than once. Before computing the average number of days tickets spent in "Doing", make sure that tickets that have visited the "Doing" stage multiple times get properly accounted for as a single ticket.