CODEWITHSUNDEEP
sqlgroup-by
Vlad
Vlad

Reputation: 2565

Get a number of new users per date

My SQL skills are way subpar and I am struggling with seemingly simple tasks. I am trying to get a report on the number of new users who visit our site for each date.

This is my Stats table:

    | Date  | userid |
    +-------+-------+|
    | date1 | user1  |
    | date1 | user1  |
    | date1 | user2  |
    | date2 | user1  |
    | date2 | user3  |
    | date3 | user2  |
    | date3 | user4  |
    | date4 | user3  |

and I need to get a recordset where records would look something like this:

| Date  | New users on that date |
+-------+------------------------+    
| date1 | 2                      |
| date2 | 1                      |
| date3 | 1                      |
| date4 | 0                      |

I have a feeling I need to group the users distinctly per date but I get tripped on the actual implementation...

Upvotes: 0

Views: 72

Answers (1)

lc.
lc.

Reputation: 116458

Try something like this, which will count the number of users which do not have a record for a previous date (SQL Fiddle demo):

SELECT Date, COUNT(*) as NewUsers
FROM Stats s1
WHERE NOT EXISTS
(
    SELECT 1
    FROM Stats s2
    WHERE s2.userid = s1.userid
    AND s2.Date < s1.Date
)
GROUP BY Date

Note, if you want to explicitly get the "0" value, you'll have to join back on the Stats table (SQL Fiddle demo):

SELECT s.Date, COALESCE(x.NewUsers, 0) AS NewUsers
FROM Stats s
LEFT OUTER JOIN
(
    SELECT Date, COUNT(*) as NewUsers
    FROM Stats s1
    WHERE NOT EXISTS
    (
        SELECT 1
        FROM Stats s2
        WHERE s2.userid = s1.userid
        AND s2.Date < s1.Date
    )
    GROUP BY Date
) x ON s.Date = x.Date

Alternatively, you can use COUNT over MIN, which might yield more performance with the right indexes (SQL Fiddle demo):

SELECT Date, COUNT(*)
FROM
(
    SELECT MIN(Date) AS Date, userid
    FROM Stats
    GROUP BY userid
) x
GROUP BY Date

Upvotes: 1

Related Questions