Ten questions. Ten SQL patterns.

List paid orders placed in January 2026, with their IDs and totals, ordered by ID.

SELECT id, total
FROM orders
WHERE status = 'paid'
  AND ordered_at >= TIMESTAMP '2026-01-01 00:00:00'
  AND ordered_at < TIMESTAMP '2026-02-01 00:00:00'
ORDER BY id;

The inclusive start and exclusive end include every January timestamp and exclude midnight on February 1. BETWEEN with a January 31 date would miss most of the last day. The status predicate defines which orders count; no date function is applied to the filtered column.

One row per paid January order; later months and cancelled orders are excluded.

Show total paid order revenue by customer country, highest revenue first; break ties by country.

SELECT c.country, SUM(o.total) AS revenue
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.id
WHERE o.status = 'paid'
GROUP BY c.country
ORDER BY revenue DESC, c.country;

The join follows the foreign key from orders to customers. Each order contributes once because customers.id is unique. Adding an order-items join here would multiply order totals unless items were aggregated first. Countries with no paid orders are absent.

For every customer, count paid orders, including customers with zero. Sort by customer ID.

SELECT c.id, c.name, COUNT(o.id) AS paid_orders
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.id AND o.status = 'paid'
GROUP BY c.id, c.name
ORDER BY c.id;

The paid filter belongs in ON so the left join preserves non-buyers. COUNT(o.id) counts matching orders; COUNT(*) would count the placeholder row and incorrectly report one for a customer with no paid orders.

Find customers with at least two paid orders across all time. Return customer ID and paid order count, ordered by customer ID.

SELECT customer_id, COUNT(*) AS paid_orders
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING COUNT(*) >= 2
ORDER BY customer_id;

WHERE removes unpaid rows before counting; HAVING removes customer groups after counting. State the reporting period when asking for repeat customers: this example deliberately uses all recorded history.

List customers who have never placed any order, including cancelled orders when checking existence. Sort by ID.

SELECT c.id, c.name
FROM customers AS c
WHERE NOT EXISTS (
  SELECT 1 FROM orders AS o WHERE o.customer_id = c.id
)
ORDER BY c.id;

NOT EXISTS asks whether any related row exists. It avoids the NULL trap of NOT IN and differs from 'no paid orders': a customer with only a cancelled order does not qualify.

Count distinct customers with a paid order in each calendar month of the stored UTC timestamps. Sort by month.

SELECT DATE_TRUNC('month', ordered_at)::date AS month,
       COUNT(DISTINCT customer_id) AS purchasing_customers
FROM orders
WHERE status = 'paid'
GROUP BY 1
ORDER BY 1;

COUNT(DISTINCT customer_id) counts a customer once within each month even when that customer places several paid orders. COUNT(*) would count orders instead. Monthly distinct counts cannot be summed to obtain all-time distinct customers: a repeat buyer can appear in several months. NULL customer IDs are excluded by COUNT(DISTINCT), although the teaching relationship expects every order to have a customer.

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.

Show cumulative paid revenue by month, starting with the first recorded month.

WITH monthly AS (
  SELECT DATE_TRUNC('month', ordered_at)::date AS month,
         SUM(total) AS revenue
  FROM orders
  WHERE status = 'paid'
  GROUP BY 1
)
SELECT month,
       SUM(revenue) OVER (
         ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS cumulative_revenue
FROM monthly
ORDER BY month;

The CTE creates one row per month before the window runs. The explicit ROWS frame adds every prior month to the current month. Filtering to a year inside the CTE would reset the accumulation to that year; filtering outside would retain earlier history.

List paid orders whose total exceeds the average paid order total across all time. Sort by order ID.

SELECT id, total
FROM orders
WHERE status = 'paid'
  AND total > (SELECT AVG(total) FROM orders WHERE status = 'paid')
ORDER BY id;

Both the outer query and the average use paid orders, so a cancelled high-value order cannot distort the benchmark. This is an order-weighted average, not an average of customer averages. AVG ignores NULL totals; define a NULL policy if your real schema allows them.

Compare paid revenue in January and February 2026 using stored UTC timestamps. Show both totals, February minus January, and percentage change rounded to two decimals. Treat a month with no paid orders as zero; return NULL percentage change when January revenue is zero.

WITH periods AS (
  SELECT
    COALESCE(SUM(total) FILTER (
      WHERE ordered_at < TIMESTAMP '2026-02-01'
    ), 0) AS january_revenue,
    COALESCE(SUM(total) FILTER (
      WHERE ordered_at >= TIMESTAMP '2026-02-01'
    ), 0) AS february_revenue
  FROM orders
  WHERE status = 'paid'
    AND ordered_at >= TIMESTAMP '2026-01-01'
    AND ordered_at < TIMESTAMP '2026-03-01'
)
SELECT january_revenue, february_revenue,
       february_revenue - january_revenue AS revenue_change,
       ROUND(100.0 * (february_revenue - january_revenue)
         / NULLIF(january_revenue, 0), 2) AS percent_change
FROM periods;

The half-open range includes both complete months and excludes March 1. Conditional SUM separates January and February without dropping the output row when either period is absent; COALESCE applies the explicit no-orders-means-zero policy. NULLIF makes growth from a zero January base undefined (NULL), rather than a division error or a fabricated percentage. The sample totals are in one reporting currency and are assumed known: missing amounts or an incomplete data load require investigation, not automatic replacement with zero. Compare complete periods with the same recognition rules and currency; month totals also reflect the different numbers of days, so request daily averages if that is the intended metric.