sales_report.cs in C#

Join, group and rank in one query: a sales table from raw orders.

// sales_report: join orders to a catalog, group, and rank the take
var catalog = new[]
{
    (Sku: "kb-01", Name: "Keyboard", Price: 89.00),
    (Sku: "ms-02", Name: "Mouse", Price: 49.00),
    (Sku: "mn-03", Name: "Monitor", Price: 349.00),
};
var orders = new[]
{
    (Sku: "kb-01", Qty: 3), (Sku: "mn-03", Qty: 1),
    (Sku: "kb-01", Qty: 2), (Sku: "ms-02", Qty: 4),
    (Sku: "mn-03", Qty: 2), (Sku: "xx-99", Qty: 1),
};

var rows = (
    from o in orders
    join item in catalog on o.Sku equals item.Sku
    group (o, item) by item.Name into g
    let revenue = g.Sum(x => x.o.Qty * x.item.Price)
    orderby revenue descending
    select (Product: g.Key, Units: g.Sum(x => x.o.Qty), Revenue: revenue))
    .ToList();

Console.WriteLine("product    units  revenue");
foreach (var (product, units, revenue) in rows)
{
    Console.WriteLine($"{product,-9} {units,5}  {revenue,8:F2}");
}

var matched = rows.Sum(r => r.Units);
var total = orders.Sum(o => o.Qty);
Console.WriteLine($"\n{total - matched} units had no catalog entry");

How it works

1. The query joins orders to a catalog and groups by product name.
2. let revenue names the aggregation the ordering reuses.
3. Unmatched SKUs fall out of the join and are counted at the end.

Keywords and builtins used here

• by
• descending
• equals
• foreach
• from
• group
• in
• into
• join
• let
• new
• on
• orderby
• select
• var

The run, in numbers

Lines

32

Characters to type

980

Tokens

274

Three-star pace

90 tpm

At the three-star pace of 90 tokens a minute, this run takes about 183 seconds.

Type this snippet

Step 1 of 2 in Encore, step 16 of 17 in LINQ in depth.

← Previous Next →