Drill 12 of 12 in Food delivery questions
0

Interview Question Bank · Monthly AVG · self-join on the previous month · gaps

12. Monthly rating vs the previous month

HardFood deliveryQuestion #240

Concept: Monthly AVG · self-join on the previous month · gapsshow

Month-over-month means comparing with the previous calendar month, not the previous row. LAG fetches the previous row, which is the wrong month whenever a group skipped one. Aggregate to one row per group and month, then LEFT JOIN that result to itself on prev.month = cur.month - INTERVAL '1 month', so a missing month gives NULL instead of an older value.

Example (different data):

WITH m AS (SELECT store, date_trunc('month', sold_on) AS month, SUM(amount) AS sales FROM sales GROUP BY 1, 2)
SELECT cur.store, cur.month, cur.sales, prev.sales AS prev_sales
FROM m cur
LEFT JOIN m prev ON prev.store = cur.store AND prev.month = cur.month - INTERVAL '1 month';

Your task

Brightbowl's partner managers review every restaurant's star rating monthly and flag the biggest drops against the month before.

For each restaurant and each month with at least one rating in fd_ratings, return restaurant (name), month (to_char(month, 'YYYY-MM')), avg_stars, prev_month_avg (the average for the previous calendar month, NULL if that month had no ratings) and change (avg_stars minus prev_month_avg, from the unrounded averages). Round all three to 2 decimals. Sort by restaurant, month.

Starting Postgres in your browser…

Checking your access…