Advanced SQL Customer Analysis: Versions, Joins, and Zero Orders

Build a customer-level analysis that retains non-buyers

An advanced SQL answer should still have a simple contract: one row per customer, January paid-order totals, and each customer’s share of those known totals. This PostgreSQL 16 case combines deterministic version selection, a fixed time window, independent aggregation, a preserving join, and window calculations. It includes corrected order versions, an end-date boundary, a refunded order, and a customer with a missing segment.

Set the population and version policy

The customers table defines the report population. Every listed customer must appear, even with no qualifying order. In the fictional order feed, the largest unique ingest_id is explicitly authoritative for an order_id; that is a source contract, not a property every ingestion timestamp has. Select that current version before testing its status and date, or an obsolete paid version could survive a later correction.

The reporting interval is January 1 inclusive through February 1 exclusive, with timestamps stored under a UTC convention. January 31 at 23:59:59 is included; February 1 at midnight is not. A real instant column and local business-month boundary require the corresponding timezone policy. The fixture’s source is complete, so no matching paid order means zero orders and zero revenue.

Run the complete query

WITH customers(customer_id,segment) AS (VALUES (1,'A'),(2,NULL),(3,'A')),
raw_orders(ingest_id,order_id,customer_id,order_ts,status,amount) AS (
 VALUES (1,10,1,TIMESTAMP '2026-01-01 00:00','paid',100::numeric),
        (2,10,1,TIMESTAMP '2026-01-01 00:00','paid',120),
        (3,11,1,TIMESTAMP '2026-01-31 23:59:59','paid',50),
        (4,12,2,TIMESTAMP '2026-02-01 00:00','paid',40),
        (5,13,3,TIMESTAMP '2026-01-20 12:00','refunded',30)
), versions AS (
 SELECT *,ROW_NUMBER() OVER(PARTITION BY order_id ORDER BY ingest_id DESC) AS rn
 FROM raw_orders
), monthly AS (
 SELECT customer_id,COUNT(*) AS orders,SUM(amount) AS revenue
 FROM versions WHERE rn=1 AND status='paid'
  AND order_ts>=TIMESTAMP '2026-01-01 00:00'
  AND order_ts<TIMESTAMP '2026-02-01 00:00'
 GROUP BY customer_id
), customer_metrics AS (
 SELECT c.customer_id,c.segment,COALESCE(m.orders,0) AS orders,
 COALESCE(m.revenue,0) AS revenue
 FROM customers c LEFT JOIN monthly m USING(customer_id)
)
SELECT customer_id,segment,orders,revenue,
 DENSE_RANK() OVER(ORDER BY revenue DESC) AS revenue_rank,
 ROUND(100*revenue/NULLIF(SUM(revenue) OVER(),0),2) AS revenue_share_pct
FROM customer_metrics ORDER BY customer_id;

customer_id | segment | orders | revenue | revenue_rank | revenue_share_pct
1 | A | 2 | 170 | 1 | 100.00
2 | NULL | 0 | 0 | 2 | 0.00
3 | A | 0 | 0 | 2 | 0.00

Order 10 contributes 120 from its latest version, not 100 + 120. Order 11 contributes 50 on the final day. Customer 2’s order begins the next month, and customer 3’s only order is refunded. Both customers remain in the result. The two zero-revenue customers share dense rank 2. Customer 2’s segment remains NULL rather than being silently assigned to segment A.

Keep right-side qualification out of the final WHERE

If a raw LEFT JOIN is followed by WHERE orders.order_ts >= the start date, the NULL-extended row for a customer without an order fails that comparison. The report loses its zero-match customers. Filtering and aggregating orders in monthly first makes the intended relationship explicit: each customer matches at most one summary row. Putting the time predicates in a raw join’s ON clause can also retain unmatched customers, but additional fact joins can still inflate measures.

COUNT(*) over a raw left join counts its null-extended row. It is not automatically an order count. Here COUNT(*) operates only on qualifying order rows before the left join, and the absence of a summary is converted to zero. The PostgreSQL join reference explains the distinction between join conditions and subsequent filtering.

Reconcile before interpreting the ranking

Five feed rows become four current orders. Two paid January orders contribute 170. The output has exactly three customers and the output revenue sum is 170. These checks reveal double-counting even if the final result happens to have one row per customer. Do not call the period total lifetime value: it covers only this January under the stated refund and source-completeness policies.

Revenue share uses the whole report population. If you later filter out zero-revenue customers before computing the window, the share denominator may or may not change, depending on the filter. If you filter by segment, it will become segment-only revenue unless you deliberately compute global totals first. When all report revenue is zero, NULLIF leaves the percentage undefined rather than inventing a percentage.

Inspect the work at the right stages

On large data, inspect the current-version selection, time filtering, aggregation, and final join separately. The latest-version requirement can prevent an early date filter from being correct if a correction changes the order date. A maintained current-order table may simplify the workload, but that is a data-model decision with its own consistency contract. Indexes should support the observed access pattern; creating an index does not instruct the planner to always use it.

Export a stable result, not a new interpretation

When loading this output into Python, R, or a dashboard, retain numeric types, customer identity, the reporting cutoff, and the meaning of NULL segment and zero revenue. Reconcile row counts and totals after export. Bind query parameters through the connector, and avoid embedding credentials in example connection strings or exported notebooks. A typed result contract is more useful than a generic SELECT * integration snippet.

Use authorized data and explain remaining uncertainty

Run production analytics with the appropriate read permissions and approved reporting source. A read-only query can still expose sensitive customer information, so select only required fields. The fictional IDs here have no personal data. Source freshness, identity resolution, currency, and partial refunds are unresolved requirements in a different business dataset; do not infer them from this teaching schema.

Prove the zero-match cases

The core result is not just customer 1’s 170; it is also customers 2 and 3 remaining present with zero qualifying activity. Check corrected versions, the exact time endpoint, null dimensions, and multiple matches before trusting an advanced aggregate. For more join-grain practice, see why joins multiply rows.

FAQ

Is zero always the right missing total? Only when absent facts mean no activity in a complete source. Why choose the latest version first? Its date or status can change eligibility. Does a window rank predict future value? No; it describes this report’s observed totals.