SQL window functions for FDE interviews: four patterns and how to explain them

By FDE Interview Prep, a SourceStrongAI company

In this post11 sections

1. Where SQL shows up in FDE hiring
2. Window functions in one minute: partition, order and frame
3. Running totals per customer, and the frame trap with ties
4. The latest row per group with ROW_NUMBER
5. Change since last time with LAG
6. Sessionizing events from the gaps between them
7. How to explain a window query out loud
8. Seven checks before you say you’re done
9. Questions people ask
10. Keep reading
11. More from the blog

You have an FDE interview coming up, the posting mentions SQL, and the last time you wrote OVER (PARTITION BY ...) was a while ago. You don’t need the whole manual. You need a handful of window patterns you can write from memory, and one clear sentence for each that tells the interviewer you know why the query is right. This post gives you four: running totals, the latest row per group, change since the last row, and sessions. Every query here was run in SQLite. For the rest of the loop, round by round, read the FDE interview guide.

Where SQL shows up in FDE hiring

The short answer to “will I get SQL?” is: at some companies, and none of the postings cited here says how much of a loop is SQL.

Some postings name it. As of September 2026, Rippling’s Manager, Forward Deployed Engineering posting asks for a general-purpose language “along with SQL and data modeling”. ^[1]Source 1Manager, Forward Deployed EngineeringPublisherRippling (ats.rippling.com)Source typecompany job posting Notion’s IC Forward Deployed Engineer postings list SQL among the languages a candidate can be proficient in. ^{[2, 3]}Source 2Forward Deployed Engineer, GTM, AMER @ NotionPublisherNotion (Ashby job board)Source typecompany job postingSource 3Forward Deployed Engineer @ NotionPublisherNotion (Ashby job board)Source typecompany job posting Databricks’ Sr. Deployment Strategist, FDE postings say candidates are proficient in a language such as Python, Java or SQL and can explore data and work in notebooks. ^[4]Source 4Sr. Deployment Strategist, FDE - Financial ServicesPublisherDatabricks (careers site / Greenhouse)Source typecompany job posting

Some candidates report it in assessments, and one thread argues about it:

• One Blind commenter reported, in November 2025, that their Palantir FDSE process began with an online assessment of three questions (coding, SQL and API); the same comment promotes a third-party site. ^[5]Source 5Palantir FDSE Interview (Blind thread)PublisherBlindSource typecandidate report on Blind How to prepare for FDE online assessments covers that format.
• One commenter in a Reddit thread about Salesforce’s Agentforce FDE role reported, in August 2026, an assessment that included “some basic SQL”. ^[6]Source 6Forward Deployed Engineer (Agentforce) (comment by u/joemons)PublisherReddit r/SalesforceCareersSource typecandidate report on Reddit
• One entry-level candidate for Palantir’s Forward Deployed Software Engineer (Gov) role wrote on Aced, about an August 2025 interview, that the hardest part for them was learning SQL, which they had never used before. ^[7]Source 7Palantir Forward Deployed Engineer Interview ExperiencePublisherAced (formerly Exponent)Source typecandidate’s personal write-up
• In a February 2026 Blind thread about Snowflake’s FDE interview, one Cloudera-labeled commenter said FDEs must write SQL fast, and Snowflake-labeled commenters pushed back; none of them described a round they had sat. ^[8]Source 8Snowflake FDE interview? | Tech Industry - BlindPublisherBlindSource typecandidate report on Blind

So if your targets list SQL, practice it on purpose. The free lesson on reading the evidence for a target company turns postings and reports like these into a practice list. The four patterns below are our selection, chosen because each one hides a trap that a correct-looking query walks into.

Window functions in one minute: partition, order and frame

A window function computes a value for each row from a set of related rows, and keeps every row. GROUP BY collapses rows; a window does not. That is the whole reason to reach for one.

Every window has up to three parts:

• Partition: which rows belong together. PARTITION BY cust restarts the calculation for each customer.
• Order: the sequence inside the partition. ORDER BY day makes “previous” and “so far” mean something.
• Frame: which rows around the current one count. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW means “from the first row up to this one”.

If you remember one thing, remember this: with an ORDER BY and no frame, the default frame is RANGE, not ROWS. That is the SQL standard, and SQLite, PostgreSQL and Snowflake all follow it for SUM. The first pattern shows why that matters.

Running totals per customer, and the frame trap with ties

The prompt: “Show a running total of revenue per customer.” Here is a tiny orders table, with two orders from acme on the same day.

CREATE TABLE orders
  (id INT, cust TEXT, day TEXT, amt INT);
INSERT INTO orders VALUES
  (1, 'acme', '2026-03-01', 7),
  (2, 'acme', '2026-03-02', 8),
  (3, 'acme', '2026-03-02', 7),
  (4, 'bolt', '2026-03-01', 5);

The first draft that looks right:

SELECT id, cust, day, amt,
  SUM(amt) OVER (
    PARTITION BY cust
    ORDER BY day
  ) AS running
FROM orders;

It looks right until you read the acme rows:

Orders 2 and 3 share a day, so under the default RANGE frame they are peers, and each one’s total includes the other. Both show 22. If the question was “the balance after each order”, that is wrong. The SQLite documentation on window frames, PostgreSQL’s window function calls and Snowflake’s window function syntax all describe this default.

The fix is to say the frame and give the order a tie-breaker:

SUM(amt) OVER (
  PARTITION BY cust
  ORDER BY day, id
  ROWS BETWEEN UNBOUNDED PRECEDING
    AND CURRENT ROW
) AS running

Now the acme totals go 7, 15, 22. Without id in the ORDER BY, ROWS would still split the tie, but in whatever order the engine picks, so the numbers could change between runs.

Often the better answer is to ask about the grain first. If the customer wants one number per customer per day, aggregate to daily rows in a CTE, then window over those. With one row per day there are no ties, and both frames agree.

A seven-row moving average is the same window with AVG and ROWS BETWEEN 6 PRECEDING AND CURRENT ROW. Like LAG below, it counts rows, not days, so fill missing days first.

Say it like this: “I partition by customer so the total restarts for each one, order by day and id so ties have a fixed order, and I write the frame as ROWS because the default RANGE frame gives tied rows the same total.”

Practice it on the running total question, which adds a date spine for days with no orders.

The latest row per group with ROW_NUMBER

The prompt: “For each ticket, return its current status.” The history table has one row per change, and an automation wrote two changes to ticket 101 at the same minute.

CREATE TABLE events (id INT,
  ticket INT, status TEXT, at TEXT);
INSERT INTO events VALUES
  (1, 101, 'open', '09:00'),
  (2, 101, 'pending', '10:00'),
  (3, 101, 'solved', '10:00'),
  (4, 102, 'open', '09:30');

SELECT ticket, status, at
FROM (
  SELECT ticket, status, at,
    ROW_NUMBER() OVER (
      PARTITION BY ticket
      ORDER BY at DESC, id DESC
    ) AS rn
  FROM events
) AS ranked
WHERE rn = 1;

Ticket 101 has open at 09:00, then pending and solved both at 10:00, with ids 2 and 3. The query returns solved for 101 and open for 102: one row per ticket.

Here is ticket 101 numbered both ways, with RANK ordered by at DESC alone:

Three things make it correct:

1. ROW_NUMBER, not RANK. On ticket 101, ROW_NUMBER numbers the two 10:00 rows 1 and 2. RANK gives both a 1, so filtering on rank one brings the duplicate back. DENSE_RANK does the same, without the gap after the tie.
2. A tie-breaker. When we ran it without id DESC, SQLite happened to pick pending. That is not a bug in SQLite; it is a query that never said which row wins.
3. The filter lives outside. WHERE runs before window functions are computed, so WHERE ROW_NUMBER() OVER (...) = 1 fails with “misuse of window function”. Compute the number in a subquery or CTE, then filter.

Change rn = 1 to rn <= 3 and the same query returns the top three per group.

A tempting wrong answer joins back on the maximum timestamp. On this data it returns both pending and solved for ticket 101: three rows for two tickets.

Say it like this: “I number each ticket’s rows from newest to oldest with ROW_NUMBER, breaking ties on the event id, and keep row one. ROW_NUMBER never repeats a number, so I get exactly one row per ticket even when two changes share a timestamp.”

Then ask the question that makes you sound like you’ve done this for a customer: “Does a higher id mean the change happened later, or just that it was loaded later?” The latest status per ticket question works through that follow-up and the scale version.

Change since last time with LAG

The prompt: “Show week-over-week change in active seats for each account.” LAG reads a value from the previous row in the window.

SELECT acct, wk, n,
  n - LAG(n) OVER (
    PARTITION BY acct
    ORDER BY wk
  ) AS delta
FROM seats;

Two details to say out loud.

The first row is null. There is no previous row, so LAG returns null and the subtraction does too. That is usually right: “no change” and “no earlier data” are different facts. If the customer wants a number, LAG(n, 1, 0) supplies a default, but then the first week reads as +40, a claim to agree with them before you ship it.

“Previous row” is not “last week”. Look at the dates: the week of 2026-03-16 is missing, so the -15 is a two-week drop shown as if it were one. Put LAG(wk) next to the delta, or check the gap, before anyone puts this on a chart. If every week must appear, build a calendar of weeks and left join to it first.

Say it like this: “LAG gives me the previous row for the same account in week order, so the delta is this week minus the last week we have data for. If weeks can be missing, I’d fill them first, because otherwise a two-week change looks like a one-week change.”

Sessionizing events from the gaps between them

The prompt: “Group page views into sessions, where 30 minutes of inactivity ends a session.” This is a gaps and islands problem, and it combines the two patterns above: LAG finds the gaps, a running SUM turns them into session numbers.

CREATE TABLE views
  (id INT, usr TEXT, ts TEXT);
INSERT INTO views VALUES
  (1, 'u1', '2026-03-02 10:00'),
  (2, 'u1', '2026-03-02 10:10'),
  (3, 'u1', '2026-03-02 10:40'),
  (4, 'u1', '2026-03-02 11:25');

WITH flagged AS (
  SELECT id, usr, ts,
    CASE WHEN unixepoch(ts)
      - unixepoch(LAG(ts) OVER w)
      <= 1800
    THEN 0 ELSE 1 END AS new_sess
  FROM views
  WINDOW w AS
    (PARTITION BY usr ORDER BY ts, id)
),
numbered AS (
  SELECT *, SUM(new_sess) OVER (
    PARTITION BY usr ORDER BY ts, id
    ROWS BETWEEN UNBOUNDED PRECEDING
      AND CURRENT ROW
  ) AS sess
  FROM flagged
)
SELECT usr, sess, MIN(ts) AS started,
  COUNT(*) AS views
FROM numbered
GROUP BY usr, sess;

On these views, the flags come out 1, 0, 0, 1, and the running sum gives sessions 1, 1, 1, 2. The gap from 10:10 to 10:40 is exactly 1800 seconds, so it stays in the first session; the 45-minute gap to 11:25 starts a new one.

What to point out:

• The first view flags itself. LAG is null on a user’s first row, the comparison is null, and the CASE falls to ELSE 1. No special case needed.
• The boundary is a decision. Is a gap of exactly the threshold the same session? Ask, then write <= or < to match.
• Partition by user everywhere. Without it, one user’s first view continues another user’s session.
• unixepoch() needs SQLite 3.38.0 or later. In PostgreSQL, subtract the timestamps and compare to an interval; in Snowflake, use DATEDIFF('second', ...); in BigQuery, TIMESTAMP_DIFF(..., SECOND).

Say it like this: “I flag a row as a session start when the gap to the user’s previous view is longer than the threshold, then a running sum of those flags numbers the sessions. Grouping by user and that number gives one row per session.”

The sessionize question adds the follow-ups: capping a session that never goes quiet, and sessionizing new rows each hour.

How to explain a window query out loud

The interviewer can read your SQL. What they can’t see is whether you know why it works. Use the same four beats every time:

1. Grain. “The input is one row per order; the output is one row per order with a running balance.”
2. Partition. “Partition by customer, so it restarts for each customer.”
3. Order and ties. “Order by day, then id, so the order is the same on every run.”
4. Frame or filter. “Frame from the first row to this one, written out” or “keep row one in the outer query”.

Then close with how you’d check it: “The row count should match the input,” or “the result should have one row per ticket, so I’d compare it to COUNT(DISTINCT ticket).” A check you name unprompted is worth more than a clever query you can’t defend.

If the data came from a customer, say what you’d ask them. “Which time zone defines a day?” and “Can two changes share a timestamp?” are questions an FDE asks on the first call. To rehearse that half, with a simulated customer asking back, try the free AI practice case.

Seven checks before you say you’re done

Another mistake isn’t about syntax: a join upstream of your window can multiply rows before the window ever runs, and every running total after it is inflated. Finding and fixing join fan-out covers the row-count check that catches it.

Every SQL question in our bank is free, with a model answer. Pick one, write the query from a blank editor, say the four beats out loud, then compare your answer with ours. When you want model answers for the other rounds, Pro opens the whole bank. Pro starts with a 7-day free trial.

Questions people ask

What do postings and candidates report about SQL in FDE interviews?

Rippling’s Manager, Forward Deployed Engineering posting asks for SQL and data modeling, and Notion’s FDE postings name SQL as one of the languages a candidate can be proficient in. One Blind commenter reported a SQL question in a Palantir FDSE online assessment, and a commenter in a Reddit thread about Salesforce’s Agentforce FDE role reported an assessment with some basic SQL. None of the postings cited here says how much of a loop is SQL.^{[1, 2, 3, 5, 6]}Source 1Manager, Forward Deployed EngineeringPublisherRippling (ats.rippling.com)Source typecompany job postingSource 2Forward Deployed Engineer, GTM, AMER @ NotionPublisherNotion (Ashby job board)Source typecompany job postingSource 3Forward Deployed Engineer @ NotionPublisherNotion (Ashby job board)Source typecompany job postingSource 5Palantir FDSE Interview (Blind thread)PublisherBlindSource typecandidate report on BlindSource 6Forward Deployed Engineer (Agentforce) (comment by u/joemons)PublisherReddit r/SalesforceCareersSource typecandidate report on Reddit

What is the difference between ROW_NUMBER, RANK and DENSE_RANK?

All three number rows inside a window. ROW_NUMBER gives tied rows different numbers, so it is the one to use when you must keep a single row per group. RANK gives ties the same number and skips the numbers after them; DENSE_RANK gives ties the same number without gaps.

Can you filter on a window function in WHERE?

No. WHERE is applied before window functions are computed, so compute the window in a subquery or CTE and filter in the outer query. Some warehouses add a QUALIFY clause for this; SQLite does not, so learn the subquery form.

Keep reading

Guides

• The forward deployed engineer interview, round by round

Lessons

• What FDE coding rounds test, by the evidence
• Dataset-first prompts and the short screen
• The evidence map: what employers publish, what candidates report, how to read both

Questions

• Write a running total of revenue per customer by day.
• For each ticket, return only its most recent status.
• Group page views into sessions separated by a period of inactivity.

More from the blog

1. Interview rounds

   Why your SQL revenue is too high: finding and fixing join fan-out in an interview

   A one-to-many join quietly inflates revenue. How to catch join fan-out with a row count, fix it at the right grain and explain the bug to a customer.

   9 min readPublished September 2026

2. Interview rounds

   ‘Tell me about a customer escalation you owned’: a structure and a worked answer

   How to answer ‘tell me about a customer escalation you owned’: a structure, a worked answer about a fictional customer, and the follow-ups to rehearse.

   11 min readPublished September 2026

3. Interview rounds

   A customer says requests are failing: how to run the first hour in an FDE interview

   A customer says requests are failing. Your first hour in the FDE interview: scope, timeline, recent changes, error classes, rollback and the update.

   11 min readPublished September 2026

More in Interview rounds, page 3