Hive/Impala changing table counts

Question

I have a list of release dates (some past and some future) and a list of registration numbers.

release date     registration
01/01/2019        R1
02/01/2019        R2
07/02/2019        R3

I basically want to create a new table that will display the total number of registration numbers for each day (future dates).

date            total registration numbers
05/02/2019       2
06/02/2019       2
07/02/2019       3

I know how to use count(*) to find the number of registrations and I have thought about combining this with a calendar table for the future dates.

Answer 1

If you have a calendar table, you can use window functions:

select c.date,
       count(t.date) as registrations_on_day,
       sum(count(t.date)) over (order by c.date) as registrations_through_day
from calendar c left join
     t
     on c.date = t.date
group by c.date
order by c.date;