SELECT DATETIME_ADD(DATETIME "2018-10-31 00:30:00", INTERVAL 1 DAY);

with installs as (
    select 1 as id, timestamp '2018-09-01' as ts union all
    select 2 as id, timestamp '2018-09-01' as ts union all
    select 3 as id, timestamp '2018-09-02' as ts union all
    select 4 as id, timestamp '2018-09-02' as ts union all
    select 5 as id, timestamp '2018-09-02' as ts
),
  logins as (
    select 1 as id, timestamp '2018-09-02' as ts union all
    select 1 as id, timestamp '2018-09-02' as ts union all
    select 3 as id, timestamp '2018-09-03' as ts union all
    select 3 as id, timestamp '2018-09-03' as ts union all
    select 2 as id, timestamp '2018-09-03' as ts
)
select i.ts as day, 
       count(distinct i.id) as new_users,
       count(distinct l.id) as returned_users,
       count(distinct l.id)/count(distinct i.id) as retention
from installs i left join logins l
    on i.id = l.id and l.ts = timestamp_add (i.ts, interval 1 day)
where 
    i.ts between timestamp '2018-09-01' and timestamp '2018-09-30'
group by i.ts
order by i.ts;