python 36 lines · 7 steps

Build a monthly revenue report in Django

Grouping and aggregating orders by month entirely in SQL with Django's ORM.

Explained by highlit
Django Advanced aggregation orm group-by annotations reporting
1from django.db.models import Sum, Count, F, DecimalField
2from django.db.models.functions import TruncMonth, Coalesce
3 
4from .models import Order
5 
6 
7def monthly_revenue_report(year, *, status="paid"):
8 queryset = (
9 Order.objects.filter(
10 placed_at__year=year,
11 status=status,
12 )
13 .annotate(month=TruncMonth("placed_at"))
14 .values("month")
15 .annotate(
16 gross_revenue=Coalesce(
17 Sum(F("line_items__unit_price") * F("line_items__quantity")),
18 0,
19 output_field=DecimalField(max_digits=12, decimal_places=2),
20 ),
21 refunds=Coalesce(Sum("refunds__amount"), 0, output_field=DecimalField()),
22 order_count=Count("id", distinct=True),
23 )
24 .annotate(net_revenue=F("gross_revenue") - F("refunds"))
25 .order_by("month")
26 )
27 
28 return [
29 {
30 "month": row["month"].strftime("%Y-%m"),
31 "orders": row["order_count"],
32 "gross": row["gross_revenue"],
33 "net": row["net_revenue"],
34 }
35 for row in queryset
36 ]
01 / 01
STEP 01

Walkthrough

Space play ←→ step click any line
Three takeaways
  1. 1Chaining values() then annotate() turns the ORM into a SQL GROUP BY over the chosen column.
  2. 2Coalesce guards aggregates against NULL so empty sums return a usable zero instead of None.
  3. 3Pushing arithmetic into F expressions keeps computation in the database rather than in Python.

Related explainers

python 
from fastapi import APIRouter, BackgroundTasks, Depends, HTTPException, status
from pydantic import BaseModel, EmailStr
from sqlalchemy.orm import Session
 

Building a signup endpoint in FastAPI

dependency-injection request-validation background-tasks
Intermediate 8 steps
python 
from django import forms
from django.utils import timezone
 
from .models import Reservation

Multi-field validation in a Django ModelForm

form validation cross-field validation modelform
Intermediate 7 steps
python 
from django import template
from django.urls import reverse, NoReverseMatch
from django.utils.html import format_html
 

Active nav-link template tags in Django

template tags url routing active state
Intermediate 7 steps
python 
from functools import wraps
import asyncio
 
from fastapi import APIRouter, FastAPI, Request

Per-route request timeouts in FastAPI

decorators async timeouts
Intermediate 6 steps
python 
import os
from pathlib import Path
 
BASE_DIR = Path(__file__).resolve().parent.parent.parent

How a Django settings module is wired

configuration environment-variables middleware
Intermediate 8 steps
php 
<?php
 
namespace App\Services\Reports;
 

Streaming a monthly revenue CSV in Laravel

csv-export lazy-collections eloquent-query
Intermediate 8 steps

Share this explainer

Here's the card — post it anywhere.

Build a monthly revenue report in Django — share card
Made with highlit — turn any snippet into a walkthrough like this in about a minute.
Explain your code