Skip to content
Katabench
Try free

Top Spending Customers

Medium About 15 min Free, no account needed

Return the highest-spending customers with one ranked query, not an N+1.

The brief

Using the ECommerceDbContext, implement TopSpenders(ECommerceDbContext db, string country, int limit) to return the top limit customers in country by total order value, as CustomerTotal { CustomerId, Total }. Order by Total descending, breaking ties by CustomerId ascending, and return at most limit rows.

The performance goal

The starter code loads every customer, sums their orders with a separate query each (N+1), then sorts in memory. Against thousands of customers it times out.

Your goal: push the sum, ordering and limit into a single translated query so the database ranks the customers and returns only the rows you need, instead of the N+1 the starter performs.

This puzzle runs against a real, throwaway PostgreSQL database created just for your submission and destroyed afterwards.

Starter code

C#
using System.Collections.Generic;
using System.Linq;
using CodeRunner.PuzzleSchemas;

public class Solution
{
    // Return the top `limit` customers in `country` by total spend (desc),
    // ties broken by CustomerId ascending.
    public List<CustomerTotal> TopSpenders(ECommerceDbContext db, string country, int limit)
    {
        // Naive N+1: sum each customer with its own query, then sort in memory.
        var customers = db.Customers
            .Where(c => c.Country == country)
            .ToList();

        var totals = new List<CustomerTotal>();
        foreach (var c in customers)
        {
            var total = db.Orders
                .Where(o => o.CustomerId == c.Id)
                .Sum(o => o.Total);

            totals.Add(new CustomerTotal { CustomerId = c.Id, Total = total });
        }

        return totals
            .OrderByDescending(x => x.Total)
            .ThenBy(x => x.CustomerId)
            .Take(limit)
            .ToList();
    }
}

How it is graded

Your LINQ or SQL runs against real PostgreSQL. You see the SQL your code generates on the sample tests and can inspect its execution plan, so you learn when the database did the wrong work.

EF Core practice →

What it practices

Aggregation and grouping
GROUP BY, HAVING, conditional aggregates, pivots and ROLLUP or grouping sets.
N+1 queries
Replace a query per row, or per key, with one set-based round trip.

Related katas