SQL Query Writer for Business Reports

Monthly Revenue by Product Tier — PostgreSQL

SELECT

DATE_TRUNC('month', o.created_at) AS month,

p.tier,

COUNT(DISTINCT o.customer_id) AS paying_customers,

SUM(o.amount_cents) / 100.0 AS revenue_usd,

ROUND(AVG(o.amount_cents) / 100.0, 2) AS avg_order_value

FROM orders o

JOIN products p ON p.id = o.product_id

WHERE

o.status = 'completed'

AND o.created_at >= DATE_TRUNC('year', CURRENT_DATE) -- YTD

AND o.test_mode = false -- exclude test accounts

GROUP BY 1, 2

ORDER BY 1 DESC, revenue_usd DESC;

**Interpretation:** Each row = one month + product tier combination. Compare `revenue_usd` across tiers to spot which tier drives growth.

Index recommendation:

CREATE INDEX idx_orders_created_status ON orders(created_at, status) WHERE test_mode = false;

MoM Comparison variation:

-- Add LAG() window function to compare with prior month

LAG(revenue_usd) OVER (PARTITION BY tier ORDER BY month) AS prev_month_revenue