CODEWITHSUNDEEP
mysqlsqlsql-server
user3616587
user3616587

Reputation: 13

Count number of entries in time interval 1 that appear in time interval 2 - SQL

I am new here and tried to look up the answer to my question but couldn't find anything on it. I am currently learning how to work with SQL queries and am wondering how I can count the amount of unique values that appear in two time intervals?

I have two columns; one is the timestamp while the other is a customer id. What I want to do is to check, for example, the amount of customers that appear in time interval A, let's say January 2014 - February 2014. I then want to see how many of these also appear in another time interval that i specify, for example February 2014-April 2014. If the total sample were 2 people who both bought something in january while only one of them bought something else before the end of April, the count would be 1.

I am a total beginner and tried the query below but it obviously won't return what I want because each entry only having one timestamp makes it not possible to be in two intervals.

SELECT
count(customer_id)
FROM db.table
WHERE time >= date('2014-01-01 00:00:00')
AND time < date('2014-02-01 00:00:00')
AND time >= date('2014-02-01 00:00:00')
AND time < date('2014-05-01 00:00:00')

;

Upvotes: 1

Views: 123

Answers (2)

Siyual
Siyual

Reputation: 16917

Here's one method of doing this with conditional grouping in an inner-select. 

Select  Case 
            When GroupBy = 1 Then 'January - February 2014' 
            When GroupBy = 2 Then 'February - April 2014'
        End As Period,
        Count (Customer_Id) As Total
From
(
    SELECT  Customer_Id,
            Case 
                When Time Between '2014-01-01' And '2014-02-01' Then 1
                When Time Between '2014-02-01' And '2014-04-01' Then 2
                Else -1
            End As GroupBy
    From    db.Table
) D
Where GroupBy <> -1
Group By GroupBy

Edit: Sorry, misread the question. This will show you those that overlap those two time ranges:

Select  Count(Customer_Id) 
From    db.Table t1
Where Exists 
(
    Select  Customer_Id
    From    db.Table t2
    Where   t1.customer_id = t2.customer_id
    And     t2.Time Between '2014-02-01' And '2014-04-01'
)
And     t1.Time Between '2014-01-01' And '2014-02-01'

Upvotes: 0

Charvee Shah
Charvee Shah

Reputation: 720

Try this.

    select count(distinct t.customer_id) from Table t
    INNER JOIN Table t1 on t1.customer_id = t.customer_id 
     and t1.time >= '2014-01-01 00:00:00' and t1.time<'2014-02-01 00:00:00'
    where t.time >='2014-02-01 00:00:00' and t.time<'2014-05-01 00:00:00'

Upvotes: 1

Related Questions