Day: September 30, 2019

select 
event_date as install_dt, 
round(count(distinct b.player_id),0) as installs, 
round(count(distinct b.retained_player_id)/count(distinct b.player_id),2) as Day1_retention
from(
    select 
    a.player_id, 
    a.event_date, 
    case 
        when datediff(next_event_date, event_date) = 1 then a.player_id
        else null
    end as retained_player_id
    from(
        select 
        player_id,
        event_date, 
        lead(event_date,1) over(partition by player_id order by event_date) as next_event_date
        from Activity
        group by 1,2
    ) a
    join (
        select 
        player_id, 
        min(event_date) as min_event_date
        from Activity
        group by 1 
    ) m
        on (a.player_id = m.player_id and a.event_date = min_event_date)
)b
group by 1
order by 1

select 
a.book_id, 
a.name
from(
    select 
    b.book_id, 
    b.name, 
    b.available_from, 
    sum(
        case 
            when o.dispatch_date <= '2019-06-23' and o.dispatch_date >= '2018-06-23' then o.quantity 
        else 0 
        end) as total_quantity
    from (
        select * 
        from Books 
        /* exclude records available in the latest month from 2019-06-23 */
        where available_from < date_sub(date_format('2019-06-23', '%Y-%m-%d') , interval 1 month) 
    ) b
        left join Orders o 
            on b.book_id = o.book_id
    group by 1,2,3
    /* total books sold <10 copies in the last year */
    having sum(
        case 
            when o.dispatch_date <= '2019-06-23' and o.dispatch_date >= '2018-06-23' then o.quantity 
            else 0 
        end) < 10
) a
order by a.book_id

select 
min_date as login_date, 
count(distinct user_id) as user_count
from(
    select 
    user_id, 
    min(activity_date) as min_date
    from traffic
    where activity = 'login'
    group by 1
) t
where datediff('2019-06-30', min_date) <= 90
group by 1
order by 1