CODEWITHSUNDEEP
mysqlsql
bogdanCsn
bogdanCsn

Reputation: 1325

SQL - Counting re-occuring records based on date

I have a table named LoginTable with just 2 columns: id as VarChar and login_date as Date. Here is a small sample: 

+-------+------------+
| id    | login_date |
+-------+------------+
| user1 | 2014-10-15 |
| user1 | 2014-10-15 |
| user2 | 2014-10-15 |
| user3 | 2014-10-15 |
| user1 | 2014-10-16 |
| user3 | 2014-10-16 |
| user4 | 2014-10-16 |
| user2 | 2014-10-17 |
| user4 | 2014-10-17 |
+-------+------------+

I would like to write a SQL query that shows, for each day, how many users logged in the next day. For instance, Oct 15th has 3 unique logins - out of those 3, only 2 users have logged in on Oct 16th. THe output should be this:

+------------+--------------+
| Date       | NextDayLogin |
+------------+--------------+
| 2014-10-15 | 2            |
| 2014-10-16 | 1            |
| 2014-10-17 | 0            |
+------------+--------------+

My best try (theoretically) was this:

SELECT 
    DISTINCT(id),
    DATE(login_date) as 'Dates' 
FROM LoginTable t1
INNER JOIN (
    SELECT 
        DISTINCT(id) as id2, 
        DATE(login_date) as 'DatesNew' 
    FROM LoginTable 
    WHERE 'DatesNew' = DATE(t1.login_date) + INTERVAL '1' DAY
) t2
ON DATE(t1.login_date) = t2.DatesNew 
AND t1.id = t2.id2

However, I receive an error: Unknown column t1.log_date in where clause .
How can this be achieved? I am using MySQL if this matters.

Upvotes: 0

Views: 45

Answers (3)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You can left join on the table with the specified +1 date to get the desired count.

Fiddle with sample data

select t1.login_date, count(distinct t2.id) as nextdaylogin
from t t1
left join t t2 on t1.login_date = t2.login_date-1 and t1.id = t2.id
group by t1.login_date

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269693

One method is to use a correlated subquery:

select date(login_date), count(*) as NumOnDay,
       (select count(*)
        from LoginTable lt2
        where lt2.login_date >= date_add(date(lt.login_date), interval 1 day) and
              lt2.login_date < date_add(date(lt.login_date), interval 2 day)
       ) as NumNextDay
from LoginTable lt
group by date(login_date)
order by date(login_date);

Your attempt doesn't even have a count(), so I'm not sure how you were thinking of getting the counts.

Upvotes: 0

1&#39;&#39;
1&#39;&#39;

Reputation: 324

Just setup a new column maybe called consecutive_days

Then each time the users login, just test the curr_date against a last_login_datetime (column you can create as well) and if the times are less than 24 hours apart, set consecutive_days = consecutive_days +1;

If not the set it equal to 0.

Upvotes: 0

Related Questions