题解 | 统计每个产品的销售情况
统计每个产品的销售情况
https://www.nowcoder.com/practice/d431aa7bf72c4fd7b048ec639bc83ad2
with base_data as ( select products.product_id, month(order_date) as month, unit_price, quantity, quantity * unit_price as total_amount, customer_age, case when customer_age between 1 and 10 then '1-10' when customer_age between 11 and 20 then '11-20' when customer_age between 21 and 30 then '21-30' when customer_age between 31 and 40 then '31-40' when customer_age between 41 and 50 then '41-50' when customer_age between 51 and 60 then '51-60' ELSE '61+' end as customer_age_group from orders join customers using (customer_id) join products using (product_id) where year(order_date) = 2023 ), max_monthly_quantity_data as ( select distinct product_id, max_monthly_quantity from ( select product_id, max(sum(quantity)) over ( partition by product_id ) as max_monthly_quantity from base_data group by product_id, month order by product_id asc, month asc ) t1 ), grouped_data AS ( select product_id, customer_age_group from ( SELECT product_id, customer_age_group, total_quantity, ROW_NUMBER() OVER ( PARTITION BY product_id ORDER BY total_quantity DESC, customer_age_group asc ) AS sales_rank FROM ( SELECT product_id, customer_age_group, SUM(quantity) AS total_quantity FROM base_data GROUP BY product_id, customer_age_group ) t2 ORDER BY product_id, sales_rank ) t3 where sales_rank = 1 ), basic_stat as ( select product_id, round(sum(total_amount), 2) as total_sales, round(unit_price, 2) as unit_price, sum(quantity) as total_quantity, round(sum(total_amount) / 12, 2) as avg_monthly_sales from base_data group by product_id, unit_price ) select * from basic_stat join max_monthly_quantity_data using (product_id) join grouped_data using (product_id) order by total_sales desc, product_id asc, customer_age_group asc