CODEWITHSUNDEEP
sqlhive
Michał89
Michał89

Reputation: 87

Count new entries day by day

I would like to count new id's in each day. Saying new, I mean new relative to the day before. Assume we have a table:

Date Id
2021-01-01 1
2021-01-02 4
2021-01-02 5
2021-01-02 6
2021-01-03 1
2021-01-03 5
2021-01-03 7

My desired output, would look like this:

Date Count(NewId)
2021-01-01 1
2021-01-02 3
2021-01-03 2

Upvotes: 0

Views: 56

Answers (3)

James
James

Reputation: 3015

Maybe this other option could also do the job, but being honest I would prefer the @GordonLinoff answer:

select date, count(*)
from your_table t
where not exists (
  select 1 
  from your_table tt
  where tt.Id=t.id
    and tt.date = date_sub(t.date,1)
  )
group by date

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270191

You can use two levels of aggregation:

select date, count(*)
from (select id, min(date) as date
      from t
      group by id
     ) i
group by date
order by date;

If by "relative to the day before" you mean that you want to count someone as new whenever they have no record on the previous day, then use lag() . . . carefully:

select date,
       sum(case when prev_date = date - interval '1' day then 0 else 1 end) 
from (select t.*,
             lag(date) over (partition by id order by date) as prev_date
      from t
     ) t
group by date
order by date;

Upvotes: 2

eshirvana
eshirvana

Reputation: 24603

here is another way, probably the simplest :

select t1.Date, count(*) from table t1
where id not in (select id from table t2 where t2.date = t1.date- interval '1 day')
group by t1.Date

Upvotes: 1

Related Questions