左加入筛选器加入30天而不是加入30天的推荐

Question

我有两张桌子

表用户

USER_ID------USERNAME------------REGISTERTIME  
1-----------------admin----------1432985224    
2-----------------test ----------1433985224
3-----------------test2----------1332985224     

表转介

REFERRER_ID----------REFERRAL_ID 
1-----------------------------2
1-----------------------------3

如何在mysql中显示以下内容：

user admin have 2 referrals in last 30 days
user test  have 0 referrals in last 30 days
user test2 have 0 referrals in last 30 days

试过：

SELECT
    users.username,
    COUNT(referrals.user_id) AS referrals
FROM
    users
LEFT JOIN
    referrals ON
    referrals.referral_id = users.user_id
WHERE users.registertime >= UNIX_TIMESTAMP( '2015-10-15' ) AND registertime < UNIX_TIMESTAMP( '2015-10-18' )
GROUP BY
    users.user_id

它所显示的是

只有那些参加了30天并且有推荐的用户，但我需要它来显示所有在过去30天内有新推荐的用户。

错误：(这只显示在过去30天内加入并有推荐的用户)

new user1 have 12 referrals new user2 have 3 referrals new user3 have 2 referrals

正确：(这显示了所有在过去30天内加入推荐的用户)

Old user1 have 52 referrals New user3 have 28 referrals Old user33 have 10 referrals

我希望我解释得很好

Answer 1

SELECT
    users.username,
    COUNT(referrals.referrer_id) AS referrals
FROM
    users
LEFT JOIN
    referrals ON
    referrals.referrer_id = users.user_id
WHERE users.registertime >= UNIX_TIMESTAMP( '2015-10-15' ) AND registertime < UNIX_TIMESTAMP( '2015-10-18' )
GROUP BY
    users.user_id