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 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
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.
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
- Customer Order Totals Medium Free Sum each customer's orders with a single SQL query instead of an N+1 loop.
- Average Measurement Value per Device Medium Pro Compute each device's average measurement value in one query, not an N+1 loop.
- Average Basket Size Medium Pro Average number of line items per tab, computed server-side.
- Repeat Customers Medium Pro Find customers with more than one order using a single query instead of an N+1 loop.