SQL from zero · Chapter 19 of 37

Running totals and trends

By the end of this chapter you can add up rows as you go, average the last few, compare each row with the one before it, and show each row's share of the whole. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: Analytics
  • 4 tasks
  • SQL
  • In the all labs plan

The first 2 chapters of SQL from zero are free. This one comes with the all labs plan.

Why you need this

The board liked Meera's leaderboards. Now they want lines going up: how many members MingleSphere had at the end of each month, whether anya's posts are trending up or down, who brings in the likes, and who is drifting away.

Each of those needs one row to see other rows. A total that includes every month before this one, an average of the last three posts, the time since the previous post, a share of the grand total. With GROUP BY you get one number per group, not a number per row that depends on its neighbours. The only other way is a subquery per row ("sum of the months up to this one"), which is slow to write, slow to run and easy to get wrong at the edges.

What you build: 4 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    signups-running.sql: signups per month with a running total

    For each month in which at least one member joined (their created_at, India time), how many members joined that month, and how many had joined in total by the end of that month. Every member counts, members who left included. month is a date: the first day of the month, like 2026-01-01.

    Return three columns: month, signups and total. Oldest month first.

  2. Task 2

    moving-average.sql: anya's posts with the average of the last 3

    anya's posts that are not deleted, each with avg_last_3: the average likes_count of this post and her 2 posts just before it, in time order. Her first post averages just itself, her second averages two posts.

    Return three columns: created_at, likes_count and avg_last_3. Oldest post first. Round avg_last_3 to 2 decimals.

  3. Task 3

    share-of-likes.sql: each member's share of all likes

    Each member whose posts that are not deleted got at least one like (members who left included): likes is the total likes_count of those posts, and share is that total as a percent of all those likes across MingleSphere, so 13.2 means 13.2%. Members whose live posts got no likes do not appear.

    Return three columns: username, likes and share. Any order. Round share to 1 decimal.

  4. Task 4 · interview question

    Interview: posting-gaps.sql, the time since each member's previous post

    Every post that is not deleted (by any member, members who left included), with gap: the time since the same member's previous post that is not deleted, as an interval (this created_at minus that one). A member's first post has an empty (NULL) gap. Deleted posts do not count, not even as the previous post.

    Return three columns: username, created_at and gap. Any order.

What you will learn

  • Running totals: sum(...) OVER (ORDER BY ...) adds up every row so far.
  • lag and lead: a value from the row before or after.
  • Frames: ROWS BETWEEN ... AND CURRENT ROW, the last few rows only, for a moving average.
  • OVER (): the whole result, so each row can see the grand total.

More in Analytics

Build "Running totals and trends" yourself

MingleSphereQL, a social app, needs answers from its data every day. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.

The first 2 chapters of SQL from zero are free. This one comes with the all labs plan.