校招算法笔面试 | SQL笔试面试编程题-统计每个产品的销售情况

题目

题目链接

最近做了阿里的24年春招题，题目如下：

题目分析

在这道题目中，我们需要处理三个表，表中会用到的信息包括：

• orders（订单表）

  • order_id：订单编号
  • product_id：产品编号
  • customer_id：客户编号
  • quantity：购买数量

• products（产品表）

  • product_id：产品编号
  • unit_price：单价

• customers（客户表）

  • customer_id：客户编号
  • customer_age：客户年龄

目标：

• 计算每个产品2023年的总销售额、平均月销售额、单月最高销量、最大月销售量，并找出每个产品的最大购买量的客户的年龄段。

输出要求：

• 按照每个产品的总销售额降序排列。
• 如果总销售额一致，则按照产品的ID升序排列。
• 当存在两个客户年购买量都是最高时，customer_age_group展示年龄小的顾客的年龄段。

知识点关键词：窗口函数、分组、连接、条件判断

解答步骤

步骤1：计算每个产品的年度销售数据

我们首先需要计算每个产品的总销售额、总购买量、平均月销售额和最大月购买量。

• 窗口函数：使用 sum() 和 max() 窗口函数来计算每个产品的总购买量和最大月购买量。

--每个产品的总购买量
 sum(o.quantity) over(partition by p.product_id) as total_quantity

--最大月购买量
max(quantity) over(partition by product_id) as max_monthly_quantity

• 分区：通过 partition by 按产品编号分区，确保计算是在每个产品的范围内进行。

partition by product_id

• 计算总销售额：通过 sum(o.quantity) * unit_price 计算。

sum(o.quantity) over(partition by p.product_id) * unit_price as total_sales

• 计算平均月销售额：将总销售额除以 12，并使用 round() 函数保留两位小数。

round(sum(o.quantity) over(partition by p.product_id) * unit_price / 12.0, 2) as avg_monthly_sales

步骤一全部代码：

with year_sales as (
    select 
    p.product_id,
    p.unit_price,
    sum(o.quantity) over(partition by p.product_id) * unit_price as total_sales,
    sum(o.quantity) over(partition by p.product_id) as total_quantity,
    round(sum(o.quantity) over(partition by p.product_id) * unit_price / 12.0</