3230. 客户购买行为分析 🔒

题目描述

表：Transactions

+------------------+---------+
| Column Name      | Type    |
+------------------+---------+
| transaction_id   | int     |
| customer_id      | int     |
| product_id       | int     |
| transaction_date | date    |
| amount           | decimal |
+------------------+---------+
transaction_id 是这张表的唯一标识符。
这张表的每一行包含一次交易的信息，包括客户 ID，产品 ID，日期和总花费。

表：Products

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| product_id  | int     |
| category    | varchar |
| price       | decimal |
+-------------+---------+
product_id 是这张表的唯一标识符。
这张表的每一行包含一个产品的信息，包括它的分类和价格。

编写一个解决方案来分析用户购买行为。对于 每个消费者，计算：

总消费额
交易数量
购买的不同产品类别的数量。
平均消费金额。
最常购买 的产品类别（如果相同，选择最近交易的那个）
忠诚度分数 定义为：(交易数量 * 10) + (总消费 / 100)。

将 total_amount， avg_transaction_amount 和 loyalty_score 舍入到 2 位小数。

返回结果表以 loyalty_score 降序排序，然后以 customer_id 升序排序。

查询结果格式如下所示。

示例：

输入：

Transactions 表：

+----------------+-------------+------------+------------------+--------+
| transaction_id | customer_id | product_id | transaction_date | amount |
+----------------+-------------+------------+------------------+--------+
| 1              | 101         | 1          | 2023-01-01       | 100.00 |
| 2              | 101         | 2          | 2023-01-15       | 150.00 |
| 3              | 102         | 1          | 2023-01-01       | 100.00 |
| 4              | 102         | 3          | 2023-01-22       | 200.00 |
| 5              | 101         | 3          | 2023-02-10       | 200.00 |
+----------------+-------------+------------+------------------+--------+

Products 表：

+------------+----------+--------+
| product_id | category | price  |
+------------+----------+--------+
| 1          | A        | 100.00 |
| 2          | B        | 150.00 |
| 3          | C        | 200.00 |
+------------+----------+--------+

输出：

+-------------+--------------+-------------------+-------------------+------------------------+--------------+---------------+
| customer_id | total_amount | transaction_count | unique_categories | avg_transaction_amount | top_category | loyalty_score |
+-------------+--------------+-------------------+-------------------+------------------------+--------------+---------------+
| 101         | 450.00       | 3                 | 3                 | 150.00                 | C            | 34.50         |
| 102         | 300.00       | 2                 | 2                 | 150.00                 | C            | 23.00         |
+-------------+--------------+-------------------+-------------------+------------------------+--------------+---------------+

解释：

对于消费者 101：
- 总消费额：100.00 + 150.00 + 200.00 = 450.00
- 交易次数：3
- 不同分类：A, B, C （3 个分类）
- 平均交易金额：450.00 / 3 = 150.00
- 最高分类：C （消费者 101 在分类 A，B，C 分别进行了一次交易。因为所有分类的数量都一样，我们选择最近的那次交易，即在 2023-02-10 的分类 C）
- 忠诚度分数：(3 * 10) + (450.00 / 100) = 34.50
对于消费者 102：
- 总消费额：100.00 + 200.00 = 300.00
- 交易次数：2
- 不同分类：A, C（2 个分类）
- 平均交易金额：300.00 / 2 = 150.00
- 最高分类：C （消费者 102 在分类 A 和 C 分别进行了一次交易。因为所有分类的数量都一样，我们选择最近的那次交易，即在 2023-01-22 的分类 C）
- 忠诚度分数：(2 * 10) + (300.00 / 100) = 23.00

注意：输出表以 loyalty_score 降序排序，然后以 customer_id 升序排序。

解法

方法一：分组 + 窗口函数 + 连接

我们首先将 Transactions 表和 Products 表连接起来，记录在临时表 T 中。

然后，我们使用 T 表计算每个用户在每个类别下的交易次数以及最近的交易日期，将结果保存在临时表 P 中。

接着，我们使用 P 表计算每个用户在每个类别下的交易次数的排名，将结果保存在临时表 R 中。

最后，我们使用 T 表和 R 表计算每个用户的总交易金额、交易次数、唯一类别数、平均交易金额、最常购买的类别、忠诚度分数，并按照忠诚度分数降序、用户 ID 升序的顺序返回结果。

MySQL

# Write your MySQL query statement below
WITH
    T AS (
        SELECT *
        FROM
            Transactions
            JOIN Products USING (product_id)
    ),
    P AS (
        SELECT
            customer_id,
            category,
            COUNT(1) cnt,
            MAX(transaction_date) max_date
        FROM T
        GROUP BY 1, 2
    ),
    R AS (
        SELECT
            customer_id,
            category,
            RANK() OVER (
                PARTITION BY customer_id
                ORDER BY cnt DESC, max_date DESC
            ) rk
        FROM P
    )
SELECT
    t.customer_id,
    ROUND(SUM(amount), 2) total_amount,
    COUNT(1) transaction_count,
    COUNT(DISTINCT t.category) unique_categories,
    ROUND(AVG(amount), 2) avg_transaction_amount,
    r.category top_category,
    ROUND(COUNT(1) * 10 + SUM(amount) / 100, 2) loyalty_score
FROM
    T t
    JOIN R r ON t.customer_id = r.customer_id AND r.rk = 1
GROUP BY 1
ORDER BY 7 DESC, 1;

3230. 客户购买行为分析 🔒

题目描述

解法

方法一：分组 + 窗口函数 + 连接

评论