Level 6 · Where customers first buy

“First row per group” is one of the most common window function jobs: the first order per customer, the latest price per product, the top seller per category.

Recap: number, then filter

SELECT customer_id, order_id, channel,
       ROW_NUMBER() OVER (PARTITION BY customer_id
                          ORDER BY order_date, order_id) AS order_number
FROM orders
WHERE status = 'completed';

PARTITION BY customer_id restarts the numbering for every customer; ORDER BY order_date makes 1 the earliest. The order_id tie-breaker matters: without it, two orders with the same timestamp could swap places between runs, and your counts would wobble.

Customers who never completed an order have no rows here at all, so the denominator for the share is the 1,730 customers who did, not everyone in the customers table.

A window function cannot be filtered in the same query’s WHERE, because WHERE runs first. Wrap the numbering in a CTE and filter order_number = 1 outside.

What the answer shows

Of the 1,730 customers with a completed order, 893 (51.6%) placed their first one on the web, 593 (34.3%) in the app and 244 (14.1%) on the marketplace. Compare that with all completed orders, where the web has 49.5% and the app 35.8%. The first-order mix leans a little more to the web than the overall mix, which is consistent with the ticket’s hunch that customers move to the app later; the next step would be to count channel switches between order 1 and order 2.

SUM(COUNT(*)) OVER () looks odd at first: the inner COUNT(*) is the grouped count per channel, and the window sums those counts over the whole result. It gives the total without a second query.

Common mistakes

• No tie-breaker in the window’s ORDER BY.
• RANK() instead of ROW_NUMBER(): ties would give two rows with rank 1 for the same customer.
• Forgetting the status filter, so a cancelled first order decides the channel.

In SQL Server

The window syntax is the same. Neither SQL Server nor most other databases have DuckDB’s QUALIFY, so the CTE-then-filter pattern is the portable one. For a quick look at rows, SQL Server uses SELECT TOP 20 ... instead of LIMIT 20.