17 lines
886 B
SQL
17 lines
886 B
SQL
-- Read-only audit: repeated active member grants per user/product/quota type.
|
|
-- This query does not change quota balances or historical accounting records.
|
|
SELECT g.user_id, o.goods_id, g.quota_type,
|
|
COUNT(*) AS grant_count,
|
|
SUM(g.amount) AS granted_amount,
|
|
SUM(g.used) AS used_amount,
|
|
SUM(g.amount - g.used) AS recorded_remaining,
|
|
MIN(g.id) AS first_grant_id,
|
|
GROUP_CONCAT(g.ref_id ORDER BY g.id) AS order_numbers
|
|
FROM quota_grants AS g
|
|
JOIN orders AS o ON o.order_no = g.ref_id AND o.user_id = g.user_id
|
|
WHERE g.source = 1 AND g.deleted_at = 0
|
|
GROUP BY g.user_id, o.goods_id, g.quota_type
|
|
HAVING COUNT(*) > 1
|
|
ORDER BY grant_count DESC, g.user_id, o.goods_id, g.quota_type;
|
|
-- recorded_remaining is not necessarily effective remaining: VIP may be expired,
|
|
-- and daily quota types use separate daily usage counters. Do not deduct it blindly. |