Using Advanced SQL to Generate Business Insights

The time-series analysis in the previous article showed that recent sales growth looks different depending on the comparison period and customer mix. This article asks a different set of questions about the same business.

How concentrated is sales value? Does an 80/20 pattern actually exist? Can customer accounts be compared fairly when stores and individuals behave so differently? Do acquisition cohorts reveal any change in early repeat purchasing?

These questions need more than a straightforward GROUP BY. I use ranking, cumulative window functions, NTILE, and cohort-style analysis, but the SQL technique remains secondary to the business interpretation.

This article is part of a twelve-part SQL sales analytics project based on AdventureWorks2025, supported by the sql-sales-analytics-portfolio repository.

SQL Sales Analytics Project Series

1. Building a Sales Analytics Solution with SQL: Project Overview
2. Understanding the Sales Data Landscape with SQL
3. Assessing Sales Data Quality with SQL
4. Preparing Sales Data for Analysis with SQL
5. Analysing Customer Purchasing Behaviour with SQL
6. Measuring Product Performance with SQL
7. Analysing Regional Sales Performance with SQL
8. Time-Series Analysis with SQL
9. Using Advanced SQL to Generate Business Insights ← You are here
10. Optimising SQL for Analytical Workloads
11. Designing an Analytics-Friendly Data Model
12. Completing a SQL Sales Analytics Project

Reproduce the Analysis

This article depends on the analytical views created in Article 4. If the Analytics views aren’t installed, run the analytical view creation script against AdventureWorks2025 first.

Then run the complete advanced SQL business insights script in SQL Server Management Studio. The analysis is read-only, uses session-scoped temporary tables, and includes every customer ranking, product ranking, value-quartile, and cohort query referenced below.

The Business Problem

Earlier articles identified high-value customer and product groups. Those aggregate results don’t show how quickly value accumulates across the ranked population or whether a chosen segment represents a genuinely unusual result.

I want to answer four related questions:

I use 30 April 2025 as the common cutoff because store orders end on that date, two months before individual orders. The baseline query returns 28,342 orders, 17,437 ordering customer accounts, and $107.89 million in net sales through the cutoff.

As elsewhere in the series, net sales value means the order subtotal or the corresponding sum of line values. It excludes tax and freight, but it isn’t profit.

Measuring Customer Concentration

I first create one row per ordering customer account. This preserves customer type, first and last order dates, order count, and lifetime net sales through the common cutoff. The complete customer-value dataset query establishes the grain used by the customer analysis.

The Pareto calculation then ranks accounts by net sales value and adds a cumulative total.

SUM(NetSalesValue) OVER
(
    ORDER BY NetSalesValue DESC, CustomerID
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS CumulativeNetSalesValue

The explicit ROWS frame is worth keeping. It tells SQL Server to accumulate physical rows in rank order. CustomerID provides a deterministic tie-breaker when two accounts have the same value.

The customer Pareto query finds the first ranked row where cumulative value reaches at least 80%.

It takes 1,368 customer accounts, or 7.85% of ordering accounts, to generate 80% of net sales value.

That is substantially more concentrated than a literal 80/20 pattern. It also reflects customer mix. The ten highest-value accounts are all stores and together contribute 7.34% of net sales. The top-customer ranking query keeps account type and order count beside the cumulative contribution.

This doesn’t mean the remaining accounts are unimportant. Individual accounts have lower order values by design, while store accounts represent a different commercial channel. A company-wide Pareto curve describes financial concentration, not customer quality or future potential.

A Pareto threshold isn’t automatically a customer segment

The 80% cutoff is a useful way to describe concentration, but it isn’t evidence that accounts immediately above and below the boundary need different treatment. Segmentation should also consider customer type, recency, frequency, margin, service cost, and commercial potential.

Comparing Value Quartiles Fairly

A single ranking places almost every store above almost every individual because store orders are much larger. That result is accurate, but it isn’t very useful for comparing similar accounts.

I calculate quartiles separately within each customer type.

NTILE(4) OVER
(
    PARTITION BY CustomerType
    ORDER BY NetSalesValue DESC, CustomerID
) AS ValueQuartile

NTILE(4) assigns approximately equal numbers of accounts to four ranked groups. Quartile 1 contains the highest-value quarter in each customer population.

The customer-quartile query generates the account counts, ranges, average values, totals, and percentage contributions.

The top quartile contributes almost three quarters of value for both customer types. The monetary boundaries are completely different, though. The lowest store value in quartile 1 is more than thirteen times the highest individual account value.

This is why a label such as “high value” needs a reference population. A high-value individual and a high-value store can both be commercially meaningful without being comparable in absolute spend.

NTILE also has a limitation. It creates groups with similar row counts, not groups separated by statistically fixed percentile values. Boundary values can move as accounts enter the dataset, and ties may fall into adjacent groups. For recurring segmentation, I would store the definition, effective date, and population alongside the result.

Comparing Customer and Product Concentration

The same cumulative method can be applied at product grain. I create one row per sold product through the common cutoff, retaining quantity, order reach, customer reach, category, and net sales value. The product-value dataset query supplies the reusable input.

The product Pareto query shows that 64 of 266 sold products, or 24.06%, generate 80.32% of product sales value.

These checkpoints come from the product-contribution query.

Product sales are still concentrated, but less sharply than customer sales. Reaching 80% requires 24.06% of sold products compared with 7.85% of ordering customer accounts.

The comparison is descriptive rather than causal. Customer and product populations have different sizes and business meanings. It does suggest different operational risks:

• Customer concentration: Losing a small number of large store accounts could materially affect sales value.
• Product concentration: Inventory and availability decisions across the leading product set matter, but exposure isn’t confined to only a handful of SKUs.

A stronger risk assessment would add product margin, substitution, stock availability, contract terms, and account-level dependency by product family.

Building Acquisition Cohorts

Lifetime repeat rates favour older accounts because they have had longer to place another order. I use a fixed follow-up window instead.

For each customer account, ROW_NUMBER identifies the first and second order. The analysis then asks whether the second order occurred within 90 or 180 days of the first.

ROW_NUMBER() OVER
(
    PARTITION BY CustomerID
    ORDER BY OrderDate, SalesOrderID
) AS OrderSequence

Only complete acquisition months with a full 180-day follow-up window are included. With a common analysis date of 30 April 2025, the final included acquisition month is October 2024.

The complete monthly cohort query keeps acquired-account counts and both repeat windows visible. The yearly cohort summary produces this compact comparison:

Store behaviour is consistent at the yearly level. Around 87% to 90% of eligible store accounts place a second order within 180 days, while relatively few do so within 90 days. That pattern agrees with the roughly six-month store purchase interval found in the customer analysis.

The individual result needs much more caution. Early repeat purchasing is almost absent for the 2022 and 2023 cohorts, then appears in 2024. The monthly results show the shift beginning in December 2023 and becoming more visible from March 2024.

I wouldn’t interpret that as a successful retention programme without external evidence. AdventureWorks is a sample database, and the pattern may reflect how its data was generated. In a real engagement, this result would trigger checks for changes in account creation, identity matching, order imports, channel definitions, promotions, or customer policy.

Cohorts make exposure time explicit

Comparing customers over the same 90-day or 180-day window removes one common source of bias. It doesn’t explain a change in behaviour, but it makes the change easier to locate and investigate.

Business Recommendations

Monitor dependency on high-value store accounts

The customer Pareto result shows material concentration in a small population dominated by stores. I would track account health, salesperson ownership, product dependency, and recent direction for those accounts rather than relying on a static top-customer list.

Define value segments within customer type

Store and individual accounts operate at very different monetary scales. Separate thresholds will produce more useful treatment groups and prevent the individual population from being labelled low value simply because its channel has smaller orders.

Protect the leading product set without ignoring the long tail

The first 64 sold products contribute just over 80% of value. Availability and supplier risk deserve close attention across that set. The remaining catalogue should be assessed for margin, strategic range, attachment sales, substitution, and lifecycle status before any rationalisation decision.

Investigate the individual-cohort discontinuity

The change in early repeat behaviour is large enough to validate against source-system history. Until its cause is understood, I wouldn’t use it as evidence of improving retention or as the basis for a forecast.

Turn thresholds into monitored definitions

Production reporting should record the analysis cutoff, population, grain, ranking measure, tie-breaker, and segment boundary. That makes a result reproducible and explains why an account or product moves between groups.

Limitations

• Net sales isn’t profit: The rankings don’t include product cost, servicing cost, returns, or customer margin.
• Account grain isn’t necessarily person grain: AdventureWorks customer accounts shouldn’t be treated as verified unique human beings or businesses.
• Customer type and channel overlap: Every individual order is online and every store order is salesperson-assisted in this sample.
• Pareto boundaries are descriptive: An 80% threshold doesn’t establish a natural commercial breakpoint.
• Quartiles are relative: Their monetary boundaries change with the population and analysis date.
• Cohorts show association: They don’t explain why repeat behaviour differs.
• The sample has structural artefacts: The individual-cohort discontinuity may reflect data generation rather than a real commercial intervention.

Advanced SQL makes it possible to ask more precise questions, but it doesn’t make the underlying data more complete or the conclusions causal. The strongest outcome here is a clearer set of priorities and follow-up questions.

Next in the Series

The next article will examine how efficiently these analytical queries run.

I will use execution plans, indexing, and measured query comparisons to improve performance without changing the business results.