KaijuSF
KaijuSF

Reputation: 1

Amazon Redshift multi-aggregate rollup

I have a REDSHIFT dataset which includes customer purchases as a SKU level.

I'm struggling to produce a view that includes multiple aggregates at a customer level. For example my table includes columns like: customer_id, order_id, product_id, product_category, product_division, sales, units

From this base I'd like a result which looks like

customer_id
total_sales (i.e. sum of all SKU sales)
total_units
total_orders
categories_purchased (i.e. a distinct count of categories the customer purchased)
divisions_purchased
primary_category_sales (i.e. category with the highest sales)
primary_division_sales
primary_category_mix (i.e. primary category sales / total sales)
primary_division_mix

While I can aggregate results for the whole dataset, I can't seem to crack how to incorporate sub-aggregates like finding the maximum category and its relative contribution to total sales. Any help you can offer is most appreciated!

I have tried nesting queries + using window functions but keep running into errors like aggregate function calls may not have nested aggregate or window functions.

Upvotes: 0

Views: 179

Answers (0)

Related Questions