SQL Window Functions in a Spreadsheet: Running Totals, Ranks per Group, Previous-Row Values, Moving Averages & Sessions

What you'll build

What you will be able to do

Each lab ends with a concrete result that you build yourself and the checker confirms, cell by cell.

• Put each customer's total and average next to every single order (the window-function way), work out each order's share of its customer's total, and compare it with a GROUP BY summary that collapses the rows.
• Build a running total per customer, a running total over all orders that handles two orders on the same day the way a ROWS frame does, and the running share of the month's revenue.
• Rank coffees inside their category with RANK and DENSE_RANK logic, give every row a unique ROW_NUMBER with a tie-breaker, and flag the top two per category – and see why ties change who is "top 2".
• Fetch each customer's previous order date (LAG), the days since that order, the change against the previous amount and the next order date (LEAD) – with formulas that work no matter how the log is sorted.
• Calculate a 7-day moving average over a date window, count how many days are really in each window, compare it with a 7-row moving average across a closed day, and flag revenue spikes against the trend.
• Sort subscription customers into spend quartiles, give each a percent rank between 0 and 1, split every region into a top and bottom half and find the spend level of the 75th percentile.
• Write each customer's first order date next to every order, turn it into a cohort label, fetch the amount of the first order and mark the latest order of each customer – and learn why LAST_VALUE so often returns the current row.
• Turn a raw click log into sessions: the minutes since each user's previous event (LAG), a new-session flag when the gap is over 30 minutes, a running session number per user (a running SUM of the flags) and the number of sessions per user.