# Bean & Bark: answer a business question

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

> **9:14am — the follow-up:** **Nadia:** *"you're a lifesaver. bigger thing though — West revenue is down ~12% and the board call is at 11. can you break it down by month so I can see when it started?"*

## 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 table
SELECT month, SUM(TotalCents) / 100 AS revenue
FROM "BeanAndBark"
WHERE Region = 'West' AND begins_with(sk, 'ORDER#')
GROUP BY month
ORDER BY month
```

**Try it · revenue by month**

```sql
SELECT month, SUM(TotalCents) / 100 AS revenue
FROM "BeanAndBark"
WHERE Region = 'West' AND begins_with(sk, 'ORDER#')
GROUP BY month
ORDER BY month
```

_Executes as:_ Scan + client-side fold · BeanAndBark — 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.

_Run it live in DynoStudio:_ https://www.kanject.com/dynostudio/

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.

> **The answer: they didn't slow down — they left:** Grouped by month, the churn spikes hard in **March** — right after a **price change landed at the end of February** — and it's overwhelmingly **decaf** subscriptions. The West over-indexes on decaf, so when the price moved, the region moved with it. That's not "sales are soft," it's a **cohort that churned** — a real answer, in Nadia's inbox at 10:40, twenty minutes before the board call.

**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 yourself**

**1. Why does `SUM(...) GROUP BY month` over the West region cost more than looking up one customer's orders?**

- There's no key for it, so DynamoDB Scans every item and the studio folds the matches ✓ — "revenue by month" spans the whole table — no partition to key on. It reads everything, then folds client-side; the read is what you pay for.
- Aggregates are a premium DynamoDB feature billed at a higher rate — there's no special aggregate rate — the cost is simply the **Scan** underneath. DynamoDB has no server-side GROUP BY at all.
- SUM writes the total back to the table, and writes cost more than reads — nothing is written — the fold happens in memory in the studio. The cost is entirely the read it takes to gather the rows.

**2. Where does the `GROUP BY` actually execute?**

- Client-side, in the studio, after the items are read ✓ — DynamoDB returns the matched items; DynoStudio folds them into groups in memory — which is why the whole matched set has to be read first.
- On the DynamoDB server, which returns only the grouped rows — DynamoDB has no GROUP BY — it can't return grouped rows. The studio does the grouping after reading the items.

**Try it yourself**

**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`.

_Hint:_ Same fold, different key: swap `month` for `RoastLevel` in both the `SELECT` and the `GROUP BY`.

_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 revenue
FROM "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.

_Hint:_ The rows are subscriptions (`begins_with(sk, 'SUB#')`) with `Status = 'cancelled'`. Count them with `COUNT(*)`, grouped by month.

_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 cancellations
FROM "BeanAndBark"
WHERE Region = 'West' AND begins_with(sk, 'SUB#')
  AND Status = 'cancelled'
GROUP BY month
ORDER BY month
```

> **Where next?:** You answered a real business question — and felt what it cost. [Aggregates & GROUP BY](https://www.kanject.com/docs/dynostudio-partiql-aggregates/) is the full reference: every fold, `HAVING`, and how the studio discloses the read behind each one. In Part 2, you'll ask the *same* questions against a zero-ETL replica and watch the bill disappear.

---
_Source: https://www.kanject.com/docs/dynostudio-bean-bark-region-revenue/ · Kanject Docs_