Customer Order Totals
Medium About 15 min Free, no account needed
Sum each customer's orders with a single SQL query instead of an N+1 loop.
The brief
You are given an Entity Framework Core ECommerceDbContext over two tables:
Customers(Id,Name,Country)Orders(Id,CustomerId,Total,PlacedAt)
Implement CustomerTotals(ECommerceDbContext db, string country) to return one
CustomerTotal { CustomerId, Total } for every customer in the given
country, where Total is the sum of that customer's order totals. Order the
result by CustomerId ascending. (Every customer in the test data has at least
one order.)
The starter code is a classic N+1: it loads the customers, then runs a separate query per customer to sum their orders. Against the hidden dataset of thousands of customers that means thousands of round-trips to the database, and it times out.
Your goal: rewrite CustomerTotals so EF Core translates it into a single
SQL query, collapsing the per-customer round-trips into one statement the database
executes server-side.
This puzzle runs against a real, throwaway PostgreSQL database that is created just for your submission and destroyed afterwards. Write provider-agnostic LINQ, you do not open the connection yourself.
Starter code
using System.Collections.Generic;
using System.Linq;
using CodeRunner.PuzzleSchemas;
public class Solution
{
// Return one CustomerTotal per customer in `country`, summing their orders,
// ordered by CustomerId ascending.
public List<CustomerTotal> CustomerTotals(ECommerceDbContext db, string country)
{
// Naive N+1: one query for the customers, then one query PER customer.
// Correct, but far too many round-trips for the hidden dataset.
var customers = db.Customers
.Where(c => c.Country == country)
.OrderBy(c => c.Id)
.ToList();
var result = new List<CustomerTotal>();
foreach (var c in customers)
{
var total = db.Orders
.Where(o => o.CustomerId == c.Id)
.Sum(o => o.Total);
result.Add(new CustomerTotal { CustomerId = c.Id, Total = total });
}
return result;
}
}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.
Related guides
What it practices
- N+1 queries
- Replace a query per row, or per key, with one set-based round trip.
- Aggregation and grouping
- GROUP BY, HAVING, conditional aggregates, pivots and ROLLUP or grouping sets.
Related katas
- Top Spending Customers Medium Free Return the highest-spending customers with one ranked query, not an N+1.
- 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.