Quick summary
Summarize this blog with AI
A useful MySQL analysis starts with a precise question: how much paid order revenue did each customer generate in January, including customers with no paid orders? This walkthrough targets MySQL 8.4 and combines a reproducible fixture, date boundaries, aggregation, a left join, and ranking. It requires no existing tables and makes no changes to stored data.
The MySQL syntax is checked against the official 8.4 documentation. The expected results are reconciled from the five input orders; equivalent relational logic was executed in PostgreSQL after changing the timestamp casts. The MySQL query itself was not executed on a MySQL server during this revision.
1. Define the reporting contract
The unit is one customer. Revenue is the sum of paid order amounts, in a single reporting currency, for January 2026. A zero-value paid order still counts as an order. Cancelled orders do not count. The customer table defines the population, so Cara must appear even though she has no orders. This fixture represents a complete order feed; in a partial feed, an absent order is not evidence of zero activity.
The fixture uses DATETIME(6), which stores six fractional-second digits without a time-zone conversion. Treat its values as UTC by an explicit data contract. If your application uses TIMESTAMP, MySQL converts between UTC storage and the session time zone; confirm the session setting and reporting time zone before reusing the boundaries. A named local business month may require different UTC endpoints.
2. Run the complete query
Copy the entire statement. Each common table expression exists only for this statement. The first rows give the fixture columns their names and deliberate numeric/date types. The query groups orders before joining customers, so the join cannot multiply the revenue.
WITH customers AS (
SELECT 1 AS customer_id, 'Ada' AS customer_name
UNION ALL SELECT 2, 'Ben'
UNION ALL SELECT 3, 'Cara'
), orders AS (
SELECT 1 AS order_id, 1 AS customer_id,
CAST('2026-01-01 00:00:00' AS DATETIME(6)) AS ordered_at,
'paid' AS status, CAST(10 AS DECIMAL(12,2)) AS amount
UNION ALL SELECT 2, 1, CAST('2026-01-31 23:59:59.999999' AS DATETIME(6)), 'paid', 20
UNION ALL SELECT 3, 2, CAST('2026-02-01 00:00:00' AS DATETIME(6)), 'paid', 50
UNION ALL SELECT 4, 2, CAST('2026-01-15 00:00:00' AS DATETIME(6)), 'cancelled', 999
UNION ALL SELECT 5, 2, CAST('2026-01-20 00:00:00' AS DATETIME(6)), 'paid', 0
), january_paid AS (
SELECT * FROM orders
WHERE status = 'paid'
AND ordered_at >= CAST('2026-01-01 00:00:00' AS DATETIME(6))
AND ordered_at < CAST('2026-02-01 00:00:00' AS DATETIME(6))
), totals AS (
SELECT customer_id, COUNT(*) AS paid_orders, SUM(amount) AS revenue
FROM january_paid GROUP BY customer_id
), report AS (
SELECT c.customer_id, c.customer_name,
COALESCE(t.paid_orders, 0) AS paid_orders,
COALESCE(t.revenue, 0) AS revenue,
ROUND(t.revenue / NULLIF(t.paid_orders, 0), 2) AS average_order_value
FROM customers AS c
LEFT JOIN totals AS t ON t.customer_id = c.customer_id
)
SELECT customer_id, customer_name, paid_orders, revenue, average_order_value,
DENSE_RANK() OVER (ORDER BY revenue DESC) AS revenue_rank
FROM report ORDER BY customer_id;Expected values (your client may display a different number of trailing zeros):
customer_id | customer_name | paid_orders | revenue | average_order_value | revenue_rank
1 | Ada | 2 | 30.00 | 15.00 | 1
2 | Ben | 1 | 0.00 | 0.00 | 2
3 | Cara | 0 | 0.00 | NULL | 2Ada’s two orders total 30. Ben’s zero-value order is a real order, so his average order value is zero. Cara has no orders, so her average is undefined. Replacing that NULL with zero would erase the difference between no observations and observed zero revenue. Rank ties are intentional: Ben and Cara have the same revenue. The final ORDER BY customer_id controls presentation independently of the ranking.
3. Validate the month boundary and denominator
The half-open range includes the first instant of January and excludes the first instant of February. Order 2 at 23:59:59.999999 on January 31 belongs in the report. A predicate ending at 2026-01-31 00:00:00 would omit almost all of that last day. Avoid inventing a “last possible second”; its precision can differ from the column.
To inspect the selected orders, reuse the same WITH customers ... january_paid AS (...) prefix, omit the later totals and report CTEs, and end with this SELECT:
SELECT order_id, customer_id, amount FROM january_paid ORDER BY order_id;order_id | customer_id | amount
1 | 1 | 10.00
2 | 1 | 20.00
5 | 2 | 0.00The report must reconcile to three paid orders and total revenue 30. The full five-row source also contains a February order worth 50 and a cancelled January order worth 999; neither belongs in this denominator. Confirm distinct order identifiers in a real feed. If rows represent line items or successive order versions, first aggregate lines or choose an authoritative version before counting orders.
4. Adapt it to real tables
Replace only the customers and orders fixture CTEs with your table references, keeping the reporting stages visible. Confirm column types, currency, status semantics, duplicate policy, and missing-value policy. The sample assumes every paid amount is known; SUM ignores NULL amounts, so unknown amounts need an explicit completeness check instead of silently being treated as zero.
Pass date bounds as parameters through your database driver. Do not concatenate user-supplied dates or customer values into SQL text. Check the query plan on representative data with MySQL EXPLAIN before proposing an index. An index involving status, timestamp, and customer may help some workloads, but column order and selectivity must match actual filters and table sizes. A CTE is a useful way to express stages; it does not guarantee that MySQL materializes or optimizes them in a particular way.
Use a read-only reporting account and an appropriate connection timeout in an application. This tutorial does not need administrative privileges, table changes, stored routines, or a client DELIMITER command. Those features solve different operational tasks and are unnecessary for this report.
References and next practice
Primary references: MySQL 8.4 common table expressions, DATETIME and TIMESTAMP, window functions, EXPLAIN, and derived table and CTE optimization. Extend the fixture with a customer who has two orders at the same time, an unknown paid amount, and a second currency; define the reporting rules before changing the query.
