Bean & Bark: answer a business question

View .md

You found Dana in one cheap read. This one's different: why did the West region's revenue fall 12% last month? There is no partition key for "revenue" — this isn't a lookup in the table, it's a question about it. That's what SUM and GROUP BY are for, and it's where you meet the honest cost of asking DynamoDB an analytical question.

You'll learn
  • Fold thousands of rows into an answer with SUM and GROUP BY
  • Understand that DynamoDB has no GROUP BY — the studio reads the matched items and folds them client-side, and every item read is billed
  • Follow a total to its cause: what changed, then why it changed

You can't look this up — you have to fold it

A key gets you one partition. This question spans the whole West region across months — there's nothing to key on. DynamoDB itself has no GROUP BY, so DynoStudio does the fold for you: it reads the matching orders and adds them up client-side, one row per month. Useful — but be clear-eyed about the strip. To total the West's orders it must first Scan for them, and everything it reads is billed.

sql 
-- there is no partition called "revenue" — you fold the tableSELECT month, SUM(TotalCents) / 100 AS revenueFROM "BeanAndBark"WHERE Region = 'West' AND begins_with(sk, 'ORDER#')GROUP BY monthORDER BY month
Try it · revenue by monthSample
Run it live in DynoStudio →
Executes asScan + client-side fold · BeanAndBark
Scan FilterExpression: Region = 'West' · reads ~240,000 items, then GROUP BY folded in the studio
DynamoDB has no GROUP BY — the studio reads the matched orders and folds them into one row per month, in memory.
Every item read is billed. A cross-cutting total is a Scan; in Part 2, a zero-ETL analytics replica is how you make this cheap and repeatable.
#monthrevenue
12026-01$46,100
22026-02$48,800
32026-03$44,200
42026-04$41,900
52026-05$40,300
62026-06$42,700
Fetched 6 items in 402ms · 480 RCU · 22 partitions

There's the shape of it: a February peak, then a slide — June is down about 12% from that top. But a total only tells you what happened, not why. So you ask a second question, and this is the move that earns your morning: instead of grouping orders by month, group subscription cancellations by month.

Recap
  • SUM / GROUP BY answer questions about the table (totals, trends), not lookups in it — there's no key for "revenue".
  • DynamoDB has no GROUP BY: the studio reads every matched item and folds client-side, so a cross-cutting aggregate is a Scan you pay for in full.
  • A total tells you what changed; a second grouping (churn by month) tells you why.
Before the board call — check yourself2
Why does SUM(...) GROUP BY month over the West region cost more than looking up one customer's orders?
Where does the GROUP BY actually execute?
Try it yourself 2
1 Break the drop down a different way
Nadia asks a follow-up: is the fall in every roast, or one? Re-group West revenue by RoastLevel instead of month.
Same fold, different key: swap month for RoastLevel in both the SELECT and the GROUP BY.
Show solution
Group on RoastLevel — the decaf row is the one that fell, which is the thread that leads to the churned subscriptions.
sql 
SELECT RoastLevel, SUM(TotalCents) / 100 AS revenueFROM "BeanAndBark"WHERE Region = 'West' AND begins_with(sk, 'ORDER#')GROUP BY RoastLevel
2 Count the cancellations
Prove the cause: count subscription cancellations per month for the West region, so the March spike is undeniable.
The rows are subscriptions (begins_with(sk, 'SUB#')) with Status = 'cancelled'. Count them with COUNT(*), grouped by month.
Show solution
COUNT(*) ... GROUP BY month over cancelled West subscriptions puts a hard number on the March spike — the evidence behind the revenue drop.
sql 
SELECT month, COUNT(*) AS cancellationsFROM "BeanAndBark"WHERE Region = 'West' AND begins_with(sk, 'SUB#')  AND Status = 'cancelled'GROUP BY monthORDER BY month
Was this page helpful?