CODEWITHSUNDEEP
mysqlmariadb
AnnaSm
AnnaSm

Reputation: 2300

How to determine the number of Invitations per user in the last 30 days?

my database has two tables related to this questions:

users: id, first_name, last_name, created_at
invitations: id, user_id

What I am trying to output is a list of all users created in the last 30 days and the number of invitations they sent out.. desired output from the query:

user.id | total_invitations
31 | 2
32 | 0
33 | 12
34 | 1
35 | 1
.....

Here's my work in progress query, what am I doing wrong here?

SELECT u.id,
             u.first_name,
           u.last_name,
             u.invitation_approved_at,
       COUNT(i.id) AS Total
FROM users u 
LEFT JOIN invitations i
     ON u.id = i.user_id
WHERE 
    i.type = 'email'
        AND i.revoked_at IS NULL
        AND u.invitation_approved_at >= curdate() - INTERVAL 30 DAY
    AND u.invitation_approved_at < curdate() - INTERVAL -7 DAY
GROUP BY u.id
ORDER BY u.invitation_approved_at;

ty

Upvotes: 0

Views: 30

Answers (1)

JNevill
JNevill

Reputation: 50064

The issue is that you are restricting your LEFT JOIN'd table in your WHERE clause effectively turning it into an INNER JOIN. Instead:

SELECT u.id,
             u.first_name,
           u.last_name,
             u.invitation_approved_at,
       COUNT(i.id) AS Total
FROM users u 
LEFT JOIN invitations i
     ON u.id = i.user_id
       AND i.type = 'email'
        AND i.revoked_at IS NULL
WHERE  u.invitation_approved_at >= curdate() - INTERVAL 30 DAY
    AND u.invitation_approved_at < curdate() - INTERVAL -7 DAY
GROUP BY u.id
ORDER BY u.invitation_approved_at;

Moving those two conditions to your ON clause causes those records to be filtered BEFORE the join occurs instead of AFTER.

I would also advocate for including your first_name and last_name field in your GROUP BY as every other RDBMS will error should a non-aggregated field not be present in your GROUP BY clause. I'm not sure if newer versions of MariaDB would throw an error, but newer versions of MySQL definitely will (versions earlier than 5.7 would happily run this as-is though):

 GROUP BY u.id, u.first_name, u.last_name

Upvotes: 1

Related Questions