How to get common IDs in each group from a group by SQL clause?

Question

I have data of calls for customers. I want to get those customers between two dates that have activity against every date. They did at least one activity every day. I tried following query.

Following is the query:

select date_id , count (distinct customer_id) from usage_analysis 
where usage_direction_type_id = 1
and date_id => 20130608 and date_id <= 20130612
group by date_id

That returns:

DATE_ID         COUNT
----------------------------
20130608        23451
20130609        9878
20130610        56122   
20130611        7811
20130612        12334

But I want to get those customers that are common in each group. It may happen a person who called on 8 June does not exist on the next day. So I only want those customers that exist in every group.

Any idea who can I do that in SQL?

score 3 · Accepted Answer · answered Jun 18 '13 at 11:01

3

You can count the distinct dates for each customer. Only customers with five distinct dates would then pass the test. The following provides the list of customers:

select customer_id
from usage_analysis 
where usage_direction_type_id = 1 and
      date_id >= 20130608 and date_id <= 20130612
group by customer_id
having count(distinct date_id) = 5

answered Jun 18 '13 at 11:01

Gordon Linoff

1,242,037
58
646
786

If a user makes an activity on 20130612 only but it will be 5 times then what will happen with your query's output????? – Romesh Jun 18 '13 at 11:17
@RomsJain . . . The query is counting distinct values of `date_id`, so that would count only as one date. The customer would not appear. – Gordon Linoff Jun 18 '13 at 11:20
It does not fetch any record. Seems strange. Does it mean, I don't have any user that did activity every day. – Tahir Jun 18 '13 at 13:10
@TahirAkram . . . I can't speak to contents of your data. Start with a smaller data range -- just June 8th, then June 8th and 9th, and so on -- and see what the results are returning. – Gordon Linoff Jun 18 '13 at 13:12
I understand. Well I tried with even 2 days. But does not bring any data. I see more into my data. – Tahir Jun 18 '13 at 13:50
It works, without any change. Thanks Gordon. I was connected with another DB. – Tahir Jun 19 '13 at 04:46

score 0 · Answer 2 · answered Jun 18 '13 at 16:02

0

@Gordon Linoff answer should be working fine for your situation. When you tried with 2 days, did you make sure to change the count value from 5 to 2?

answered Jun 18 '13 at 16:02

user2497624

159
9

How to get common IDs in each group from a group by SQL clause?

2 Answers2