Interview › Percent of total

SQL interview: share of total with a window

The total is one window. The row stays. That is SUM() OVER () with an empty PARTITION.

Prompt. Each order as a percent of that customer’s spend, and of all spend.

Hint: SUM(total_amount) OVER (PARTITION BY customer_id)

Your T-SQL

SELECT order_id, customer_id, total_amount,
       total_amount * 1.0 / SUM(total_amount) OVER (PARTITION BY customer_id) AS pct_of_customer,
       total_amount * 1.0 / SUM(total_amount) OVER () AS pct_of_all
FROM Orders;

Times 1.0 (or CAST to decimal) or you get integer 0. NULLIF the total if it can be 0.

Then write it on a ticket

Monday is free. A colleague asks, you type, we mark it.

Clock in free