34 lines
1022 B
SQL
34 lines
1022 B
SQL
-- 预览：将补多少超级个体用户、合计增加多少营收（每人一条 ¥1980 vip 已付订单）
|
||
-- 条件：当前仍为 VIP（is_vip=1 且未过期或未填过期视为仍有效）且尚无 vip 已付订单
|
||
|
||
SELECT
|
||
COUNT(*) AS users_to_backfill,
|
||
COUNT(*) * 1980.00 AS revenue_to_add_yuan
|
||
FROM users u
|
||
WHERE u.deleted_at IS NULL
|
||
AND COALESCE(u.is_vip, 0) = 1
|
||
AND (u.vip_expire_date IS NULL OR u.vip_expire_date > NOW())
|
||
AND NOT EXISTS (
|
||
SELECT 1 FROM orders o
|
||
WHERE o.user_id = u.id
|
||
AND o.product_type = 'vip'
|
||
AND o.status IN ('paid', 'completed', 'success')
|
||
);
|
||
|
||
SELECT
|
||
u.id,
|
||
u.nickname,
|
||
u.vip_activated_at,
|
||
u.vip_expire_date
|
||
FROM users u
|
||
WHERE u.deleted_at IS NULL
|
||
AND COALESCE(u.is_vip, 0) = 1
|
||
AND (u.vip_expire_date IS NULL OR u.vip_expire_date > NOW())
|
||
AND NOT EXISTS (
|
||
SELECT 1 FROM orders o
|
||
WHERE o.user_id = u.id
|
||
AND o.product_type = 'vip'
|
||
AND o.status IN ('paid', 'completed', 'success')
|
||
)
|
||
ORDER BY u.vip_activated_at DESC, u.id;
|