Customer Revenue Concentration
FY2026 Year-to-Date vs FY2025

SELECT
    t.entity                         AS customer_id,
    c.entityid                       AS customer,
    c.companyname                    AS company,
    c.parent                         AS parent_id,
    p.entityid                       AS parent_name,
    t.type                           AS tran_type,
    COUNT(DISTINCT t.id)             AS txn_count,
    ROUND(SUM(ABS(tl.netamount)), 2) AS revenue
FROM transaction t
JOIN transactionline tl ON tl.transaction = t.id
LEFT JOIN customer c    ON c.id = t.entity
LEFT JOIN customer p    ON p.id = c.parent
WHERE t.type IN ('CustInvc', 'CashSale')
  AND t.posting = 'T'
  AND tl.mainline = 'F'
  AND tl.taxline = 'F'
  AND tl.subsidiary IN (1, 2, 3)
  AND t.trandate >= TO_DATE('2026-01-01', 'YYYY-MM-DD')
  AND t.trandate <= TO_DATE('2026-09-04', 'YYYY-MM-DD')
GROUP BY t.entity, c.entityid, c.companyname, c.parent, p.entityid, t.type
ORDER BY t.entity, t.type
-- FY2025 variant: trandate BETWEEN TO_DATE('2025-01-01') AND TO_DATE('2025-12-31')  → 79 rows
-- Like-for-like variant: trandate BETWEEN TO_DATE('2025-01-01') AND TO_DATE('2025-09-04') → 72 rows
-- FY2026 YTD result: 100 rows (98 customers × up to 2 transaction types)

SELECT
    COUNT(*)                                                    AS total_customers,
    SUM(CASE WHEN c.parent IS NOT NULL THEN 1 ELSE 0 END)       AS with_parent,
    SUM(CASE WHEN c.parent IS NOT NULL AND p.parent IS NOT NULL THEN 1 ELSE 0 END) AS depth2,
    SUM(CASE WHEN c.parent IS NOT NULL AND p.parent IS NOT NULL AND gp.parent IS NOT NULL THEN 1 ELSE 0 END) AS depth3
FROM customer c
LEFT JOIN customer p  ON p.id = c.parent
LEFT JOIN customer gp ON gp.id = p.parent
-- Result: total_customers 274, with_parent 1, depth2 0, depth3 0

SELECT
    CASE WHEN t.trandate >= TO_DATE('2026-01-01','YYYY-MM-DD') THEN 'FY26 YTD' ELSE 'FY25' END AS period,
    SUM(CASE WHEN tl.netamount < 0 THEN 1 ELSE 0 END)                     AS negative_lines,
    ROUND(SUM(CASE WHEN tl.netamount < 0 THEN tl.netamount ELSE 0 END), 2) AS negative_amount,
    SUM(CASE WHEN tl.netamount > 0 THEN 1 ELSE 0 END)                     AS positive_lines,
    ROUND(SUM(tl.netamount), 2)                                            AS signed_net,
    ROUND(SUM(ABS(tl.netamount)), 2)                                       AS abs_net
FROM transaction t
JOIN transactionline tl ON tl.transaction = t.id
WHERE t.type IN ('CustInvc', 'CashSale')
  AND t.posting = 'T'
  AND tl.mainline = 'F'
  AND tl.taxline = 'F'
  AND tl.subsidiary IN (1, 2, 3)
  AND t.trandate >= TO_DATE('2025-01-01', 'YYYY-MM-DD')
  AND t.trandate <= TO_DATE('2026-09-04', 'YYYY-MM-DD')
GROUP BY CASE WHEN t.trandate >= TO_DATE('2026-01-01','YYYY-MM-DD') THEN 'FY26 YTD' ELSE 'FY25' END
-- FY26 YTD: 1,800 negative lines, 0 positive, signed −1,329,784.27, abs 1,329,784.27
-- FY25:     2,493 negative lines, 0 positive, signed −1,249,049.59, abs 1,249,049.59

function build(raw){
  // Roll up to top-level parent: key = parent_id || customer_id (hierarchy depth verified = 1)
  const map = {};
  let rolled = 0, unknownEntity = 0;
  const byType = {CustInvc:{rev:0,txns:0}, CashSale:{rev:0,txns:0}};
  for (const r of raw){
    const rev = H.num(r.revenue), txns = H.num(r.txn_count);
    const key = r.parent_id ? String(r.parent_id) : String(r.customer_id);
    if (r.parent_id) rolled++;
    if (!r.customer) unknownEntity++;
    const name = r.parent_id ? (r.parent_name || ('#'+r.parent_id)) : (r.customer || r.company || ('entity #'+r.customer_id));
    if (!map[key]) map[key] = {key, name, rev:0, txns:0, inv:0, cash:0, children:new Set()};
    map[key].rev += rev; map[key].txns += txns;
    if (r.tran_type==='CustInvc') map[key].inv += rev; else map[key].cash += rev;
    map[key].children.add(String(r.customer_id));
    byType[r.tran_type].rev += rev; byType[r.tran_type].txns += txns;
  }
  const custs = Object.values(map).map(c=>({key:c.key,name:c.name,rev:+c.rev.toFixed(2),txns:c.txns,inv:+c.inv.toFixed(2),cash:+c.cash.toFixed(2),members:c.children.size}));
  custs.sort((a,b)=>b.rev-a.rev);
  const n = custs.length, total = custs.reduce((s,c)=>s+c.rev,0);
  let cum = 0, hhi = 0, shareSum = 0, c50=null, c80=null;
  custs.forEach((c,i)=>{ c.share = c.rev/total; shareSum += c.share; hhi += c.share*c.share; cum += c.share; c.cumShare = cum; c.rank=i+1;
    if(c50===null && cum>=0.5) c50=i+1; if(c80===null && cum>=0.8) c80=i+1; });
  const topShare = k => custs.slice(0,k).reduce((s,c)=>s+c.share,0);
  // Gini on ascending sort: G = 2Σ(i·x_i)/(n·Σx) − (n+1)/n
  const asc = custs.slice().sort((a,b)=>a.rev-b.rev);
  let num=0; asc.forEach((c,i)=>{ num += (i+1)*c.rev; });
  const gini = (2*num)/(n*total) - (n+1)/n;
  // Lorenz (ascending) & Pareto (descending), 20 points at 5% steps
  const lorenz=[], pareto=[];
  for (let j=1;j<=20;j++){
    const k = Math.round(n*j/20);
    lorenz.push({pctCust:j*5, pctRev:+(asc.slice(0,k).reduce((s,c)=>s+c.rev,0)/total*100).toFixed(2), custs:k});
    pareto.push({pctCust:j*5, pctRev:+(custs.slice(0,k).reduce((s,c)=>s+c.rev,0)/total*100).toFixed(2), custs:k});
  }
  const small = custs.filter(c=>c.rev<1000);
  const hhi10k = hhi*10000;
  const band = hhi10k<1500?'Unconcentrated (<1,500)':hhi10k<2500?'Moderately concentrated (1,500-2,500)':'Highly concentrated (>2,500)';
  // Hand-check: HHI from top-15 + residual
  const top15 = custs.slice(0,15);
  const hhiTop15 = top15.reduce((s,c)=>s+c.share*c.share,0)*10000;
  const hhiResidual = custs.slice(15).reduce((s,c)=>s+c.share*c.share,0)*10000;
  const stats = H.stats(custs, c=>c.rev);
  return {
    n, total:+total.toFixed(2), rolledRows:rolled, unknownEntity,
    byType:{CustInvc:{rev:+byType.CustInvc.rev.toFixed(2),txns:byType.CustInvc.txns}, CashSale:{rev:+byType.CashSale.rev.toFixed(2),txns:byType.CashSale.txns}},
    hhi:+hhi10k.toFixed(1), band, effectiveN:+(1/hhi).toFixed(1), gini:+gini.toFixed(4),
    top1:+(topShare(1)*100).toFixed(2), top5:+(topShare(5)*100).toFixed(2), top10:+(topShare(10)*100).toFixed(2), top20:+(topShare(20)*100).toFixed(2),
    custTo50:c50, custTo80:c80, lorenz, pareto,
    top15: top15.map(c=>({rank:c.rank,key:c.key,name:c.name,rev:c.rev,txns:c.txns,inv:c.inv,cash:c.cash,members:c.members,share:+(c.share*100).toFixed(2),cumShare:+(c.cumShare*100).toFixed(2)})),
    under1k:{count:small.length, rev:+small.reduce((s,c)=>s+c.rev,0).toFixed(2), share:+(small.reduce((s,c)=>s+c.share,0)*100).toFixed(2)},
    checks:{shareSumPct:+(shareSum*100).toFixed(6), hhiTop15:+hhiTop15.toFixed(1), hhiResidual:+hhiResidual.toFixed(1), hhiRecomputed:+(hhiTop15+hhiResidual).toFixed(1),
            sumTop15Rev:+top15.reduce((s,c)=>s+c.rev,0).toFixed(2), sumRestRev:+custs.slice(15).reduce((s,c)=>s+c.rev,0).toFixed(2)},
    dist:{min:+stats.min.toFixed(2),median:+stats.median.toFixed(2),mean:+stats.mean.toFixed(2),p90:+stats.p90.toFixed(2),max:+stats.max.toFixed(2)},
    _all: custs.map(c=>({key:c.key,name:c.name,rev:c.rev,share:c.share,rank:c.rank}))
  };
}
const A = build(rows.fy26), B = build(rows.fy25);
// ---- YoY drivers ----
const idx26 = H.indexBy(A._all,'key'), idx25 = H.indexBy(B._all,'key');
const top10_26 = A._all.slice(0,10).map(c=>c.key), top10_25 = B._all.slice(0,10).map(c=>c.key);
const entered = top10_26.filter(k=>!top10_25.includes(k)).map(k=>({name:idx26[k].name, rank26:idx26[k].rank, share26:+(idx26[k].share*100).toFixed(2), rank25: idx25[k]?idx25[k].rank:null, share25: idx25[k]?+(idx25[k].share*100).toFixed(2):0}));
const left = top10_25.filter(k=>!top10_26.includes(k)).map(k=>({name:idx25[k].name, rank25:idx25[k].rank, share25:+(idx25[k].share*100).toFixed(2), rank26: idx26[k]?idx26[k].rank:null, share26: idx26[k]?+(idx26[k].share*100).toFixed(2):0}));
const keys = H.uniq([...Object.keys(idx26),...Object.keys(idx25)]);
const deltas = keys.map(k=>{const a=idx26[k],b=idx25[k];return {key:k,name:(a||b).name, share25:b?b.share*100:0, share26:a?a.share*100:0, rev25:b?b.rev:0, rev26:a?a.rev:0, d:(a?a.share:0)-(b?b.share:0)};});
const fmt = d=>({name:d.name, share25:+d.share25.toFixed(2), share26:+d.share26.toFixed(2), deltaPts:+(d.d*100).toFixed(2), rev25:d.rev25, rev26:d.rev26,
                  status: !idx25[d.key]?'NEW in FY26':!idx26[d.key]?'No FY26 revenue':'Both'});
const gains = H.sortBy(deltas, d=>-d.d).slice(0,8).map(fmt);
const losses = H.sortBy(deltas, d=>d.d).slice(0,8).map(fmt);
const newCust = deltas.filter(d=>!idx25[d.key]), lostCust = deltas.filter(d=>!idx26[d.key]), both = deltas.filter(d=>idx25[d.key]&&idx26[d.key]);
const retention = {newCount:newCust.length, newRev26:+newCust.reduce((s,d)=>s+d.rev26,0).toFixed(2), lostCount:lostCust.length, lostRev25:+lostCust.reduce((s,d)=>s+d.rev25,0).toFixed(2),
                   retainedCount:both.length, retainedRev25:+both.reduce((s,d)=>s+d.rev25,0).toFixed(2), retainedRev26:+both.reduce((s,d)=>s+d.rev26,0).toFixed(2)};
// HHI decomposition: Δ(share²) × 10,000 per customer
const hhiDrivers = H.sortBy(deltas.map(d=>({name:d.name, dHHI: (Math.pow(d.share26/100,2)-Math.pow(d.share25/100,2))*10000})), d=>-Math.abs(d.dHHI)).slice(0,6).map(d=>({name:d.name,dHHI:+d.dHHI.toFixed(1)}));
delete A._all; delete B._all;
return {meta:meta.rowCounts, fy26:A, fy25:B, yoy:{entered, left, gains, losses, retention, hhiDrivers}};

Execution: 100 + 79 rows fetched (28 KB), worker time 17 ms, total 1.3 s. The like-for-like FY2025 window was computed with a trimmed variant of build() against the 72-row Jan 1 – Sep 4, 2025 result set. SVG path coordinates were generated in evalJs from the reducer's 20-point arrays (x = 60 + pct × 4.6, y = 400 − pct × 3.8) so no coordinate was hand-derived.