By FDE Interview Prep, a SourceStrongAI company

In this post13 sections
  1. The short answer
  2. The number looks right and is wrong
  3. How a one-to-many join multiplies rows
  4. The row-count check that catches fan-out
  5. Fix one: aggregate to the grain before you join
  6. Fix two: EXISTS when you only need to filter
  7. Fix three: reduce the many side to one row per key
  8. Why DISTINCT on a sum is a trap
  9. Explaining the bug to a non-technical stakeholder
  10. What to say in the interview, start to finish
  11. Questions people ask
  12. Keep reading
  13. More from the blog

Your query runs, the revenue figure looks reasonable, and it is wrong. On a real deployment, the customer’s CFO notices, because the figure does not match the ledger. In a SQL debugging exercise, the bug is there on purpose, and the question is whether you catch it before the interviewer points at it. The cause is usually join fan-out: you joined a table that has several rows per key, every row on the other side was repeated once per match, and the sum came out too high. This post is one part of the FDE interview guide, which walks the whole loop round by round; here we take one SQL bug apart on tables small enough to check by hand.

The short answer

Join fan-out (often typed “fanout”) is what happens when a one-to-many join repeats the “one” side, so sums and counts after the join double count. You catch it by counting rows and distinct keys at the grain you expect, before and after each join. You fix it in one of three ways:

  • Aggregate the many side first, so it has one row per key before you join.
  • Use EXISTS when the other table only filters and contributes no numbers.
  • Reduce the many side to one row per key, for example the latest status per order.

What you never do is add DISTINCT until the number looks right. The rest of this post shows each step with runnable SQL and gives you the words to say in the interview and to the customer.

The number looks right and is wrong

Here is a made-up scenario with a real shape. A regional distributor’s finance team asks for last month’s revenue, and for the revenue from orders that used a promo code. You join orders to order_items, sum the amounts, and hand over two normal-looking figures. Nothing errored. When the SQL arrives inside a bigger prompt, such as “here is a dataset, what would you build?”, the grain check is still your first move; the post on decomposition interviews that come with a dataset shows how to start from the columns.

That is what makes it a good debugging exercise: it tests whether you check a number before you trust it, which matters more on a customer’s data than whether you remember a function name. A broken report is someone else’s code, one of the round types the free lesson on what FDE coding rounds test sorts by company.

As of September 2026, postings at Rippling (a manager role asking for SQL and data modeling), Databricks and Notion list SQL among the skills they want. Source 1Manager, Forward Deployed EngineeringPublisherRippling (ats.rippling.com)Source typecompany job postingSource 2Sr. Deployment Strategist, FDE - Financial ServicesPublisherDatabricks (careers site / Greenhouse)Source typecompany job postingSource 3Forward Deployed Engineer, GTM, AMER @ NotionPublisherNotion (Ashby job board)Source typecompany job postingSource 4Forward Deployed Engineer @ NotionPublisherNotion (Ashby job board)Source typecompany job posting One candidate on Aced, interviewing for Palantir’s (Gov) role in August 2025, wrote that the hardest part for them was learning SQL, which they had never used before. Source 5Palantir Forward Deployed Engineer Interview ExperiencePublisherAced (formerly Exponent)Source typecandidate’s personal write-up One poster on Reddit described the online assessment they had taken for HackerRank’s Forward Deployed Engineer role, in January 2026, as four questions: an API call, SQL, Node and a business question. Source 6HackerRank forward deployed engineer interview process (comment by u/pranav_india)PublisherReddit r/developersIndiaSource typecandidate report on RedditSource 7HackerRank forward deployed engineer interview process (comment by u/Strange-Egg5496)PublisherReddit r/developersIndiaSource typecandidate report on RedditSource 8HackerRank Forward Deployed Engineer Online Assessment insights? (post by u/ParticularSpirit3387)PublisherReddit r/leetcodeSource typecandidate report on RedditSource 9HackerRank Forward Deployed Engineer Online Assessment insights? (comment by u/ParticularSpirit3387)PublisherReddit r/leetcodeSource typecandidate report on Reddit A commenter in a Reddit thread about Salesforce’s Agentforce FDE role wrote, in August 2026, that the assessment they cleared included some basic SQL. Source 10Forward Deployed Engineer (Agentforce) (comment by u/joemons)PublisherReddit r/SalesforceCareersSource typecandidate report on Reddit

How a one-to-many join multiplies rows

Start with the tables. Every block below runs in SQLite.

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer TEXT,
  amount   INTEGER
);
INSERT INTO orders VALUES
  (1, 'Acme',  10),
  (2, 'Birch', 10),
  (3, 'Cedar',  5);

CREATE TABLE order_items (
  order_id INTEGER,
  sku      TEXT,
  promo    INTEGER
);
INSERT INTO order_items VALUES
  (1, 'A', 1),
  (1, 'B', 1),
  (2, 'A', 0),
  (3, 'C', 1);

The true revenue is the sum of orders.amount: 25. Now the query most people write first:

SELECT SUM(o.amount) AS revenue
FROM orders o
JOIN order_items i
  ON i.order_id = o.order_id;
-- revenue: 35

Look at the rows the SUM actually saw:

order_idamountsku
110A
110B
210A
35C

Order 1 has two items, so its amount appears once per item. That is the whole bug. The word for it is grain: the grain of a table is what one row means. orders is one row per order. order_items is one row per order line. The moment you join them, the result is at the line grain, and any order-level number you sum there is repeated once per line.

Fan-out gets worse when two many sides hang off the same parent. Join items and payments to the same order and they multiply each other: every item row repeats per payment row and every payment row repeats per item row. The free question fix a join fan-out that inflates revenue is built on exactly that case, with a model answer you can compare yours to.

The row-count check that catches fan-out

Before you trust any joined total, run two checks and say them out loud.

Check one: rows against keys, before and after the join. At the order grain, the number of rows should equal the number of distinct orders.

SELECT
  (SELECT COUNT(*) FROM orders)
    AS orders_before,
  COUNT(*) AS rows_after,
  COUNT(DISTINCT o.order_id)
    AS orders_after
FROM orders o
JOIN order_items i
  ON i.order_id = o.order_id;
-- orders_before: 3, rows_after: 4,
-- orders_after: 3

More rows than orders means the join fanned out. Fewer orders after than before means an inner join dropped orders with no match, which is the opposite bug and just as wrong.

Check two: find the key that repeats.

SELECT order_id, COUNT(*) AS n
FROM order_items
GROUP BY order_id
HAVING COUNT(*) > 1;
-- order_id 1, n 2

Any row here is a key that will fan out. Run it on every table you join, not just the one you suspect.

Then reconcile. Compute the total from the source table alone and compare it with your report:

SELECT
  (SELECT SUM(amount) FROM orders)
    AS source_total,
  (SELECT SUM(o.amount)
     FROM orders o
     JOIN order_items i
       ON i.order_id = o.order_id)
    AS report_total;
-- source_total: 25, report_total: 35

Narrate the grain before you write the join

Say it as you type: “Orders is one row per order. Items is one row per line, so this join takes me to the line grain. I only want order amounts, so I’ll check the row count before I sum.” The interviewer hears that you knew the risk, not that you got lucky.

Fan-out also costs time on a real table. In PostgreSQL, EXPLAIN ANALYZE shows it in the query plan: the actual row count jumps at the join node, and every step after it does more work. The free question on reading a plan for a query that got slow trains you to find the node where rows and time jump.

Fix one: aggregate to the grain before you join

Use this when you need a number from the many side, such as how many items each order has. Bring the many side down to one row per order first, then join.

WITH items AS (
  SELECT order_id,
         COUNT(*) AS n_items
  FROM order_items
  GROUP BY order_id
)
SELECT SUM(o.amount) AS revenue,
  SUM(COALESCE(i.n_items, 0))
    AS items
FROM orders o
LEFT JOIN items i
  ON i.order_id = o.order_id;
-- revenue: 25, items: 4

Use LEFT JOIN here, so an order with no lines still counts toward revenue. After the CTE, items has exactly one row per order_id, so the join is one-to-one and nothing repeats. When two many sides hang off one parent, give each its own CTE and join both at the parent’s grain. This is the fix to reach for first, because it makes the grain explicit in the code where the next person can see it.

Fix two: EXISTS when you only need to filter

The finance team’s second question was revenue from orders that used a promo. The join version looks innocent:

SELECT SUM(o.amount) AS promo_revenue
FROM orders o
JOIN order_items i
  ON i.order_id = o.order_id
WHERE i.promo = 1;
-- promo_revenue: 25

Order 1 has two promo lines, so it is counted once per line. Notice the result: it happens to equal the true total for all orders. A wrong number that matches a familiar one is the most convincing kind of wrong.

order_items contributes no numbers here; it only answers a yes or no question about each order. That is what EXISTS is for:

SELECT SUM(o.amount) AS promo_revenue
FROM orders o
WHERE EXISTS (
  SELECT 1
  FROM order_items i
  WHERE i.order_id = o.order_id
    AND i.promo = 1
);
-- promo_revenue: 15

EXISTS asks whether at least one match exists and never repeats the outer row, however many matches there are. For the opposite question, orders with no promo line, use NOT EXISTS rather than NOT IN: if the subquery returns even one NULL, NOT IN matches no rows at all.

Fix three: reduce the many side to one row per key

Sometimes the many side is a history, and you want one row from it. Say the customer keeps every status change:

CREATE TABLE order_status (
  order_id   INTEGER,
  status     TEXT,
  changed_at TEXT
);
INSERT INTO order_status VALUES
  (1, 'placed',   '2026-09-01'),
  (1, 'shipped',  '2026-09-03'),
  (2, 'placed',   '2026-09-02'),
  (3, 'placed',   '2026-09-02'),
  (3, 'canceled', '2026-09-04');

Join it straight in and group by status, and you get placed 25, shipped 10, canceled 5. Those add up to 40 against a true 25, because orders 1 and 3 each count under their old status and their new one. Pick the latest row per order first:

WITH latest AS (
  SELECT order_id, status,
    ROW_NUMBER() OVER (
      PARTITION BY order_id
      ORDER BY changed_at DESC
    ) AS rn
  FROM order_status
)
SELECT l.status,
       SUM(o.amount) AS revenue
FROM orders o
JOIN latest l
  ON l.order_id = o.order_id
 AND l.rn = 1
GROUP BY l.status;
-- canceled 5, placed 10, shipped 10

An order with no status row drops out of this inner join, so check the count against orders or switch to LEFT JOIN. Say the tie rule out loud: if two changes share a timestamp, ROW_NUMBER picks one of them arbitrarily, so add a tiebreaker column to the ORDER BY or ask the customer which one wins. Then rerun check two on latest filtered to rn = 1 to prove it is one row per key.

The same trap hides in dimension tables that keep history, such as a customer table with one row per version of the customer. Join each order to the one version that was valid on the order date, or to the current row only when the question is about today. The glossary entry on the slowly changing dimension shows the half-open date range that keeps it to one row per key. For more on ROW_NUMBER, read the post on SQL window functions for FDE interviews and try the free question on the latest status per ticket.

Why DISTINCT on a sum is a trap

Under time pressure, the tempting fix is one keyword:

SELECT SUM(DISTINCT o.amount)
  AS revenue
FROM orders o
JOIN order_items i
  ON i.order_id = o.order_id;
-- revenue: 15

Now the number is too low. SUM(DISTINCT ...) removes repeated values, not repeated rows. Orders 1 and 2 are different orders that both happen to be worth 10, so one of them vanishes. On a customer’s real data, equal amounts are everywhere: the same subscription price, the same shipping fee, the same unit price.

SELECT DISTINCT over the joined rows fails in a different way. The repeated order rows differ in sku, so nothing collapses, and when you drop columns until they do collapse, you also merge real rows that were meant to be separate. If DISTINCT ever makes the number right, the data made it right by luck, and the next load breaks it.

In an interview, name the temptation and turn it down: “I could add DISTINCT, but that removes equal values, not duplicate rows. I’d rather fix the grain.”

Explaining the bug to a non-technical stakeholder

Finding the bug is half the job. In our method, the other half is telling the customer in words that do not need SQL, without hiding the size of the mistake. Five parts, in this order:

  1. The correct figure first. They need the number before the story.
  2. What was wrong, in business terms. Orders, not rows. Lines, not joins.
  3. Direction and scope. Too high or too low, which reports, which dates.
  4. What you changed. The fix, in one sentence.
  5. How it stays fixed. The check that now runs.

Here is what that sounds like for our made-up distributor:

“Last month’s revenue was [correct figure], not the [dashboard figure] the dashboard showed, so [difference] lower. The report counted an order once for every line on it, so any order with several products was counted more than once. The same query feeds the weekly sales report, so I have corrected that too, back to [first affected month]. The figure now matches the order totals in your system, and I have added a check that compares the report with those totals every night, so if this happens again we hear about it before you do.”

No “join”, no “cardinality”, no “fan-out”. The CFO can repeat every sentence to the board. The same habit holds when the mistake is an AI system’s rather than a query’s; the post on explaining AI mistakes to a non-technical executive works through that version. The Pro lesson on writing for customers covers the follow-up email that records the correction.

What to say in the interview, start to finish

When the interviewer hands you a report with an inflated total, this order keeps you out of trouble:

  1. “Let me get the true total from the source table first, with no joins.”
  2. “Here is the grain of each table.” Name them.
  3. “This one isn’t unique on the join key.” Show the HAVING COUNT(*) > 1 result.
  4. “I’ll aggregate it to the order first,” or “I only need it as a filter, so EXISTS.”
  5. “Now the totals reconcile.” Show both numbers side by side.
  6. “To stop it happening again, I’d add a uniqueness test on the key and a test that the report total equals the source total.”

Before you trust a joined total

  • I can say the grain of every table in the query.
  • I counted rows and distinct keys before and after each join.
  • Every table I join is unique on the join key, or I aggregated it first.
  • Tables that only filter are in EXISTS, not in a join.
  • No DISTINCT inside a SUM or COUNT is doing the work of a fix.
  • The report total reconciles with the source table.

To practice the customer half out loud, the free practice case puts you in front of an AI customer who answers your questions.

The free join fan-out question goes one step past this post: items and payments both hang off the order, and an inner join quietly drops the unpaid ones, so the error is not a clean multiple. Time yourself, say every check out loud, then compare with the model answer.

GlossaryForward deployed engineerA software engineer who builds and ships production systems inside a customer’s problem and environment, accountable to that customer’s outcome.More on Forward deployed engineerGlossaryForward deployed software engineerPalantir’s title for its FDE role, called Delta internally; OpenAI and EY also post FDSE titles, each with its own duties.More on Forward deployed software engineer

Questions people ask

What is join fan-out in SQL?

Fan-out happens when you join a table to another that has several matching rows per key. Each row on the one side is repeated once per match, so sums and counts taken after the join come out too high.

Does DISTINCT fix double counting?

Not reliably. SUM(DISTINCT amount) removes repeated values, not repeated rows, so two different orders with the same amount are counted once and the total comes out too low. Fix the grain instead. Aggregate the many side to one row per key before joining, or use EXISTS.

How do you check a join for fan-out in an interview?

Count rows and distinct keys at the grain you expect before the join, then count again after it. If the row count grew when the grain was meant to stay the same, the join fanned out. Say the check out loud so the interviewer hears it.

Keep reading

Guides

Lessons

Questions

More in Interview rounds, page 4