Planned lessonMID-011-L07

Demo: building the monthly sales report in SQL

On This Lesson 3 topics

This lesson is planned. The outline below lists the topics it will cover. The full lesson has not been published yet.

In this lesson

build the monthly report as one query that uses CTEs, windows, and FILTER.

Draft outline details

Show the Java version that loads all orders and aggregates in memory; note its row count and time.
Write a CTE monthly that groups paid orders by date_trunc('month', created_at) and category.
Add rank() OVER (PARTITION BY month ORDER BY revenue DESC) and keep the top 3.
Add lag(revenue) for month-over-month change and FILTER for refunded counts.
Planned mistake: put the rank() condition in WHERE; PostgreSQL rejects it. Fix: wrap in a CTE and filter outside.
Compare time and data transferred with the Java version.

Prepares MID-011-LAB1.

Why is aggregating in SQL usually better than in Java?

Show answer

Less data crosses the network and the database is built for it.

Apply this lesson with the module's lab, exercises and quiz.

Marking a lesson complete is optional and you can undo it any time. Your progress stays in this browser.