如何用Python标准库实现两CSV关联聚合（等价指定SQL查询）

现有数据库表结构如下：

CREATE TABLE transactions (
transaction_id UUID,
date DATE,
user_id UUID,
is_blocked BOOL,
transaction_amount INTEGER,
transaction_category_id INTEGER
);
CREATE TABLE users (
user_id UUID,
is_active BOOLEAN
);

对应示例数据存储于transactions.csv和users.csv文件中，可通过generate_data.py脚本生成。

需使用Python标准库（禁止使用pandas、sqlite等外部库）编写程序，实现等价于以下SQL查询的功能：

SELECT
t.transaction_category_id,
SUM(t.transaction_amount) AS sum_amount,
COUNT(DISTINCT t.user_id) AS num_users
FROM transactions t
JOIN users u USING (user_id)
WHERE t.is_blocked = False
AND u.is_active = 1
GROUP BY t.transaction_category_id
ORDER BY sum_amount DESC;

要求程序高效可扩展，适配大数据集，最终结果输出至标准输出。

本人为Python新手，最初仅能实现两CSV文件的合并操作，不清楚如何实现WHERE过滤、GROUP BY分组聚合、ORDER BY排序逻辑，现已编写完成如下代码并得到所需结果：

import csv

transactions_file = open('M:\\Codebase\\PySpark\\test\\new\\transactions.csv', 'r')
users_file = open('M:\\Codebase\\PySpark\\test\\new\\users.csv', 'r')

transactions_reader1 = list(csv.reader(transactions_file))
users_reader1 = list(csv.reader(users_file))

transactions_reader = list(filter(None, transactions_reader1))
users_reader = list(filter(None, users_reader1))

data = []

for i in range(len(transactions_reader)):
    for j in range(len(users_reader)):
        output_line = []

    # Creating Headers
        if i == 0 and j == 0:
            output_line = ['transaction_id', 'date', 'user_id', 'is_blocked', 'transaction_amount', 'transaction_category_id', 'is_active']

    # Creating data
        else:

            users_line = users_reader[j]
            transactions_line = transactions_reader[i]

            if users_line[0] == transactions_line[2]:
                if str(transactions_line[3]) == 'False' and str(users_line[1]) == 'True':
                    output_line = [transactions_line[2],
                                   float(transactions_line[4]),
                                   int(transactions_line[5])]

        data.append(output_line)


data = list(filter(None, data))
output = []
last = None
data.pop(0)
data = sorted(data, key=lambda x: x[2])

for (user_id, transaction_amount, transaction_category_id) in data:
    if int(transaction_category_id) != last:
        output.append([int(transaction_category_id), 0, 0])
        last = int(transaction_category_id)
        ids = set()
    if user_id not in ids :
        output[-1][1] += 1
        ids.add(user_id)
    output[-1][2] += float(transaction_amount)

output = sorted(output, key=lambda x: x[2], reverse=True)

output.insert(0, "['transaction_category_id', 'num_users', 'sum_amount']")
print(*output, sep = "\n")

内容的提问来源于stack exchange，提问作者Ayush