Enough SQL to be dangerous: the queries a product manager should write, and the traps that fool AI

“Can you pull forecast usage?” is a request that produces a week of back-and-forth. “To decide whether to keep the callout, what share of eligible namespaces opened forecast details within 14 days, by variant, for assignments from 15 November to 13 December?” produces an answer.

Product managers don't need to be data scientists. But they need to ask good questions of data, read results critically, and answer simple questions themselves without waiting a week for an analyst. This article uses the book's Beacon case, a delivery-forecasting feature inside GitLab, to show the minimum toolkit: how product data is shaped, four SQL patterns, the most common way queries lie, and how to write a decision that the data actually supports.

Three levels of data work

The handoff between levels is a well-framed question. A good analytics request states the decision it informs, the exact metric definitions, the population, the time window, the comparison, and when the answer is needed.

Events, entities and grain

Product data comes in two shapes. Events are things that happened, with a timestamp: user X viewed the forecast panel on milestone Y at 14:02. Entities are things that exist, with attributes: namespace 4821 is on Premium, created in 2023, with 64 members. Most analysis joins the two: count events, grouped by an entity attribute.

The single most important idea is grain: what does one row represent? One event? One user per day? One namespace per month? Most analytics mistakes are grain mistakes: counting events when you meant users, or averaging per-namespace rates as if every namespace were the same size.

The traps in product data are predictable: your own team's usage dominating early numbers; bots and API clients generating huge event counts; the same event fired twice; “per day” depending on whose day; self-managed usage pings arriving weeks late so recent periods look artificially low; and “active” quietly redefined last quarter.

Four patterns that answer most questions

1. Count distinct things over time

-- Distinct customer namespaces that viewed a forecast, per week
SELECT
  DATE_TRUNC('week', e.created_at)  AS week,
  COUNT(DISTINCT e.namespace_id)    AS forecast_groups
FROM forecast_events e
JOIN namespaces n ON n.namespace_id = e.namespace_id
WHERE e.event_name = 'forecast_panel_viewed'
  AND n.is_internal = FALSE
  AND e.created_at >= '2027-09-13'
GROUP BY 1
ORDER BY 1;

Read it from the inside out: take panel-view events, keep customer namespaces only, bucket them by week, and count distinct namespaces (not events) per week. COUNT(DISTINCT …) is the most important function in product analytics, because it turns events into users or accounts.

2. Break it down by segment

SELECT
  n.plan_tier,
  COUNT(DISTINCT e.namespace_id) AS forecast_groups
FROM forecast_events e
JOIN namespaces n ON n.namespace_id = e.namespace_id
WHERE e.event_name = 'forecast_panel_viewed'
  AND n.is_internal = FALSE
  AND e.created_at >= '2027-11-01'
  AND e.created_at <  '2027-11-08'   -- half-open: >= start, < end
GROUP BY 1
ORDER BY 2 DESC;

Note the half-open date range. It avoids double counting at boundaries and works whatever the time of day.

3. A funnel

-- Of namespaces that saw the panel, how many opened details within 14 days?
WITH first_view AS (
  SELECT namespace_id, MIN(created_at) AS first_seen
  FROM forecast_events
  WHERE event_name = 'forecast_panel_viewed'
  GROUP BY 1
),
opened AS (
  SELECT DISTINCT f.namespace_id
  FROM first_view f
  JOIN forecast_events e
    ON  e.namespace_id = f.namespace_id
    AND e.event_name   = 'forecast_details_opened'
    AND e.created_at  >= f.first_seen
    AND e.created_at  <  f.first_seen + INTERVAL '14 days'
)
SELECT
  COUNT(*)                                   AS saw_panel,
  (SELECT COUNT(*) FROM opened)              AS opened_details,
  ROUND(100.0 * (SELECT COUNT(*) FROM opened) / COUNT(*), 1) AS activation_pct
FROM first_view;

The WITH clauses (common table expressions) build the query in readable steps: first-view date per namespace, then which of those opened details within 14 days, then the rate. If you can read a CTE, you can review most analysts' work.

4. A cohort retention table

WITH first_week AS (
  SELECT namespace_id, DATE_TRUNC('week', MIN(created_at)) AS cohort
  FROM forecast_events
  WHERE event_name = 'forecast_panel_viewed'
  GROUP BY 1
),
activity AS (
  SELECT DISTINCT namespace_id, DATE_TRUNC('week', created_at) AS active_week
  FROM forecast_events
  WHERE event_name = 'forecast_panel_viewed'
)
SELECT
  f.cohort,
  DATE_DIFF('week', f.cohort, a.active_week) AS week_n,
  COUNT(DISTINCT a.namespace_id)             AS active
FROM first_week f
JOIN activity a ON a.namespace_id = f.namespace_id
GROUP BY 1, 2
ORDER BY 1, 2;

Divide each row's active by its cohort's week-0 count to get retention percentages. Date arithmetic varies by warehouse; this version uses DuckDB, which students run on their own laptops.

The fan-out join: how totals inflate silently

Here's the trap that catches analysts, PMs and AI assistants alike. Take a toy dataset: two customer namespaces with 6 and 2 forecast panel views (8 in total), plus one internal namespace with 9. Namespace 4821 has three milestones; 5170 has one. Now someone joins events to milestones, perhaps to filter on milestone eligibility:

SELECT COUNT(*)                       AS panel_views,
       COUNT(DISTINCT e.namespace_id) AS groups
FROM forecast_events e
JOIN namespaces n ON n.namespace_id = e.namespace_id
JOIN milestones m ON m.namespace_id = e.namespace_id   -- one row per milestone!
WHERE n.is_internal = FALSE;

panel_views | groups
         20 |      2      -- the truth is 8 views; COUNT(DISTINCT) still looks fine

Every event for namespace 4821 matched three milestone rows, so it was counted three times: 6 × 3 + 2 × 1 = 20. Worse, the distinct count of namespaces is still correct, so a quick sanity check on “groups” passes. COUNT(DISTINCT …) hides fan-out for counts of entities, never for sums or row counts.

The fix is to bring every table to the same grain before joining:

-- Bring every table to the same grain (one row per namespace) before joining
WITH views AS (
  SELECT namespace_id, COUNT(*) AS panel_views
  FROM forecast_events
  WHERE event_name = 'forecast_panel_viewed'
  GROUP BY 1
),
ms AS (
  SELECT namespace_id, COUNT(*) AS milestones
  FROM milestones
  GROUP BY 1
)
SELECT n.namespace_id, n.plan_tier, v.panel_views, ms.milestones
FROM namespaces n
JOIN views v USING (namespace_id)
JOIN ms      USING (namespace_id)
WHERE n.is_internal = FALSE;

namespace_id | plan_tier | panel_views | milestones
        4821 | premium   |           6 |          3
        5170 | free      |           2 |          1

Reading other people's queries (and AI's)

PMs read SQL more often than they write it from scratch. A six-question review checklist catches most problems:

1. What's the grain of each table?
2. Are the joins one-to-one, or one-to-many (which multiplies rows)?
3. Are internal and test accounts excluded?
4. Is the date range half-open, and in UTC?
5. Is it counting events or distinct entities?
6. Does the denominator match the question?

Large language models write passable SQL from plain-language questions, and they're genuinely useful for drafts and for explaining unfamiliar queries. They also make every mistake above, confidently, especially about grain and definitions they can't see. Treat AI-written queries like a junior analyst's: read them, check the definitions, and compare the result against a number you already trust.

Simpson's paradox: when the total lies about every part

Beacon measures forecast accuracy by back-testing: of the milestones and epics that closed, what share finished inside the forecast's 85% range? The team shipped a fix for epic forecasts between Q3 and Q4. Here's the quarterly report:

SELECT quarter, type,
       SUM(inside_85)                    AS inside,
       COUNT(*)                          AS total,
       ROUND(100.0 * AVG(inside_85), 0)  AS accuracy_pct
FROM forecast_backtests
GROUP BY 1, 2
ORDER BY 1, 2 DESC;

Report only the blended number and you'd conclude the fix did nothing. Report by type and you see a nine-point improvement in exactly the place the team worked on. That's why Beacon's dashboard specification says, in a footnote, “by type, never only blended.”

A statistics primer in four sentences

Most product distributions are skewed, so prefer medians and percentiles to means; engineering teams set performance targets on p95 because users remember slow experiences, not average ones. A rate is only as meaningful as its denominator, so report numerator and denominator together. Every metric moves randomly week to week, so a single week is rarely evidence of anything and small groups are noisy. And beware the classics: regression to the mean, base-rate neglect, survivorship bias and slicing data until something looks significant.

Write the decision, not just the chart

Data informs decisions; it doesn't make them. Here's the memo Beacon's product manager wrote when choosing what to build next:

The last section is the most useful habit in the memo. It turns a decision into a testable bet and tells everyone what evidence to watch.

How students practice this

SQL starts in week 9 on a real engine (DuckDB, free, on your own laptop) and runs every week after: velocity from an issues table, joins across people and iterations (where everyone meets the fan-out trap on purpose), incident timelines rebuilt from event logs, earned value from a cost ledger, funnels and cohorts, and finally a full question → SQL → Python → Tableau → decision memo exercise in week 21. Students use Claude Code or Codex to draft queries, and every review checks whether they caught what the agent got wrong.

Adapted from Projects, For Real, chapter 60. The projects, people, vendors and numbers are the book's fictional case studies, set against GitLab's publicly documented practices. Gantt Guild is not affiliated with GitLab Inc.