Product Geographic Distribution
As we saw in Chapter 4, geography is a key dimension for analysis. This is true at the product level as well. This section investigates some interactions between geography and products.
Most Common Product by State
One common question is what “thing” is most frequently associated with something else, such as the most common product in each state. The easiest approach to this type of question is to use window functions, particularly ROW_NUMBER():
SELECT sp.State, sp.ProductId, cnt, p.GroupName
FROM (SELECT o.State, ol.ProductId, COUNT(*) as cnt,
ROW_NUMBER() OVER (PARTITION BY o.State
ORDER BY COUNT(*) DESC,
ol.ProductId) as seqnum
FROM Orders o JOIN
OrderLines ol
ON o.OrderId = ol.OrderId
GROUP BY o.State, ol.ProductId
) sp JOIN
Products p
ON sp.ProductId = p.ProductId
WHERE seqnum = 1;
The COUNT(*) in the...