# Gamification and invitations

**URL:** <https://meta.discourse.org/t/gamification-and-invitations/269544>\
**Category:** Bug\
**Tags:** gamification, invites\
**Created:** [June 25, 2023, 10:47am UTC](https://meta.discourse.org/t/gamification-and-invitations/269544 "2023-06-25T10:47:31Z")\
**Posts on this page:** 1\
**Showing post:** 3

<div class="post-metadata">

**Author:** ![JammyDodger](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/jammydodger/32/254611_2.png) [@JammyDodger](https://meta.discourse.org/u/JammyDodger)\
**Post date:** [September 24, 2023, 5:01pm UTC](https://meta.discourse.org/t/gamification-and-invitations/269544/3 "2023-09-24T17:01:30Z")

</div>

Would something along these lines work better (adjusted to fit the scorable query format - this is a test one for the [data explorer](https://meta.discourse.org/t/32566?silent=true) 🙂):

```sql
-- [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.

---

_[View the full topic](https://meta.discourse.org/t/gamification-and-invitations/269544)._