How can I find the team with the most active sellers (highest number of software sales per user per day)?

Question

How can I find the team with the most active sellers (highest number of software sales per user per day)? I wanted to do something like this query but it doesn't feel quiet right

SELECT users.team_id, count(*) as sales FROM users
INNER JOIN activity ON users.user_id = activity.user_id
WHERE activity.sales = software
GROUP BY users.team_id
ORDER BY sale DESC

My user table looks like this:

user_id       team_id  
0               62  
1               32      
2               21

While my activity table looks like this:

user_id      sale_date              sale
1            2019-05-02 11:02:39    hardware    
2            2018-05-02 11:02:53    software    
2            2019-06-02 11:42:33    software

Your query counts the absolute numbers of sales per team, not per user. Do you want to take the average by dividing through team size? Also it's unclear what "per day" is supposed to mean exactly, do you want to average by active days? Or get results by individual days? — Bergi, Apr 04 '20 at 15:43
oops corrected to team_id. and hm i think i need to find the single team that had the highest number of software sales done by user by day since joining. — Drew Pham, Apr 04 '20 at 15:48
Again, what exactly do you mean with "by user by day"? Just find the highest amount of sales done by a single user on a single day, then get the team of that user? — Bergi, Apr 04 '20 at 15:52
i am looking for who was most active each day for the month of may just by frequency. then out of the entire month which team showed up the most — Drew Pham, Apr 04 '20 at 16:07
would you kindly assist here as well? https://stackoverflow.com/questions/61032939/what-percent-of-the-time-does-a-user-login-immediately-followed-by-sending-a-me — Drew Pham, Apr 04 '20 at 18:58

score 2 · Accepted Answer · answered Apr 04 '20 at 16:38

It sounds like this is a step-by-step query, so let's start small with the innermost part: get the sales counts per user per day.

SELECT sale_date, user_id, count(*) as sales
WHERE sale = 'software'
GROUP BY sale_date, user_id

Then we can get the most active user for each day:

SELECT DISTINCT ON (sale_date) sale_date, user_id
FROM (
    SELECT sale_date, user_id, count(*) as sales
    WHERE sale = 'software'
    GROUP BY sale_date, user_id
) AS counts_by_date_and_user
ORDER BY sale_date, sales -- consider breaking ties somehow

Then we can count how often a user was the most active of the day, sort by that, and get their team id.

SELECT team_id, user_id, count(*) as days
FROM (
    SELECT DISTINCT ON (sale_date) sale_date, user_id
    FROM (
        SELECT sale_date, user_id, count(*) as sales
        WHERE sale = 'software'
        GROUP BY sale_date, user_id
    ) AS counts_by_date_and_user
    ORDER BY sale_date, sales -- consider breaking ties somehow
) AS most_active
JOIN users USING (user_id)
GROUP BY user_id
ORDER BY days DESC

oh this actually isnt selecting the top number of sales per day though — Drew Pham, Apr 04 '20 at 19:24

How can I find the team with the most active sellers (highest number of software sales per user per day)?

1 Answers1