如何计算所有联盟会员的总收益？附数据表与错误SQL查询

需求与背景 #

• 目标：获取所有联盟会员的总收益
• 数据表结构：
  • USER：id, email, affiliate_id, referral_id
  • TRANSACTION：id, user_id, amount, txn_fee, status, created（注：原查询用到created字段，推测表中包含该字段）
• 规则：联盟会员仅从被推荐用户的首笔有效存款交易中获得5%的奖金

原查询的问题 #

你给出的SQL存在几个核心问题，导致结果不正确：

SELECT email,sum(t.amount),min(t.created) 
FROM user as u 
INNER JOIN transaction as t 
WHERE u.id = t.user_id and t.status=1 
GROUP BY u.id;

• 完全没关联联盟推荐关系：没有通过referral_id和联盟会员的id建立关联，根本没统计到联盟会员从推荐用户获得的收益
• 未筛选首笔交易：只是取了每个用户的最早交易时间，但没确保只计算这笔首笔交易的奖金
• 统计逻辑错误：直接求和用户的所有交易金额，不是按首笔交易的5%计算联盟收益

修正后的SQL查询 #

-- 先筛选出每个用户的首笔有效交易
WITH user_first_valid_txn AS (
    SELECT 
        user_id,
        amount,
        -- 按用户分组，交易时间排序，标记首笔交易为1
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created ASC) AS txn_rank
    FROM TRANSACTION
    WHERE status = 1 -- 仅统计有效交易
)
SELECT
    u.id AS 联盟会员ID,
    u.email AS 联盟会员邮箱,
    u.affiliate_id AS 联盟会员标识,
    -- 计算首笔交易的5%作为奖金，汇总总收益
    SUM(ufvt.amount * 0.05) AS 总收益
FROM USER u
-- 关联被推荐的用户：被推荐用户的referral_id对应联盟会员的id
JOIN USER referred_user ON referred_user.referral_id = u.id
-- 关联被推荐用户的首笔有效交易
JOIN user_first_valid_txn ufvt ON referred_user.id = ufvt.user_id
WHERE ufvt.txn_rank = 1 -- 仅取首笔交易
GROUP BY u.id, u.email, u.affiliate_id
ORDER BY 总收益 DESC;

逻辑说明 #

1. CTE部分：给每个用户的有效交易按创建时间排序，标记首笔交易为txn_rank=1，确保只取符合规则的交易
2. 关联推荐关系：通过USER表的referral_id字段，把联盟会员和他们推荐的用户关联起来
3. 计算并汇总收益：对每笔首笔交易计算5%的奖金，再按联盟会员分组汇总总收益

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