Reputation: 1
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