with attempts as (
    select
        to_date(min(dt)) as date,
        event.platform as device,
        event.editor_interface as interface,
        useragent.browser_family as browser,
        useragent.os_family as os,
        sum(cast(event.action = 'saveSuccess' as int)) >= 1 as completed
    from event.editattemptstep
    where
        event.action in ('ready', 'saveSuccess') and
    --Substitute with desired date range
        year >=2019
    group by event.editing_session_id, event.platform, event.editor_interface, useragent.browser_family,  useragent.os_family 
)
select
    date,
    device,
    interface,
    browser,
    os,
    sum(cast(completed as int)) / count(*) as edit_completion
from attempts
group by date, device, interface, browser, os

with attempts as (
    select
        date_format(min(dt), "yyyy-MM-dd") as date,
        event.bucket as bucket,
        sum(cast(event.action = "saveSuccess" as int)) >= 1 as completed
    from event.editattemptstep
    where
        event.action in ("ready", "saveSuccess") and
        event.bucket in ("default-source", "default-visual") and
--Substitute with desited date range
        year >= 2019
    group by event.bucket, event.editing_session_id
)
select
    date,
    bucket,
    sum(cast(completed as int)) / count(*) as edit_completion_rate
from attempts
group by date, bucket

with attempts as (
    select
        to_date(min(dt)) as date,
        sum(cast(event.action = 'saveSuccess' as int)) >= 1 as completed,
        min(event.user_id) = 0 as unregistered,
        min(event.user_editcount) between 1 and 4 as one_to_four_edits,
        min(event.user_editcount)  >= 5 AND min(event.user_editcount) < 100 as five_99_edits,
        min(event.user_editcount)  >= 100 AND min(event.user_editcount) < 1000 as onehunderd_999_edits,
        min(event.user_editcount)  >= 1000 as over_1000
    from event_sanitized.editattemptstep
    inner join canonical_data.wikis 
    on wiki = database_code and
    database_group ==  'wikipedia'
    where
        event.platform = 'phone' and
        event.editor_interface = 'visualeditor' and
        event.action in ('ready', 'saveSuccess') and
        year >=2019
    group by event.editing_session_id
)
select
    date,
    sum(cast(completed as int)) / count(*) as edit_completion,
    sum(cast(completed and unregistered as int)) / sum(cast(unregistered as int)) as unregistered,
    sum(cast(completed and one_to_four_edits as int)) / sum(cast(one_to_four_edits as int)) as one_to_four_edits,
    sum(cast(completed and five_99_edits as int)) / sum(cast(five_99_edits as int)) as five_99_edits,
    sum(cast(completed and onehunderd_999_edits as int)) / sum(cast(onehunderd_999_edits as int)) as onehunderd_999_edits,
    sum(cast(completed and over_1000 as int)) / sum(cast(over_1000 as int)) as over_1000_edits
from attempts
group by date