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"):
  queryset = (
      Order.objects.filter(
          placed_at__year=year,
          status=status,
      )
      .annotate(month=TruncMonth("placed_at"))
      .values("month")
      .annotate(
          gross_revenue=Coalesce(
              Sum(F("line_items__unit_price") * F("line_items__quantity")),
              0,
              output_field=DecimalField(max_digits=12, decimal_places=2),
          ),
          refunds=Coalesce(Sum("refunds__amount"), 0, output_field=DecimalField()),
          order_count=Count("id", distinct=True),
      )
      .annotate(net_revenue=F("gross_revenue") - F("refunds"))
      .order_by("month")
  )
27 
  return [
      {
          "month": row["month"].strftime("%Y-%m"),
          "orders": row["order_count"],
          "gross": row["gross_revenue"],
          "net": row["net_revenue"],
      }
      for row in queryset
  ]

01 / 01

STEP 01

Walkthrough

Space play ←→ step click any line

Three takeaways

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

Related explainers

python

import argparse
import sys
from pathlib import Path

Building a subcommand CLI with argparse

cli argparse subcommands

Intermediate 6 steps

python

from collections.abc import Mapping
from typing import Any, Iterator

Flattening nested config into dotted keys

recursion generators tree-traversal

Intermediate 7 steps

python

import csv
import io
from datetime import datetime

Streaming a CSV export in Flask

streaming generators csv

Intermediate 9 steps

python

import time
from collections import defaultdict
from threading import Lock

Sliding-window login rate limiting in Flask

rate-limiting sliding-window thread-safety

Intermediate 7 steps

python

from django.conf import settings
from django.contrib.auth import get_user_model
from django.core.mail import EmailMultiAlternatives
from django.db.models.signals import post_save

Sending a welcome email with Django signals

signals email user-activation

Intermediate 8 steps

java

@Target({ElementType.FIELD, ElementType.PARAMETER})
@Retention(RetentionPolicy.RUNTIME)
@Constraint(validatedBy = StrongPasswordValidator.class)
@Documented