Daily User Retention for a month mySQL-CodePudding

I have log_event table and which has table as shown below:

user_id|event_date_time| event
------------------------------
7494212|1535308430     | Opened app
7494212|1535308433     | Closed app
1475185|1535308444     | Registered
6946725|1535308475     | Opened app
6946725|1535308476     | Made a purchase
6946725|1535308477     | closed app

Create table log_event (
    user_id int,
    event_date_time bigint,
    event varchar(70)
);

INSERT INTO log_event Values( 7494212,1535308430,'Opened app');
INSERT INTO log_event Values( 7494212,1535308433,'Closed app');
INSERT INTO log_event Values( 1475185,1535308444,'Registered');
INSERT INTO log_event Values( 6946725,1535308475,'Opened app');
INSERT INTO log_event Values( 6946725,1535308476,'Made a purchase');
INSERT INTO log_event Values( 6946725,1535308477,'closed app');

db<>fiddle

I wanted Daily user retention(day-1) for Aug'18.

Day-1 retention: Users who registered on day-0 and then opened app on day-1.

For ex: In short users who registered on like 15th Aug 2018 and opened app on 16th Aug 2018

CodePudding user response：

You can join all instances of a user opening the app onto registration dates, keeping the results when the date difference is one day:

with to_dt(id, t, e) as (
    select l.user_id, date_format(from_unixtime(l.event_date_time), '%Y-%m-%d %H:%i:%s'), l.event
)
select l.id from to_dt l 
join to_dt l1 on l1.e = 'Registered' and add_date(l1.t, interval 1 day) = l.t where l.e = 'Opened app'