7-Day Rolling Average Bookings | Dataford Interview Questions

Navan wants to monitor booking trends across countries in Navan Travel. Write a PostgreSQL query that calculates a seven-day rolling average of confirmed daily bookings for each active country from January 1 through January 10, 2024.

Requirements

Count only bookings with status = 'confirmed' and a valid country in countries.
Include dates with zero bookings so the rolling period represents seven calendar days.
Calculate the average of the current day and up to the six preceding days for each country.
Return results ordered by country code and booking date, with the rolling average rounded to three decimal places.

Schema

countries

Column	Type	Description
country_idPK	INTEGER	Unique country identifier
country_code	VARCHAR(2)	Two-letter country code
country_name	VARCHAR(100)	Country display name

bookings

Column	Type	Description
booking_idPK	INTEGER	Unique booking identifier
country_id	INTEGER	Country associated with the booking
booking_date	DATE	Date on which the booking was created
status	VARCHAR(20)	Booking lifecycle status

Your question is 7-Day Rolling Average Bookings. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem