Revenue and customer integrity audit

1 · Keep raw and clean apart

I never edit the source tables. Raw data stays in public, and cleaned, analysis-ready tables go in a separate analytics schema. The original is always there to check against, and analysts query one tidy layer.

-- one source of truth for revenue: one row per order line
CREATE TABLE analytics.sales AS
SELECT ol.orderlineid, o.orderid, o.orderdate, o.customerid, ol.prod_id, ol.quantity,
       p.price AS unit_price,
       (ol.quantity * p.price) AS total_revenue
FROM public.orderlines ol
JOIN public.orders   o ON ol.orderid = o.orderid
JOIN public.products p ON ol.prod_id = p.prod_id;

2 · Audit before you analyze

Before reporting a single revenue number, I ran three checks. Orphaned sales (a sale whose customer doesn't exist) and negative revenue both came back clean. The third, an inventory reconciliation, did not.

WITH sales_summary AS (
    SELECT prod_id, SUM(quantity) AS total_units_sold
    FROM analytics.sales GROUP BY prod_id
)
SELECT p.prod_id, p.title,
       i.quan_in_stock AS current_inventory,
       s.total_units_sold,
       (i.quan_in_stock - s.total_units_sold) AS inventory_buffer
FROM analytics.products p
JOIN public.inventory i ON p.prod_id = i.prod_id
LEFT JOIN sales_summary s ON p.prod_id = s.prod_id
WHERE (i.quan_in_stock - s.total_units_sold) < 0
ORDER BY inventory_buffer;

These are high-velocity products where recorded sales exceed what was ever in stock. The sales system lets customers buy items that don't exist, which means unfillable orders, refunds and support tickets.

The fix I proposed to inventory and engineering

Together they remove the negative-inventory problem for these SKUs at the source and stop the refunds and support load it creates.

3 · Then answer the CEO's questions

With a trusted sales table, the business questions become straightforward SQL:

• Month-over-month revenue, using LAG() to compare each month with the one before, and NULLIF to avoid dividing by zero.
• The top 1% of customers by lifetime spend with PERCENT_RANK(), plus each one's favorite category via ROW_NUMBER() partitioned by customer.
• The five states with the highest average order value, counting distinct orders (not order lines) and ignoring states with 10 or fewer orders, so a single big purchase can't top the list.

-- top 5 states by average order value
SELECT c.state,
       ROUND(SUM(s.total_revenue)::numeric, 2)       AS total_state_revenue,
       COUNT(DISTINCT s.orderid)                     AS total_orders,
       ROUND((SUM(s.total_revenue) / COUNT(DISTINCT s.orderid))::numeric, 2) AS average_order_value
FROM analytics.sales s
JOIN analytics.customers c ON s.customerid = c.customerid
GROUP BY 1
HAVING COUNT(DISTINCT s.orderid) > 10
ORDER BY average_order_value DESC
LIMIT 5;