Gamification and invitations

bug
3

Would something along these lines work better (adjusted to fit the scorable query format - this is a test one for the data explorer :slight_smile:):

-- [params]
-- date :start_date
-- date :end_date

SELECT 
    invited_by_id AS user_id,
    COUNT(*) AS user_invites,
    COUNT(*) * 10 AS invite_score
FROM invited_users iu
  JOIN invites i ON i.id = iu.invite_id
  JOIN users u ON u.id = iu.user_id
WHERE iu.redeemed_at::date BETWEEN :start_date AND :end_date
  AND iu.user_id <> i.invited_by_id 
  AND u.created_at > iu.redeemed_at
GROUP BY invited_by_id
ORDER BY user_invites DESC

When a user is deleted it clears them from the invited_users table, so would no longer be in the count. If the deletion happened within 10 days then it would auto-corrected, if longer then it would need a manual score refresh.

Using the redeemed_at date would account for those invites which were created longer than 10 days ago.

AND iu.user_id <> i.invited_by_id would also exclude self invites.

Joining in the users table and adding AND u.created_at > iu.redeemed_at would also exclude inviting existing users.

3 Likes