The SQL behind every business question

Most business questions are the same dozen questions in different clothes. Learn the dozen and you can answer nearly anything your warehouse knows.

Part five gave Pondside a model: clean views over the raw syncs, and three marts on top, marts.daily_sales, marts.customers and marts.bean_margins. This part is a cookbook against that model. Every recipe runs on the sample data you can download (see part one), every result shown is the one you will get, and each recipe names the pattern it uses so you can carry it to your own data. Along the way, Pondside's three questions from part one finally get their answers.

Three habits before the first recipe

• Start from the marts. They have already decided what counts as a sale, which time zone a day belongs to and whether money is in cents. Going back to the raw tables means re-deciding all of that, probably differently.
• Write dates as half-open ranges. day >= DATE '2026-01-01' AND day < DATE '2026-10-01' includes every moment of September 30 and nothing of October 1, for dates and timestamps alike. BETWEEN is inclusive at both ends, which is right for whole dates and quietly wrong for timestamps.
• Add money exactly, and round at the end. A floating-point number cannot hold most amounts of cents exactly, so a sum of them is off by a few billionths, and because DuckDB adds in parallel, which few billionths can vary from one run to the next. Usually that vanishes in the rounding; on a total that lands exactly on fifty cents it decides which way the rounding goes. Pondside's model already stores dollars as DECIMAL(18, 2) for this reason. For anything that is not, cast before adding, as in revenue::DECIMAL(18, 2), and the answer is the same every time. (The recipes below cast anyway, so they work on models that made a different choice.)

And before writing anything clever, look. DuckDB's SUMMARIZE profiles every column of a table at once: its type, minimum, maximum, approximate number of distinct values, averages, quartiles and how much is null. It is the fastest way to meet a table you have not seen before.

SUMMARIZE marts.daily_sales;

Revenue by month and channel

The first table in every board deck: months down the side, channels across the top. In most databases that takes a CASE expression per column. In DuckDB it is a PIVOT, which turns the distinct values of one column into columns of their own:

PIVOT (
  SELECT strftime(day, '%Y-%m') AS month, channel, revenue::DECIMAL(18, 2) AS revenue
  FROM marts.daily_sales
  WHERE day >= DATE '2026-01-01' AND day < DATE '2026-10-01'
)
ON channel
USING round(sum(revenue))
GROUP BY month
ORDER BY month;

Read it like a sentence: pivot the rows on channel, using the rounded sum of revenue, grouped by month. The café is the biggest channel and the most seasonal. The subscription is the smallest and the steadiest, growing every single month, which is what recurring revenue is for.

Is the Saturday rush growing, or is it just summer?

This is part one's second question, and it is a trap for anyone comparing this month with last month. A café's summer is busier than its spring, so August beating June says nothing about growth. The fix is the oldest one in analysis: compare like with like. September with September, Saturday with Saturday.

Here are four months either side of the summer, in both years, as orders per Saturday and per weekday. Dividing by the number of days, rather than adding up the month, keeps a month with five Saturdays from looking busier than one with four.

SELECT
  strftime(sold_on, '%Y-%m') AS month,
  round(count(*) FILTER (WHERE dayofweek(sold_on) = 6)
    / count(DISTINCT sold_on) FILTER (WHERE dayofweek(sold_on) = 6)) AS per_saturday,
  round(count(*) FILTER (WHERE dayofweek(sold_on) BETWEEN 1 AND 5)
    / count(DISTINCT sold_on) FILTER (WHERE dayofweek(sold_on) BETWEEN 1 AND 5)) AS per_weekday
FROM clean.cafe_sales
WHERE month(sold_on) BETWEEN 6 AND 9
GROUP BY ALL
ORDER BY month;

FILTER is doing the work there. It attaches a condition to one aggregate, so a single pass over the table counts Saturdays and weekdays side by side. (dayofweek numbers days from Sunday as 0, so Saturday is 6 and Monday to Friday are 1 to 5.)

The answer has two parts. Summer is real but small: in 2025, summer Saturdays ran about four percent above September's. Growth is bigger: every month of 2026 beats the same month of 2025, by 7 percent in September and up to 16 percent in August. So yes, the Saturday rush is growing. But the weekdays are growing faster, by 13 to 19 percent, which is the more interesting news: the café is becoming a weekday habit.

To see when on a Saturday the extra orders arrive, put September's Saturdays side by side by hour:

SELECT
  hour(sold_at) AS hour,
  round(count(*) FILTER (WHERE year(sold_at) = 2025) / 4, 1) AS sept_2025,
  round(count(*) FILTER (WHERE year(sold_at) = 2026) / 4, 1) AS sept_2026,
  round(100 * (sept_2026 / sept_2025 - 1)) AS change_pct
FROM clean.cafe_sales
WHERE dayofweek(sold_at) = 6 AND month(sold_at) = 9
GROUP BY ALL
ORDER BY hour;

Both Septembers have four Saturdays, hence the / 4. The last column uses a DuckDB convenience: a column can refer to another defined earlier in the same SELECT, so change_pct is written in terms of sept_2025 and sept_2026 instead of repeating both counts.

Read it with care. Four Saturdays is a small sample, and a single hour moves by a few orders from one week to the next, so the big swings in the quiet afternoon hours are noise. The broad shape is still worth a look: the 7 and 8 o'clock hours are flat while 9 o'clock and lunch grew. Either the early rush is not growing, or the bar is already making as many drinks as it can at that hour. Data cannot tell those apart; someone standing behind the counter at 8:15 on a Saturday can.

A query tells you where to look. It rarely tells you why.

Window functions: moving averages, running totals, top sellers

Daily numbers are noisy. Saturday is always bigger than Tuesday, so a chart of daily revenue is a saw blade. The cure is a window function: an aggregate computed over a frame of neighboring rows, without collapsing them into one. Two frames cover most needs.

WITH daily AS (
  SELECT day, revenue::DECIMAL(18, 2) AS revenue
  FROM marts.daily_sales
  WHERE channel = 'cafe'
)
SELECT
  day,
  round(revenue) AS revenue,
  round(avg(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)) AS avg_7_days,
  round(sum(revenue) OVER (PARTITION BY date_trunc('month', day) ORDER BY day)) AS month_to_date
FROM daily
QUALIFY day >= DATE '2026-09-21'
ORDER BY day;

Daily revenue jumps by more than half on the weekend; the seven-day average barely moves. Note the QUALIFY at the end. It filters on the result of window functions, the way HAVING filters on the result of a GROUP BY. Filtering with WHERE instead would run before the windows and starve the first rows of their six preceding days, so the averages on September 21 would be wrong.

Top N per group

“What are the best sellers each quarter?” is the same idea with a ranking window. Number the items within each quarter by how many sold, then keep the top few:

SELECT quarter(sold_on) AS quarter, item, sum(quantity) AS sold
FROM clean.cafe_items
WHERE year(sold_on) = 2026
GROUP BY quarter(sold_on), item
QUALIFY row_number() OVER (PARTITION BY quarter ORDER BY sold DESC) <= 3
ORDER BY quarter, sold DESC;

Cold brew climbs into the top three in the third quarter and nowhere else, which is the summer showing up in the menu. One small DuckDB wrinkle: QUALIFY cannot yet be combined with GROUP BY ALL, so this query spells its grouping out.

Cohorts: do subscribers stay?

A subscription business lives or dies on retention, and the standard way to see it is a cohort table. Group subscribers by the month they joined (their cohort), then ask what share of each cohort was still subscribed one, three, six and twelve months later. Comparing cohorts with each other, rather than this month with last, separates “we are keeping people longer” from “we signed up more people.”

SELECT
  strftime(date_trunc('month', started_on), '%Y-%m') AS cohort,
  count(*) AS subscribers,
  round(100 * count(*) FILTER (WHERE ended_on IS NULL OR ended_on > started_on + INTERVAL 1 MONTH) / count(*)) AS month_1,
  round(100 * count(*) FILTER (WHERE ended_on IS NULL OR ended_on > started_on + INTERVAL 3 MONTH) / count(*)) AS month_3,
  round(100 * count(*) FILTER (WHERE ended_on IS NULL OR ended_on > started_on + INTERVAL 6 MONTH) / count(*)) AS month_6,
  round(100 * count(*) FILTER (WHERE ended_on IS NULL OR ended_on > started_on + INTERVAL 12 MONTH) / count(*)) AS month_12
FROM clean.subscriptions
WHERE started_on < DATE '2025-07-01'
GROUP BY ALL
ORDER BY cohort;

The shape is typical of a subscription: the steepest drop is early, and the people who stay past six months tend to keep staying. Roughly one in eight leaves after the first box, and between two in five and a half of every cohort is still subscribed a year in.

Churn and recurring revenue, month by month

Two numbers every subscription business reports: MRR, monthly recurring revenue, the monthly value of every subscription that is active; and churn, the share of subscribers at the start of a month who leave during it. Both need something the subscriptions table does not have: a row for every month, including months in which nothing happened to a given subscriber. That row of months is called a calendar spine, and DuckDB makes one with range():

WITH months AS (
  SELECT CAST(range AS DATE) AS month
  FROM range(TIMESTAMP '2026-01-01', TIMESTAMP '2026-10-01', INTERVAL 1 MONTH)
)
SELECT
  strftime(m.month, '%Y-%m') AS month,
  count(*) FILTER (WHERE s.started_on < m.month
    AND (s.ended_on IS NULL OR s.ended_on >= m.month)) AS active_at_start,
  count(*) FILTER (WHERE s.ended_on >= m.month
    AND s.ended_on < m.month + INTERVAL 1 MONTH) AS churned,
  round(100 * churned / active_at_start, 1) AS churn_pct,
  sum(s.monthly_price) FILTER (WHERE s.started_on < m.month + INTERVAL 1 MONTH
    AND (s.ended_on IS NULL OR s.ended_on >= m.month + INTERVAL 1 MONTH)) AS mrr_at_end
FROM months m
CROSS JOIN clean.subscriptions s
GROUP BY m.month
ORDER BY m.month;

The CROSS JOIN pairs every month with every subscription, and the FILTER clauses decide which pairs count. At Pondside's size that is a few thousand pairs; for a business with a million subscribers you would join on the date range instead. Two definitions are worth stating out loud, because a reader will ask: a subscription with a failed payment (past_due in Stripe) still counts as active here, since it has not been canceled; and MRR is measured at the end of each month.

Pond Club's recurring revenue rose by about half in nine months, while churn wandered between five and eight percent a month. That combination, sign-ups outrunning a steady leak, is what healthy early growth looks like. It is also a reminder that churn compounds: six percent a month means losing about half of a group of subscribers within a year, much as the cohort table showed.

Do subscribers still come to the café?

Part one's first question. The worry is cannibalization: a regular who now gets beans in the post might stop coming in for a flat white. The test is a before and after comparison for the same people: for each subscriber the café recognizes, count their visits in the six months before they subscribed and the six months after.

WITH subscribers AS (
  SELECT email, min(started_on) AS started_on
  FROM clean.subscriptions
  WHERE started_on BETWEEN DATE '2025-07-01' AND DATE '2026-03-31'
  GROUP BY email
),
visits AS (
  SELECT
    s.email,
    count(*) FILTER (WHERE c.sold_on >= s.started_on - INTERVAL 6 MONTH
      AND c.sold_on < s.started_on) AS before,
    count(*) FILTER (WHERE c.sold_on >= s.started_on
      AND c.sold_on < s.started_on + INTERVAL 6 MONTH) AS after
  FROM subscribers s
  JOIN clean.cafe_sales c USING (email)
  GROUP BY s.email
)
SELECT
  count(*) AS subscribers_seen_in_cafe,
  round(avg(before) / 6, 2) AS visits_a_month_before,
  round(avg(after) / 6, 2) AS visits_a_month_after,
  count(*) FILTER (WHERE after > before) AS visiting_more,
  count(*) FILTER (WHERE after < before) AS visiting_less,
  count(*) FILTER (WHERE after = before) AS no_change
FROM visits;

The date range is deliberate. Only people who subscribed between July 2025 and March 2026 have six full months of café history on both sides, since the data starts in January 2025 and ends in September 2026: the same censoring idea as the cohorts.

The answer is reassuring. The 96 subscribers the café can recognize visit about as often after subscribing as before, slightly more if anything, and more of them are visiting more than less. There is no sign that Pond Club is replacing the café. Two honest caveats: most café sales are anonymous, so these are only the regulars Square knows by name, and a before-and-after comparison shows what happened, not why. But “no evidence of cannibalization” from the people you can see is a long way from the spreadsheet guesswork part one started with.

Which beans earn the most?

Part one's third question needed sales from two channels and costs from a spreadsheet. The model already did the joining; the query is short:

SELECT
  bean,
  bags_sold,
  revenue,
  bean_cost_per_bag,
  margin,
  round(margin / bags_sold, 2) AS margin_per_bag
FROM marts.bean_margins
ORDER BY margin DESC;

Two different answers, both true, and the difference is the useful part. In total, the house blend wins by a mile: Mallard Blend is cheap green coffee sold in volume. Per bag, the order flips: Kingfisher Kenya costs the most to buy but sells for enough more that each bag earns about two dollars more than a bag of Mallard. Ethiopia is close behind.

That changes the decision a roaster faces. Every customer nudged from the house blend to the Kenya is worth about two dollars more a bag, before labor and packaging. Whether that is worth a tasting table at the farmers' market is a business question, but it is now a question with a number attached.

The patterns, on one page

Strip the coffee away and this part used a short list of patterns, which between them answer a surprising share of what any business asks.

Every one of those queries was written by hand, by someone who knows SQL and knows the model. Pondside's owner is not that person, and most owners are not. The next part is about the thing that changes that: a standard way for an AI assistant to read a warehouse's schema, write this SQL itself, run it and explain the answer. Part seven: MCP, explained.