Level 7 · App share of each month

A share needs two numbers on the same row: the part and the whole. A window function computes the whole without collapsing the rows.

COUNT(*) OVER versus GROUP BY

GROUP BY month returns one row per month. COUNT(*) OVER (PARTITION BY month) returns every input row and adds the month’s count to each one. That makes it easy to compare a row with its group:

SELECT order_id,
       channel,
       COUNT(*) OVER (PARTITION BY channel) AS channel_orders
FROM orders
WHERE status = 'completed'
LIMIT 5;

Each order carries the count for its whole channel (5,367 completed web orders, for example). In the reference solution, PARTITION BY month, channel gives the part, PARTITION BY month gives the whole, and DISTINCT removes the repeated rows.

The same result also comes from the starter’s grouped query plus one window on top of the grouped counts: SUM(COUNT(*)) OVER (PARTITION BY month). The inner COUNT(*) is per month and channel, and the window adds the three channels up. Both versions are fine; the checker compares results, not code.

Reading the result

The app’s share of completed orders rises from 32.8% in January 2025 to 50.3% in December, while the total in December (634 orders) is more than twice January’s 268. Shares and totals answer different questions, which is why the ticket wants both columns. A rising share could come from the app growing or from the other channels shrinking; only the counts next to it tell you which.

Common mistakes

• Filtering before the window. WHERE channel = 'app' removes web and marketplace rows before the window counts, so month_orders equals orders and every share is 100%. Compute all channels in a CTE and filter outside.
• Wrong partition. PARTITION BY channel divides by the channel’s yearly total, a different question.
• Integer division. Multiply by 100.0 before dividing.

In SQL Server

COUNT(*) OVER (PARTITION BY ...) works the same way, but the result is an int, and int divided by int truncates. Write 100.0 * COUNT(*) OVER (...) / COUNT(*) OVER (...) so the division happens in decimals. For the month, use DATETRUNC(month, order_date) (SQL Server 2022) or DATEFROMPARTS(YEAR(order_date), MONTH(order_date), 1).