I have an iPhone app out there that "calls home" to my server every time a user uses it. On my server, I create a row in a MySQL table each time with the unique ID (similar to a serial number) aka UDID for the device, IP address, and other data.
Table ClientLog columns:
Time, UDID, etc, etc.
What I'd like to know is the number of new devices (new unique UDIDs) on a given date. I.e. how many UDIDs were added to the table on a given date that don't appear before that date? Put plainly, this is the number of new users I gained that day.
This is close, I think, but I'm not 100% there and not sure it's what I want...
SELECT distinct UDID
FROM ClientLog a
WHERE NOT EXISTS (
SELECT *
FROM ClientLog b
WHERE a.UDID = b.UDID AND b.Time <= '2010-04-05 00:00:00'
)
I think the number of rows returned is the new unique users after the given date, but I'm not sure. And I want to add to the statement to limit it to a date range (specify an upper bound as well).
