Moving Calculations from Frontend to Backend

The Goal

My Titan Tracker project displays rankings for chefs (referred to as "titans") from the TV show Bobby's Triple Threat.

The ranking logic needs to handle:

• Ties: multiple chefs with identical scores get the same rank
• Rank skipping: if two chefs tie for 1st, the next chef is ranked 3rd, not 2nd
• Inactive chefs: should show "NR" ("Not Ranked") instead of a numerical rank

My Original Approach

My SQL query returned each titan's win percentage (win_pct), and the JavaScript frontend used those to calculate rankings:

function calculateTitanRanks(titanRecords) {
    const activeTitans = titanRecords.filter((t) => t.is_active);
    const inactiveTitans = titanRecords.filter((t) => !t.is_active);

    const winPcts = activeTitans.map((t) => t.win_pct);
    var ranks = [];
    if (winPcts[0] == winPcts[2]) {
        ranks = [1, 1, 1];
    } else if (winPcts[0] == winPcts[1]) {
        ranks = [1, 1, 3];
    } else if (winPcts[1] == winPcts[2]) {
        ranks = [1, 2, 2];
    } else {
        ranks = [1, 2, 3];
    }

    return [...ranks, ...Array(inactiveTitans.length).fill(null)];
}

// Plus a separate generateRankStrings() function
// to format "1st", "T-2nd", "NR", etc.

This worked, but it felt brittle. The function assumed exactly 3 active chefs, required pre-sorted data, and manually detected all tie scenarios. Any change to the data structure would require rewriting this frontend logic.

The Realization

Sometime after shipping this working solution, I learned about SQL window functions. When I saw the RANK() function I realized: this is exactly what I'd been doing manually in JavaScript.

Window functions can partition data into groups and assign ranks within each partition, automatically handling ties and rank skipping. The database already had the perfect tool for the job.

The Refactor

I added the RANK() window function to my SQL query:

SELECT 
    ts.titan_name,
    t.is_active,
    CASE 
    WHEN NOT t.is_active THEN NULL
    ELSE RANK() OVER (
        PARTITION BY t.is_active 
        ORDER BY ts.win_pct DESC, t.titan_name ASC
    )
END AS rank,
    ts.num_win,
    ts.num_tie,
    ts.num_loss
FROM titan_scores ts
JOIN titans t ON ts.titan_name = t.titan_name
ORDER BY t.is_active DESC, rank ASC, titan_name ASC;

Breaking this down:

• PARTITION BY is_active creates separate ranking groups for active vs. inactive chefs
• ORDER BY ts.win_pct DESC ranks by win percentage (highest first)
• RANK() automatically handles ties and skips ranks appropriately
• The CASE statement returns NULL for inactive chefs instead of assigning them a rank

The Simplified JavaScript

My frontend logic collapsed from ~30 lines to this:

function generateRankStrings(titanRecords) {
    return titanRecords.map((titan) => {
        if (titan.rank === null) return "NR";
        return `${titan.rank}${getRankSuffix(titan.rank)}`; // "1st", "2nd", etc.
    });
}

The function now only handles presentation logic (formatting "1" as "1st" or "T-1st"). The actual ranking calculation happens where it belongs: at the data layer.

Before & After Comparison

Why This Matters

1. Maintainability

Ranking logic lives in one place: the database. The frontend only handles presentation. If I need to update the ranking algorithm, I only change the SQL query. No risk of the frontend and backend falling out of sync.

2. Reliability

Manually implementing ranking logic means more opportunities for bugs (off-by-one errors, edge cases, incorrect tie handling). The database's RANK() function is tested, optimized, and handles these cases correctly by design.

3. Scalability

The JavaScript version assumed exactly 3 active chefs. The SQL version works for any number. If the show format evolves, the frontend code doesn't need to change.

Takeaway

When I learned about SQL window functions like RANK(), I realized I'd been reimplementing database functionality in JavaScript. Moving this logic to SQL eliminated ~30 lines of frontend code while making the system more maintainable, reliable, and scalable.