with UserData as (
    select ud.*, date_trunc('day', c.timestamp) date
    from user_data ud
    join cookies c on ud.cookie_id = c.cookie_id
    where ud.email not in (
        'matthieu@com.com', 'jared.j.burgess@gmail.com', 'nicolas.omeyer@gmail.com', 'matthieu.a.louis@gmail.com',
        'omeyeralexandre@gmail.com', 'jared@stepsize.com', 'nick@stepsize.com', 'alex@stepsize.com', 'matt@stepsize.com'
    ) or ud.email is null
)

, UsageIndicator as (
    select distinct cookie_id, date
    from (select *, date_trunc('day', utc_timestamp) date from desktop_activity) foo
)

, FirstDay as (
    select cookie_id, min(date) date
    from (select *, date_trunc('day', utc_timestamp) date from desktop_activity) foo
    group by cookie_id
)

, ShowCount as (
    select cookie_id, date, count(cookie_id) as show
    from (select *, date_trunc('day', utc_timestamp) date from desktop_activity) foo
    where activity_type_id = 2
    group by cookie_id, date
)

, SearchCount as (
    select *, row_number() over (partition by cookie_id order by date) days_active
    from (
        select cookie_id, date, count(cookie_id) as search
        from (select *, date_trunc('day', utc_timestamp) date from desktop_activity) foo
        where activity_type_id = 3
        group by cookie_id, date
    ) bar
)

, ExpandedCount as (
    select cookie_id, date, count(cookie_id) expanded
    from (select *, date_trunc('day', utc_timestamp) date from desktop_activity) foo
    where activity_type_id = 4
    group by cookie_id, date
)

, CommitClickedCount as (
    select cookie_id, date, count(cookie_id) clicked
    from (select *, date_trunc('day', utc_timestamp) date from desktop_activity) foo
    where activity_type_id = 5
    group by cookie_id, date
)

, TaskClickedCount as (
    select cookie_id, date, count(cookie_id) clicked
    from (select *, date_trunc('day', utc_timestamp) date from desktop_activity) foo
    where activity_type_id = 18
    group by cookie_id, date
)

, FilterResultsCount as (
    select cookie_id, date, count(cookie_id) filter_results
    from (select *, date_trunc('day', utc_timestamp) date from desktop_activity) foo
    where activity_type_id = 9
    group by cookie_id, date
)

, TimeoutCount as (
    select cookie_id, date, count(cookie_id) timeout
    from (select *, date_trunc('day', utc_timestamp) date from desktop_activity) foo
    where activity_type_id = 10
    group by cookie_id, date
)

, SlackMessageCount as (
    select cookie_id, date, count(cookie_id) slack_message
    from (select *, date_trunc('day', utc_timestamp) date from desktop_activity) foo
    where activity_type_id = 14
    group by cookie_id, date
)


select
    case extract(isodow from ui.date)
        when 1 then 'Mon ' || cast(ui.date as date)
        when 2 then 'Tue ' || cast(ui.date as date)
        when 3 then 'Wed ' || cast(ui.date as date)
        when 4 then 'Thu ' || cast(ui.date as date)
        when 5 then 'Fri ' || cast(ui.date as date)
        when 6 then 'Sat ' || cast(ui.date as date)
        when 7 then 'Sun ' || cast(ui.date as date)
    end date,
    ud.id,
    case
        when fd.date = ui.date then true
        else false
    end first_day,
    case
        when sec.days_active is null then 'no search'
        else sec.days_active::text
    end days_active,
    ud.email,
    coalesce(shc.show, 0) as show,
    coalesce(sec.search, 0) as search,
    coalesce(ec.expanded, 0) expanded,
    coalesce(ccc.clicked, 0) commit_clicked,
    coalesce(tcc.clicked, 0) task_clicked,
    coalesce(frc.filter_results, 0) filter_results,
    coalesce(smc.slack_message, 0) slack_message,
    coalesce(tc.timeout, 0) timeout

from UserData ud
left join UsageIndicator ui on ud.cookie_id = ui.cookie_id
left join FirstDay fd on ui.cookie_id = fd.cookie_id
left join ShowCount shc on ui.cookie_id = shc.cookie_id and ui.date = shc.date
left join SearchCount sec on ui.cookie_id = sec.cookie_id and ui.date = sec.date
left join ExpandedCount ec on ui.cookie_id = ec.cookie_id and ui.date = ec.date
left join CommitClickedCount ccc on ui.cookie_id = ccc.cookie_id and ui.date = ccc.date
left join TaskClickedCount tcc on ui.cookie_id = tcc.cookie_id and ui.date = tcc.date
left join FilterResultsCount frc on ui.cookie_id = frc.cookie_id and ui.date = frc.date
left join TimeoutCount tc on ui.cookie_id = tc.cookie_id and ui.date = tc.date
left join SlackMessageCount smc on ui.cookie_id = smc.cookie_id and ui.date = smc.date

order by ui.date desc