Using group by to get counts while having other uniqueness conditions

Question

So let's say we have a table referrals structured with the following columns:

(string) referral_id, (string) referrer, (string) user_id, (int) timestamp, (int) condition

What I want is at any given moment, the number of distinct users for which condition > 0, grouped by referrer. In addition, if a user has condition > 0 by multiple referrers, only the most recent referral should be counted. So, if both 'Referrer A' and 'Referrer B' referred a given user but Referrer B's was more recent, only Referrer B would get the user added to their count.

I know that in order to get the counts by referrer, I would do

SELECT referrer, COUNT(DISTINCT(user_id)) 
FROM referrals 
WHERE condition > 0 
GROUP BY referrer

How would I add in the uniqueness and most recent referral conditions?

score 0 · Accepted Answer · answered Aug 17 '17 at 05:02

Instead of querying the referrals table itself, use a subquery that gets the most recent referral for each user, using one of the techniques in SQL select only rows with max value on a column.

select referrer, COUNT(DISTINCT user_id))
FROM (SELECT referrer, user_id
      FROM referrals AS r
      JOIN (SELECT user_id, MAX(timestamp) AS timestamp
            FROM referrals
            WHERE condition > 0
            GROUP BY user_id) AS maxr
      ON r.user_id = maxr.user_id AND r.timestamp = maxr.timestamp) AS x
GROUP BY referrer

Using group by to get counts while having other uniqueness conditions

1 Answers1