Three day moving average of sign-ups

Medium

Question

The daily_signups table has one row for every calendar day with the number of new users who signed up that day. The rows are stored in no particular order.

For every day, return day, signups and moving_avg. The moving_avg is the average number of sign-ups of that day and the two days before it, rounded to 2 decimals.

For the first day there is no earlier day, so its average uses only the days that exist (one day, then two days). Sort by day.

The order of the rows matters for this question.

window function
moving average
avg over
window frame
round

Read first: Lesson 47, SQL window functions: ranks, running totals, LAG and frames

Tables

1 table

These are the example rows. Run uses only these rows. Submit also tests your query on a second set of rows that you cannot see.

daily_signups2 columns · 8 rowsnot run yet

CREATE TABLE daily_signups (day TEXT PRIMARY KEY, signups INTEGER NOT NULL);

Example rows of the daily_signups table
day	signups
2026-05-03	30
2026-05-01	10
2026-05-05	24
2026-05-02	20
2026-05-04	5
2026-05-07	40
2026-05-06	12
2026-05-08	21

About this SQL

Window frames (ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) work in SQLite 3.25 and later, like Postgres and MySQL 8. A frame near the start of the data is simply smaller, there is no error. ROUND returns a decimal number; AVG of whole numbers is already a decimal in SQLite.

SQLite

Opening the code editor…

Result

Run shows what your query gives on the example rows.Submit checks it on the example rows and on a hidden set of rows.

SQL · ready