Counting Mentees Correctly: A Case Study in Query Optimization

In the devlog-ist/landing project, we recently tackled an issue regarding the accuracy of mentee counts. Initially, the system was only counting mentees with completed bookings. This overlooked a crucial group: those with confirmed bookings scheduled for future dates.

The problem was that we needed a more inclusive count, reflecting all engaged mentees, regardless of whether their sessions had already occurred.

The Problem: Incomplete Counts

The original query only considered completed bookings:

// Original query (simplified)
$mentees_count = Booking::where('status', 'completed')->count();

This meant that mentees who had booked sessions in advance were not being included in the overall count, leading to an inaccurate representation of engagement.

The Solution: Expanding the Scope

To rectify this, we modified the query to include all bookings except those that were explicitly cancelled. This broader scope ensured that all active mentees were accounted for, irrespective of their session dates.

// Updated query (simplified)
$mentees_count = Booking::where('status', '!=', 'cancelled')->count();

By excluding only cancelled bookings, we captured both completed and upcoming sessions, providing a more accurate and comprehensive mentee count.

The Takeaway

When counting active users or engagements, carefully consider the criteria for inclusion and exclusion. Ensure your queries encompass all relevant states to avoid underreporting. Always validate your assumptions about data completeness. For example, a status column may need != to exclude, not == to include a specific state.