01 / PostgreSQL
Bucket paid revenue with DATE_TRUNC
Show paid revenue by calendar month in the stored UTC timestamps, oldest month first.
SELECT DATE_TRUNC('month', ordered_at)::date AS month,
SUM(total) AS revenue
FROM orders
WHERE status = 'paid'
GROUP BY 1
ORDER BY 1;
DATE_TRUNC buckets timestamps into months and the date cast gives a readable month-start value. This returns months containing paid orders. For a chart that must show zero-activity months, join the aggregate to a calendar table or generate_series.
02 / PostgreSQL
Choose one latest order with DISTINCT ON
Show each purchasing customer's latest paid order ID and total. Resolve equal timestamps by the largest order ID. Sort by customer ID.
SELECT DISTINCT ON (customer_id) customer_id, id, total
FROM orders
WHERE status = 'paid'
ORDER BY customer_id, ordered_at DESC, id DESC;
PostgreSQL DISTINCT ON keeps the first row of each customer group in the specified order. The ID tie-breaker makes the answer stable when two orders share a timestamp. MAX(ordered_at) alone would not identify a unique order total.
03 / PostgreSQL
Count each status with FILTER
For every customer with an order, show paid and cancelled order counts, ordered by customer ID.
SELECT customer_id,
COUNT(*) FILTER (WHERE status = 'paid') AS paid_orders,
COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled_orders
FROM orders
GROUP BY customer_id
ORDER BY customer_id;
FILTER gives each aggregate its own condition without discarding other statuses from the input. Customers without any order are absent because orders is the starting table. If you turn these counts into a percentage, define the denominator and guard division by zero.