BrevFeed
Sign in

53. Capstone: an analytics report suite

📖 Reading · 6 min
💡 Every code box below is live — edit it and hit Run.

The brief

The pagila DVD rental chain's management wants a single report answering four real business questions, all against the actual transactional data you've been using all course:

  1. Monthly revenue trend — total revenue per calendar month, with the month-over-month dollar change.
  2. Top 3 films by revenue, per category — not just top 3 overall, per category (Action, Comedy, etc.).
  3. Customer value tiers — every customer bucketed into quartiles by total spend, so marketing can target the top quartile differently from the bottom.
  4. Rolling 7-day rental volume — a smoothed daily view of rental counts (raw daily counts are noisy; a trailing 7-day average is the standard smoothing technique for this).

Your task

Attempt each of the four questions yourself before reading the reference solution. Identify which modules' techniques each one needs before writing anything:

  1. Monthly trend needs a GROUP BY for the monthly total (Module 5) plus LAG() for the month-over-month change (Module 7's analytic-functions lesson).
  2. Top-N-per-group needs ROW_NUMBER() OVER (PARTITION BY ...) filtered in an outer query (Module 7's practical-patterns lesson).
  3. Quartiles need NTILE(4) (Module 7's ranking lesson).
  4. A rolling average needs a ROWS BETWEEN N PRECEDING AND CURRENT ROW frame (Module 7's frames lesson).

1. Monthly revenue trend

WITH monthly_revenue AS (
    SELECT date_trunc('month', payment_date) AS month, sum(amount) AS revenue
    FROM payment
    GROUP BY date_trunc('month', payment_date)
)
SELECT
    month,
    revenue,
    revenue - lag(revenue) OVER (ORDER BY month) AS change_from_prev_month
FROM monthly_revenue
ORDER BY month;

2. Top 3 films by revenue, per category

WITH film_category_revenue AS (
    SELECT c.name AS category, f.title, sum(p.amount) AS revenue
    FROM payment p
    JOIN rental r ON r.rental_id = p.rental_id
    JOIN inventory i ON i.inventory_id = r.inventory_id
    JOIN film f ON f.film_id = i.film_id
    JOIN film_category fc ON fc.film_id = f.film_id
    JOIN category c ON c.category_id = fc.category_id
    GROUP BY c.name, f.title
),
ranked AS (
    SELECT *, row_number() OVER (PARTITION BY category ORDER BY revenue DESC) AS rn
    FROM film_category_revenue
)
SELECT category, title, revenue FROM ranked WHERE rn <= 3 ORDER BY category, rn;

3. Customer value tiers (quartiles)

WITH customer_spend AS (
    SELECT customer_id, sum(amount) AS total_spend
    FROM payment
    GROUP BY customer_id
)
SELECT
    customer_id, total_spend,
    ntile(4) OVER (ORDER BY total_spend) AS spend_quartile
FROM customer_spend
ORDER BY total_spend DESC;

4. Rolling 7-day rental volume

WITH daily_rentals AS (
    SELECT rental_date::date AS day, count(*) AS rental_count
    FROM rental
    GROUP BY rental_date::date
)
SELECT
    day, rental_count,
    avg(rental_count) OVER (
        ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS rolling_7day_avg
FROM daily_rentals
ORDER BY day;

Assembling it into one report

A real deliverable would present all four as a single named view or set of views (Module 9) rather than four ad hoc queries — build each as its own view so the report is a stable, queryable artifact, not a one-time script.

Try it

A genuinely honest wrinkle, caught while verifying: pagila's rental dates have a real gap (clustered around mid-February, then a gap until late May). ROWS BETWEEN 6 PRECEDING AND CURRENT ROW counts rows, not calendar days — daily_rentals only has a row for days with at least one rental, so a window spanning that gap silently covers far more than 7 calendar days, even though it's still exactly 7 rows. This is the honest distinction between "rolling N rows" and "rolling N calendar days" — the two coincide only when every day in range has at least one row, which real transactional data won't always guarantee. A stricter "true calendar week" version would need to generate_series (see Module 6's recursive-CTE lesson) every date in range first, LEFT JOIN rental counts onto it (so gap days appear as explicit 0 rows), and only then apply the window — worth doing if the gap behavior actually matters for your report, not needed if rows-based smoothing is good enough.

Check yourself

  1. Why does the first row of the monthly revenue trend have a NULL change_from_prev_month, and is that correct or a bug?
  2. In the top-3-per-category query, why is ROW_NUMBER() inside a CTE filtered in an outer query, rather than filtered directly with WHERE row_number() OVER (...) <= 3?
  3. What would change about the rolling-average query if you wanted a centered 7-day average (3 days before, the current day, 3 days after) instead of a trailing one?
← Previous Next →