SHARE
TWEET

Untitled

a guest Nov 14th, 2019 113 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. with first_edits as (
  2.     select
  3.         event_user_text,
  4.         trunc(event_timestamp, 'MONTH') as date,
  5.         min(event_timestamp) as first_edit_time
  6.     from wmf.mediawiki_history mh
  7.     where
  8.        event_timestamp >= '2019-03-01' and
  9.         event_timestamp < '2019-11-01'  and
  10.         page_namespace_historical % 2 == 1 and
  11.         event_user_revision_count < 100 and
  12.         not event_user_is_anonymous and
  13.         event_entity = 'revision' and
  14.         event_type = 'create' and
  15.         mh.snapshot = '2019-10'
  16.     group by event_user_text,  trunc(event_timestamp, 'MONTH')
  17. )
  18.  
  19. SELECT
  20.     (count(30_days.user_name) / count(*)) as retention_rate,
  21.      date
  22. FROM
  23. (
  24.     SELECT distinct(fe.event_user_text) as user_name,
  25.     fe.date as date,
  26.     fe.first_edit_time
  27.     FROM first_edits fe
  28. ) 1st_edit
  29.      LEFT JOIN
  30.     (
  31.     SELECT distinct(event_user_text) as user_name
  32.     FROM wmf.mediawiki_history
  33.     WHERE
  34.         event_entity = 'revision' and
  35.         event_type = 'create' and
  36.         event_user_revision_count < 100 and
  37.         event_user_id IS NOT NULL and
  38.         snapshot = '2019-10' and
  39.         page_namespace_historical % 2 == 1 and
  40.             event_timestamp >= '2019-03-01' and
  41.            event_timestamp < '2019-11-01' and
  42.             -- second since previous revision is between two and 30 days
  43.             unix_timestamp(event_timestamp, 'YYYYMMddHHmmss') >=
  44.              (unix_timestamp(first_edit_time, 'YYYYMMddHHmmss') + (2*24*60)) and
  45.                  unix_timestamp(event_timestamp, 'YYYYMMddHHmmss') <=
  46.              (unix_timestamp(first_edit_time, 'YYYYMMddHHmmss') + (30*24*60))
  47.              ) 30_days
  48.      on 1st_edit.user_name = 30_days.user_name
  49.      Group by date
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top